?
Solved

SQL DBCC indexdefrag where average fragmentation > 15

Posted on 2011-10-24
11
Medium Priority
?
547 Views
Last Modified: 2012-05-12
Hi Experts,

I need to run a DBCC indexdefrag command for each row returned on the attached query. Basically I need to execute this command from the fields in the query:

dbcc indexdefrag(SDDIPS.[database_id],"SDDIPS.[object_id]",SSI.[name])

If I'm going about this the wrong way, please let me know as I'm looking to setup a weekly schedule to defrag the db where needed.

Thanks.


USE INTERLINQE3

SELECT DB_NAME(SDDIPS.[database_id]) AS [database_name],  
        OBJECT_NAME(SDDIPS.[object_id], DB_ID()) AS [object_name],  
        SSI.[name] AS [index_name], SDDIPS.partition_number,  
        SDDIPS.index_type_desc, SDDIPS.alloc_unit_type_desc,  
        SDDIPS.[avg_fragmentation_in_percent], SDDIPS.[page_count]  
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'detailed') SDDIPS  
        INNER JOIN sys.sysindexes SSI WITH (NOLOCK)  
                ON SDDIPS.OBJECT_ID = SSI.id  
                        AND SDDIPS.index_id = SSI.indid  
WHERE SDDIPS.page_count > 30  
        AND avg_fragmentation_in_percent > 15  
        AND index_type_desc <> 'HEAP' 
        
ORDER BY OBJECT_NAME(SDDIPS.[object_id], DB_ID()), index_id

Open in new window

0
Comment
Question by:asmyatt
  • 6
  • 3
  • 2
11 Comments
 
LVL 37

Accepted Solution

by:
ValentinoV earned 1000 total points
ID: 37023890
No need to reinvent the wheel, check this script out: http://sqlfool.com/2011/06/index-defrag-script-v4-1/
0
 
LVL 2

Author Comment

by:asmyatt
ID: 37024512
This looks good.

I basically executed the entire script which created some tables and the stored procedure. Then I ran  EXECUTE dbo.dba_indexDefrag_sp

Should that be all that is needed?
0
 
LVL 40

Assisted Solution

by:lcohan
lcohan earned 1000 total points
ID: 37024810
Theoreticaly yes however....

The code from the article does not uses the "DBCC INDEXDEFRAG" command but ALTER TABLE .... REORGANIZE and even though SQL BOL says:

"REORGANIZE
Specifies the index leaf level will be reorganized. This clause is equivalent to DBCC INDEXDEFRAG. ALTER INDEX REORGANIZE statement is always performed online."

http://msdn.microsoft.com/en-us/library/ms188388.aspx

I personaly found that it doesnt do much maybe just because "This clause is equivalent to DBCC INDEXDEFRAG" but not DBCC INDEXDEFRAG.

Besides that please see below and think what you need to do based on your data and your index(es) fragmentation:
"...DBCC INDEXDEFRAG is an online operation. It does not hold locks long term. Therefore, DBCC INDEXDEFRAG does not block running queries or updates. Because the time to defragment is related to the level of fragmentation, a relatively unfragmented index can be defragmented faster than a new index can be built. A very fragmented index might take considerably longer to defragment than to rebuild. "

http://msdn.microsoft.com/en-us/library/ms177571.aspx

In my case having to deal with a pretty large SQL db behind a 24*7 e-commerce web site havind tables with hundreds of millions of rors I tried them all and so far the best ballanced aproach was to REBUILD with ONLINE = ON all fragmented indexes as follow:

for tables having > 10,000,000 rows and FRAGMENTATION > 5% -- REBUILD
for tables having < 10,000,000 rows and FRAGMENTATION > 10% -- REBUILD

Also please have a look at the
"scanMode = N'LIMITED'
      /* Options are LIMITED, SAMPLED, and DETAILED "
from the code at the link posted an consider that you could choose different scan mode than LIMITED for a more accurate "FRAGMENTATION" (but slower) reading FROM sys.dm_db_index_physical_stats.

Hope this helps...
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
LVL 37

Expert Comment

by:ValentinoV
ID: 37024844
The code from the article does not uses the "DBCC INDEXDEFRAG" command but ALTER TABLE ....

"This feature will be removed in a future version of Microsoft SQL Server. Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible. Use ALTER INDEX instead."

(from http://msdn.microsoft.com/en-us/library/ms181671.aspx)
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 37024917
for tables having > 10,000,000 rows and FRAGMENTATION > 5% -- REBUILD
for tables having < 10,000,000 rows and FRAGMENTATION > 10% -- REBUILD

That's why the script has configurable thresholds.  In general it should work fine with the defaults but indeed, in specific situations you may want to choose other settings.  You really need to try it out an test before knowing if the change in settings has a positive or negative impact.

For interesting posts/discussions on the topic, have a look at Brent Ozar's blog: http://www.brentozar.com/archive/2009/02/index-fragmentation-findings-part-1-the-basics/
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 37024954
I basically executed the entire script which created some tables and the stored procedure. Then I ran  EXECUTE dbo.dba_indexDefrag_sp
Should that be all that is needed?

Well, that means you've executed it with the default settings:
  o fragmentation between 10% and 30% -> reorganize
  o fragmentation above 30% -> rebuild

Also, you've now probably defragmented all databases, unless you specified a different parameter for @database.

If your indexes are being defragmented sufficiently according to your expectations then that is indeed all that's needed.
0
 
LVL 40

Expert Comment

by:lcohan
ID: 37024978
All true however as we both mentioned - " You really need to try it..." first then decide what's best for you.

I just wanted to help by sharing my experience (and knowledge) working with it where I found that so far REBUILD WITH ONLINE=ON works excellent in SQL (2005/2008 and again - that is for me) plust it  covers all aspects including much easier coding and faster execution.
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 37025082
lcohan: no worries, in general we both agree :-)

ONLINE is supported by the script as well, check out the @onlineRebuild param.  It's even the default.

It does have some limitations, such as: "Online index operations are available only in SQL Server Enterprise, Developer, and Evaluation editions."
(from http://technet.microsoft.com/en-us/library/ms188388.aspx)

Plus, it will also fail if your table contains the following:

  o XML index
  o Spatial index
  o Large object data type columns: image, text, ntext, varchar(max), nvarchar(max), varbinary(max), and xml

Or, in other words, as with many things related to SQL Server, "it depends" :)
0
 
LVL 2

Author Comment

by:asmyatt
ID: 37025525
I don't see DBCC INDEXDEFRAG anywhere. Where are you suggesting it be replaced with ALTER INDEX?
0
 
LVL 40

Expert Comment

by:lcohan
ID: 37025638
for SQL 2005 you find it here: http://msdn.microsoft.com/en-us/library/ms177571(v=SQL.90).aspx
for SQL 2008 you find it here http://msdn.microsoft.com/en-us/library/ms177571(v=SQL.100).aspx

Microsoft still says in SQL 2008 that it will be removed in a future version however.....can they??
Anyway - good luck!
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 37026046
They sure can but whether they'll go ahead with it or not remains to be seen with each new release.  As some evidence that they actually do remove functionality, here's the list of features removed from 2008 R2: http://msdn.microsoft.com/en-us/library/ms144262(v=SQL.105).aspx
0

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

Question has a verified solution.

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

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

850 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