Solved

How to defrag a MS SQL 2008r2 database

Posted on 2014-02-12
11
3,589 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:Scott Pletcher
Scott Pletcher 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

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:Scott Pletcher
Scott Pletcher 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
 

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:
Scott Pletcher 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:Scott Pletcher
Scott Pletcher 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

In this article I will describe the Copy Database Wizard 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.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

785 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