Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How to defrag a MS SQL 2008r2 database

Posted on 2014-02-12
11
Medium Priority
?
4,158 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 200 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 400 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 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 1200 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
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 

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 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 1200 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
 

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 70

Accepted Solution

by:
Scott Pletcher earned 1200 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 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 1200 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 200 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

971 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