DBCC SHOWCONTIG Performances

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/pro.../ss2kidbp.mspx
Regards
Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Marc G." <Marc G.@.discussions.microsoft.com> wrote in message
news:5F40205F-2835-4AA1-9B9E-D43D32E6CABE@.microsoft.com...
>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 ?

dbcc proccache units

what are the units for proc cache size? i'm assuming they're in kb and
want to make sure. thanks,
arthurIt seems to be pages.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"arthur" <alangham@.gmail.com> wrote in message
news:1106239956.487457.76280@.z14g2000cwz.googlegroups.com...
> what are the units for proc cache size? i'm assuming they're in kb and
> want to make sure. thanks,
> arthur
>|||how'd you figure that out? i'm trying to reconcile the performance
monitor's sqlserver:cache manager procedure plans w/ the results from
dbcc proccache. basically what i eventually need to find out is how
much space there is total in the procedure cache.
thanks,
arthur|||You can also run DBCC MEMORYSTATUS.
When I do that, I get the same value for Procedure Cache, Total Pages as I
do for Proc Cache Size in DBCC PROCCACHE. Since I get the same number, I
figure it's the same unit.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"arthur" <alangham@.gmail.com> wrote in message
news:1106249027.345721.114590@.f14g2000cwb.googlegroups.com...
> how'd you figure that out? i'm trying to reconcile the performance
> monitor's sqlserver:cache manager procedure plans w/ the results from
> dbcc proccache. basically what i eventually need to find out is how
> much space there is total in the procedure cache.
> thanks,
> arthur
>sql

dbcc proccache units

what are the units for proc cache size? i'm assuming they're in kb and
want to make sure. thanks,
arthur
It seems to be pages.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"arthur" <alangham@.gmail.com> wrote in message
news:1106239956.487457.76280@.z14g2000cwz.googlegro ups.com...
> what are the units for proc cache size? i'm assuming they're in kb and
> want to make sure. thanks,
> arthur
>
|||how'd you figure that out? i'm trying to reconcile the performance
monitor's sqlserver:cache manager procedure plans w/ the results from
dbcc proccache. basically what i eventually need to find out is how
much space there is total in the procedure cache.
thanks,
arthur
|||You can also run DBCC MEMORYSTATUS.
When I do that, I get the same value for Procedure Cache, Total Pages as I
do for Proc Cache Size in DBCC PROCCACHE. Since I get the same number, I
figure it's the same unit.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"arthur" <alangham@.gmail.com> wrote in message
news:1106249027.345721.114590@.f14g2000cwb.googlegr oups.com...
> how'd you figure that out? i'm trying to reconcile the performance
> monitor's sqlserver:cache manager procedure plans w/ the results from
> dbcc proccache. basically what i eventually need to find out is how
> much space there is total in the procedure cache.
> thanks,
> arthur
>

dbcc proccache units

what are the units for proc cache size? i'm assuming they're in kb and
want to make sure. thanks,
arthurIt seems to be pages.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"arthur" <alangham@.gmail.com> wrote in message
news:1106239956.487457.76280@.z14g2000cwz.googlegroups.com...
> what are the units for proc cache size? i'm assuming they're in kb and
> want to make sure. thanks,
> arthur
>|||how'd you figure that out? i'm trying to reconcile the performance
monitor's sqlserver:cache manager procedure plans w/ the results from
dbcc proccache. basically what i eventually need to find out is how
much space there is total in the procedure cache.
thanks,
arthur|||You can also run DBCC MEMORYSTATUS.
When I do that, I get the same value for Procedure Cache, Total Pages as I
do for Proc Cache Size in DBCC PROCCACHE. Since I get the same number, I
figure it's the same unit.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"arthur" <alangham@.gmail.com> wrote in message
news:1106249027.345721.114590@.f14g2000cwb.googlegroups.com...
> how'd you figure that out? i'm trying to reconcile the performance
> monitor's sqlserver:cache manager procedure plans w/ the results from
> dbcc proccache. basically what i eventually need to find out is how
> much space there is total in the procedure cache.
> thanks,
> arthur
>

DBCC DBREINDEX not working

I have a stored proc that identifies indexes that need to be
defragmented ( LogicalFragmentation > 20% ) using DBCC SHOWCONTIG.
This works fine and shows me all of the candidate indexes to be
defragged. I am using DBCC DBREINDEX to rebuild the indexes. What I
noticed yesterday is that it seems that DBREINDEX is not actually doing
anything. I would check the target indexes, run DBREINDEX, then check
again. The same list of indexes came up unchanged.

What is strange is although I am not specifying "WITH NO_INFOMSGS" this
is the only output from the DBREINDEX:
"DBCC execution completed. If DBCC printed error messages, contact your
system administrator."

According to BOL DBREINDEX should return a result set if "NO_INFOMSGS"
is not specified, but if "NO_INFOMSGS" is supplied the prior message is
what is returned. This doesn't seem to be working correctly, or if it
is, I have no idea where any error messages are being returned.

Anyone have any ideas?In this case, the documentation from Books Online is incorrect. The
documentation has been corrected in SQL Server 2005, where it says:

Result Sets

Whether any one of the options, except NO_INFOMSGS, is specified
(the table name must be specified), DBCC DBREINDEX returns:

DBCC execution completed. If DBCC printed error messages,
contact your system administrator.

Razvan|||Nice documentation.

Thanks for the heads-up, Razvan.