I have decided to automate the Index Defragmentation process on all tha
tables of my database
with a store proc.

As a starting point, I used an example from SQL Server 2000 Help (Transact
SQL Reference -
DBCC ShowContig topic : Example E : Use DBCC SHOWCONTIG and DBCC INDEXDEFRAG
to defragment
indexes in a database).

I want to only consider indexes with a fragmentation over 20%.

My problem is :
When I run DBCC SHOWCONTIG on a large table (43,000,000 rows, 12 indexes),
it takes an average
of 65 minutes before I get a result.

Code : DBCC SHOWCONTIG  (TFt01_Vente_hebdo)
       WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS

I must consider over 60 tables.

My SQL Server runs on a Windows 2000 Server (SP4), with a dual 2.70 Ghz
Intel Xeon Mp CPU and
7680 MB of RAM.

Would you have any advise helping me increasing thoses performances ?

The perf of SHOWCONTIG (which I wrote) is proportional to the IO
capabilities of your system, and in FAST mode, is also proportional to the
fanout of your table. In FAST mode, it reads all the b-tree pages in the
parent level (one level above the leaf level of the index) - so the larger
the fanout, the smaller the number of pages it has to read.

For the example table you give, you need to do the following for each index:
1) work out index row size, R
2) divide 8000 / R = LF (leaf fanout)
3) divide 43000000 / LF = LN (number of pages required at leaf level)
4) work out index key size, K (this can be different from the row size,
depending on SQL 2005 features used and whether the index is unique or not -
see 2005 BOL for full details)
5) divide 8000 / K = TF (tree fanout)
6) divide LN / TF = P (number of pages SHOWCONTIG has to read for this
index)

Does the table have a clustered index? If not, SHOWCONTIG will have to read
all (i.e. 8000 / data row size) pages.

What's the max IO throughput of your IO subsystem? Are you running
SHOWCONTIG while there's heavy load?

Why do you need to do this for all tables and indexes? Are you able to
correlate increasing logical scan fragmentation with decreasing query
performance, for all these tables and indexes?

Have a read of this whitepaper for more info too - let me know if you have
any further questions.

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx

Regards
Show quoteHide quote

Management Studio - Open File
viewing procedure code in management studio
scripting in sql management studio
SQL Server 2005 Solution Explorer - Sorting Items
Query analyzer oddness