December 31, 2002 at 1:56 pm
Here is some statistics information.
I run dbcc show_statistics('age_group','PK__Age_Group__75C27486') and get this as output:
Statistics for INDEX 'PK__Age_Group__75C27486'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Then I ran update statistics age_group PK__Age_Group__75C27486, followed by dbcc show_statistics('age_group','PK__Age_Group__75C27486') and get this output:
Statistics for INDEX 'PK__Age_Group__75C27486'.
Updated Rows Rows Sampled Steps Density Average key length
-------------------- ----------- ------------ ----------- ------------------------ ------------------------
Dec 31 2002 10:01AM 5 5 3 0.2 0.0
Question: Is there a way to remove these statistics? Looks like the drop statistics command is not to be used on pk stats.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
December 31, 2002 at 2:12 pm
To the best of my knowledge all index and unique constraints automatically create stats. What do you hope to accomplish as stats are used to determine best index canidates when running queries and you may be going the wrong way to get the results you want.
December 31, 2002 at 2:15 pm
You cannot remove statistics for an index. You remove the index. The purpose of statistical indexes are for situations where there is no index or there are poor ones. Statistics within an actual index are used for the query selection until its determined the prior condition existed, in which case SQL Server creates its own index, a statistical index. Its microsoft's way of telling businesses that it's OK to use databases with no DBA on staff, IMHO. A well indexed and maintained database seldom touches statistical indexes.
Statistical Indexes can be removed with the drop statistics command. They are prefixed with _WA in almost all cases, as far as I know.
Edited by - Scorpion_66 on 12/31/2002 2:18:42 PM
Edited by - Scorpion_66 on 12/31/2002 2:28:00 PM
December 31, 2002 at 2:27 pm
Thats what I thought. About going the wrong way I agree, but here is the puzzle I am trying to solve.
I have a slow running query. Granted it is poorly rewritten, maybe someday I will educate programmers to write better code. I've have re-written it to make it run faster, but still would like to know why the original ran slow.
During an investigation I found the query ran fine when the statistics on PK_AG_GROUP_75C27486 did not have any stats, now with stats it runs slow. There reason for the slow down is without stats the query did a clustered index scan with a WHERE:() predicate, which minimize the number of rows returned. After the stats where created it ran slow, and did not use a WHERE:() predicate more rows where return.
I'm trying to prove that the creation of statistics actually caused the query to start running slow. That is why I would like to be able to go back to a state without stats, but currently I can only do that by restoring the DB.
I'm kind of confused on how a PK got set up without statistics. Or am I misreading the output from my first DBCC SHOW_STATISTICS command?
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
December 31, 2002 at 2:35 pm
Your reading it correctly. I think Microsoft should have put more thought into splitting the tools for working with indexes and the tools for working with statistics out seperately. I don't think they should have called them statistics either, as we have had those for years in our indexes, and it tends to create confusion. Your actually dealing with an index, and the query optimizer could very well be choosing a bad plan because of it. Does the execution plan show the same index being used in both executions? You mention one being clustered with a where clause and one with no where clause but I'm not sure it's the same clustered index, from above. I would expect them to be different if it's affecting the execution time. Also, I've never seen any instances of an index OR statistic changing the inclusion of a where clause. That would worry me. Is it just moved to a different location in the plan?
Oh, and when you finish training your developers, could you train mine as well? LMAO....
Edited by - scorpion_66 on 12/31/2002 4:22:05 PM
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply