asked on

SQL DBCC indexdefrag where average fragmentation > 15

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

ASKER CERTIFIED SOLUTION

ValentinoV

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

asmyatt

ASKER

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?

SOLUTION

lcohan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ValentinoV

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)

ValentinoV

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/

ValentinoV

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.

lcohan

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.

ValentinoV

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" :)

asmyatt

ASKER

I don't see DBCC INDEXDEFRAG anywhere. Where are you suggesting it be replaced with ALTER INDEX?

lcohan

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!

ValentinoV

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

SQL DBCC indexdefrag where average fragmentation &gt; 15

SQL DBCC indexdefrag where average fragmentation > 15