Info on SQL Server waits

I'm looking for explanations and troubleshooting
documentation for analyzing SQL Server waits (output from
dbcc sqlperf(waitstats)) other than from this paper:
http://support.microsoft.com/default.aspx?scid=KB;en-
us;q244455
Does anyone have anything more current? Thanks.
RobWhat is it specifically you are looking for? Specific
wait types?
If you are concerned with specific waitstats, and do not
know what the waistat is, then search on www.deja.com. It
is a good resource.
Greg
>--Original Message--
>I'm looking for explanations and troubleshooting
>documentation for analyzing SQL Server waits (output from
>dbcc sqlperf(waitstats)) other than from this paper:
>http://support.microsoft.com/default.aspx?scid=KB;en-
>us;q244455
>Does anyone have anything more current? Thanks.
>Rob
>.
>

Indext extent fragmentation

Hi,
On the databases I manage I notice high (50%+) extent
fragmentation when I run DBCC SHOWCONTIG against the
tables' indexes. I've run dbcc indexdefrag, dbcc
dbreindex, and create index ...with drop existing on these
indexes and they still show the same fragmentation even
after an UPDATE STATISTICS.
Is there any way to eliminate this beyond completely
dropping the indexes and starting from scratch (not a
realistic option)? The procedure I run has virtually
eliminated the logical fragmentation- are there major
performance issues with leaving them as-is?
-DanHi,
If your select statement returns all the records of a table, such table
fragmentation can cause additional page reads which will reduce the
performance of your query and utilize more resource (Memory/CPU / Disk
reads- I/O. So INDEXDEFRAG will increase the performance on fragmented
table.
One more advantage is for fragmented tables, after INDEXDEFRAG / drop and
recreate index, all the fragmented pages will be cleared and you will have
enough
free space in your database.
Thanks
Hari
US Technology
Drop and re-create a clustered index. "Dan Wunder"
<anonymous@.discussions.microsoft.com> wrote in message
news:03c101c399a6$be7b5340$a401280a@.phx.gbl...
> Hi,
> On the databases I manage I notice high (50%+) extent
> fragmentation when I run DBCC SHOWCONTIG against the
> tables' indexes. I've run dbcc indexdefrag, dbcc
> dbreindex, and create index ...with drop existing on these
> indexes and they still show the same fragmentation even
> after an UPDATE STATISTICS.
> Is there any way to eliminate this beyond completely
> dropping the indexes and starting from scratch (not a
> realistic option)? The procedure I run has virtually
> eliminated the logical fragmentation- are there major
> performance issues with leaving them as-is?
> -Dan|||Please read the whitepaper at
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/optimize/ss2kidbp.asp
This explains everything you need to know.
Regards,
Paul.
--
Paul Randal
DBCC Technical Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OAqkB7dmDHA.2592@.TK2MSFTNGP10.phx.gbl...
> Hi,
> If your select statement returns all the records of a table, such table
> fragmentation can cause additional page reads which will reduce the
> performance of your query and utilize more resource (Memory/CPU / Disk
> reads- I/O. So INDEXDEFRAG will increase the performance on fragmented
> table.
> One more advantage is for fragmented tables, after INDEXDEFRAG / drop and
> recreate index, all the fragmented pages will be cleared and you will
have
> enough
> free space in your database.
> Thanks
> Hari
> US Technology
>
> Drop and re-create a clustered index. "Dan Wunder"
> <anonymous@.discussions.microsoft.com> wrote in message
> news:03c101c399a6$be7b5340$a401280a@.phx.gbl...
> > Hi,
> >
> > On the databases I manage I notice high (50%+) extent
> > fragmentation when I run DBCC SHOWCONTIG against the
> > tables' indexes. I've run dbcc indexdefrag, dbcc
> > dbreindex, and create index ...with drop existing on these
> > indexes and they still show the same fragmentation even
> > after an UPDATE STATISTICS.
> > Is there any way to eliminate this beyond completely
> > dropping the indexes and starting from scratch (not a
> > realistic option)? The procedure I run has virtually
> > eliminated the logical fragmentation- are there major
> > performance issues with leaving them as-is?
> >
> > -Dan
>

indexing architechture

OK:

1. after rebuilding indexes, shouldn't sp_updatestats and DBCC UPDATEUSAGE be run for best performance?

2. What exactly are sp_updatestats and update usage doing? It looks like (from BOL) that updating usage would be updating the IAM and the page free space, and updating stats would just update the index/row pointers. Indexes are rebuilt nightly where I am currently working, however, unallocated space is consistently negative.

3. rebuilding or defragging the indexes should defrag the tables, right? As in, re-allocate free space depending on fillfactor...

4. for a reporting database, shouldn't the fillfactor be low? That way, you would have fewer page splits during loading, and as far as querying, by the time you are done with your loading, the engine should have evened out the allocation...

Rebuilding indexes (drop/create or DBCC DBREINDEX) will automatically update statistics.

If you are rebuilding indexes each night, then you probably don't need to worry about the statistics, unless you import bulk data throughout the day, truncate tables or significantly change the data distribution through large amounts of updates before the next index rebuild.

DBCC UPDATEUSAGE simply corrects inaccuracies in the sysindexes table.

sp_updatestats runs UPDATE STATISTICS on all user tables in the database.

If you rebuild a clustered index, it will effectively rebuild the table. There is an optional parameter in the reindex command to specify a new fill factor. If not specified, the original fill factor will be used for the reindex.

DEFRAG defragments the leaf level of an index using the original fill factor.

In summary, if you have the luxury of doing an index rebuild each night, then you're in a good position, and shouldn't really need to worry about defrag or statistics.

|||

THanks...however,

I don't quite understand what UPDATEUSAGE does...we have negative unallocated space on a continual basis. Our database is 156GB, and it shows up with 66GB as negative unallocated. THe only thing that fixes this is UPDATEUSEAGE -

1 - the query analyzer can't find the right query plan with -unallocated, right? I'm thinking that it doesn't have the correct IAM, but I don't know...

2- we do loads every night. Sometimes millions of rows. Unfortunately, the DBAs do the re-indexing BEFORE the load, with default fillfactor of 90. :(

(this was just a whine)

3- can someone tell me exactly what updating stats does that is different from updating useage? Inquiring minds want to know...

4- there are no updates during the day, it's read only for reporting. So, It seems to me that we should set the fillfactor low before the load - however, is it going to negatively impact the reporting? Doesn't SQL server start picking pages and extents with an algorithm that evenly distributes the data on imports and inserts?

5 - In what order should the above items be run?