How to defrag a MS SQL 2008r2 database

Hello Experts,

I have a database that I believe needs defragmentation.  Below is one table example and the script that I used to get these results.  At the very bottom is a script I ran to try to defrag but when I ran the script it had no effect.  The database is in FULL recovery mode. First, how can I defrag the database and 2nd what should I do to put a maintenance plan in place to resolve this issue?

=======
Database Name   Table Name  Index Name   Index Type               Avg Page Frag  Page Counts
database                   CodeList          PK_CODELIST      CLUSTERED INDEX      87.5                           8


=======
SELECT  DB_NAME(DATABASE_ID) AS [DatabaseName] ,
        OBJECT_NAME(OBJECT_ID) AS TableName ,
        SI.NAME AS IndexName ,
        INDEX_TYPE_DESC AS IndexType ,
        AVG_FRAGMENTATION_IN_PERCENT AS AvgPageFragmentation ,
        PAGE_COUNT AS PageCounts
FROM    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, N'LIMITED') DPS
        INNER JOIN sysindexes SI ON DPS.OBJECT_ID = SI.ID
                                    AND DPS.INDEX_ID = SI.INDID
WHERE   avg_fragmentation_in_percent > 20
        AND OBJECT_NAME(OBJECT_ID) NOT LIKE '%Scorm%'
ORDER BY tablename
GO

========
dbcc dbreindex('SystocOwner.UDACodeList')
tucktechAsked:
Who is Participating?
 
Scott PletcherConnect With a Mentor Senior DBACommented:
Any table with 8 or fewer pages may not get any advantage from being rebuilt.

The commands I posted above for SystocOwner.Log_Detail will change it to a clustered index from a heap.  Basically it involves running a "CREATE CLUSTERED INDEX" command on the table.


DROP INDEX  MESSAGES_IN1 ON SystocOwner.Log_Detail
CREATE CLUSTERED INDEX Log_Detail__CL ON SystocOwner.Log_Detail ( <col_name> /*, <col_name2>, ... */ ) WITH ( ONLINE = ON ) ON [<same_filegroup_name_the_nonclus_index_is_currently_on>]
0
 
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
Here is the link to what most of us are using

http://ola.hallengren.com/
0
 
PadawanDBAConnect With a Mentor Operational DBACommented:
In addition to Aneesh's post, I recommend you also take a look at the Ozar team's blitzIndex which does a good job sanity checking your indexes =)

http://www.brentozar.com/blitzindex/
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
Scott PletcherConnect With a Mentor Senior DBACommented:
It's only 8 pages, so a rebuild is not a big deal one way or the other.

Still, here's the proper command to rebuild the index/table:


ALTER INDEX PK_CODELIST ON /*schema_name.*/CodeList REBUILD
0
 
tucktechAuthor Commented:
Hello, ok, I understand that was not a big table but I have these which I would think are "system / database" related and they have a high fragmentation and a somewhat large number of pages...

Also, I had run this built in the maintenance area with SQL Studio...

USE [SYSTOC]
GO
ALTER INDEX [PK_CODELIST] ON [SystocOwner].[CodeList] REBUILD PARTITION = ALL WITH ( PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, ONLINE = OFF, SORT_IN_TEMPDB = OFF )
GO

Sorry I am not a db expert so I don't know what these tables do....  Should I change the recover mode to simple rather than full?  Or is this a reindex problem?


SYSTOC      Log                      NULL                       HEAP                                              97.441      9143
SYSTOC      Log_Detail      NULL                       HEAP                                             99.47      7347
SYSTOC      Log_Detail      MESSAGES_IN1      NONCLUSTERED INDEX              34.978      3222
0
 
Scott PletcherConnect With a Mentor Senior DBACommented:
The "Log" and "Log_Detail" tables should be changed to have a clustered index rather than being "heaps" (a HEAP is simply a table that does not have a clustering index).

For Log_Detail, you can cluster the table on whatever column(s) the current nonclustered index, MESSAGES_IN1, is based on.  You'll want to drop the existing nonclustered index first.

DROP INDEX  MESSAGES_IN1 ON SystocOwner.Log_Detail
CREATE CLUSTERED INDEX Log_Detail__CL ON SystocOwner.Log_Detail ( <col_name> /*, <col_name2>, ... */ ) WITH ( ONLINE = ON ) ON [<same_filegroup_name_the_nonclus_index_is_currently_on>]

For the "Log" table, my best initial guess is to use the log datetime as the clustering key.

Since these tables aren't that large, it shouldn't take long to rebuild them.

Run the commands ONLINE if possible.  You can try the command -- SQL will give you an error if it can't rebuild it online, in which case remove it from the command.
0
 
tucktechAuthor Commented:
Hello, after I run

ALTER INDEX PK_CODELIST ON SystocOwner.CodeList REBUILD

and then run a check on the frag, nothing appears to have changed.  I do get a completed successfully message.

Also, how to do I change from a HEAP to a Clustered Index?

Sorry, I have very little knowledge of SQL
0
 
tucktechAuthor Commented:
Thanks Scott,

I believe I understand that the advantage of changing a table with 8 pages is not significant but if it won't rebuild am I doing something wrong?  I am assuming it would not rebuild for a larger page table....

As for the create cluster index I get the following message... I am using sql standard edition 2008r2


Msg 1712, Level 16, State 1, Line 2
Online index operations can only be performed in Enterprise edition of SQL Server.
0
 
Scott PletcherConnect With a Mentor Senior DBACommented:
Then we have to remove the "WITH ( ONLINE = ON )" part:


CREATE CLUSTERED INDEX Log_Detail__CL ON SystocOwner.Log_Detail ( <col_name> /*, <col_name2>, ... */ ) ON [PRIMARY]
0
 
Anthony PerkinsConnect With a Mentor Commented:
This is from SQL Server's BOL on Reorganize and Rebuild Indexes:
In general, fragmentation on small indexes is often not controllable. The pages of small indexes are stored on mixed extents. Mixed extents are shared by up to eight objects, so the fragmentation in a small index might not be reduced after reorganizing or rebuilding the index.
0
 
tucktechAuthor Commented:
Was able to fix defrag tables and remove heap files.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.