Solved

How to defrag a MS SQL 2008r2 database

Posted on 2014-02-12
11
3,541 Views
Last Modified: 2014-02-25
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')
0
Comment
Question by:tucktech
11 Comments
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 50 total points
ID: 39854121
Here is the link to what most of us are using

http://ola.hallengren.com/
0
 
LVL 10

Assisted Solution

by:PadawanDBA
PadawanDBA earned 100 total points
ID: 39854237
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
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 300 total points
ID: 39854286
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
 

Author Comment

by:tucktech
ID: 39854330
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
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 300 total points
ID: 39854381
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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 

Author Comment

by:tucktech
ID: 39855884
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
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 300 total points
ID: 39856164
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
 

Author Comment

by:tucktech
ID: 39856410
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
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 300 total points
ID: 39856912
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
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 50 total points
ID: 39862195
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
 

Author Closing Comment

by:tucktech
ID: 39886142
Was able to fix defrag tables and remove heap files.
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now