DBCC SHOW_STATISTICS

Question

DBCC SHOW_STATISTICS

Jeff W

SSCertifiable

Points: 5207
More actions
June 4, 2002 at 2:53 pm

#151698

I am trying to isolate why my queries have stopped using a helpful index.
I ran show_statistics on the index and I noticed a scan density of 0 (zero). Does this mean anything to the optimizer? I thought low density meant high selectivity.
Jeff

"Keep Your Stick On the Ice" ..Red Green

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Antares686 SSC Guru Points: 125444 More actions · Answer 1

I have never seen a density of 0. I don't think that is normal. Have you tried sp_updatestats and run DBCC again. ALso may try runnign DBCC UPDATEUSAGE to make sure the index is being reported properly.

"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

Jeff W SSCertifiable Points: 5207 More actions · Answer 2

I ran sp_updatestats and dbcc updateusage and those readings stayed the same. That's when I made the post. I can't see how a scan density would be 0 either. Right now we are having performance issues and I attribute it to something related to the optimizer.

thanks

"Keep Your Stick On the Ice" ..Red Green

Antares686 SSC Guru Points: 125444 More actions · Answer 3

Unless you are trying the details of some system item I cannot find I cannot find anything similar to this. My suggestion would be drop the index and recreate, then see if it fixes it.

"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

Jeff W SSCertifiable Points: 5207 More actions · Answer 4

Thanks for the input 🙂

I recreated the indexes today and on some of them the density levels changed. (I had many indexes and stats w/ 0 density levels.) I also ran update statistics w/ a full scan and I have rectified all but one index so far. The applications are picking up speed again.

I think I have found a bug in 7.0 sp2. It may be hard to pin point it, but I think we have one here.

"Keep Your Stick On the Ice" ..Red Green

Antares686 SSC Guru Points: 125444 More actions · Answer 5

Sorry, doesn't sound like a bug with SP as I run SP on a prod machine right now simply because I have had no need to upgrade. No issues. Could be something caused the issue with your install and an SP update may fix anyway. I also checked SP3 and 4 and no mention of this type issue.

"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)