Solved

How to defrag a MS SQL 2008r2 database

Posted on 2014-02-12
11
3,914 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

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

The Orion Papers

Are you interested in becoming an AWS Certified Solutions Architect?

Discover a new interactive way of training for the exam.

Question has a verified solution.

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

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…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

623 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