SQL Server Performance Forum – Threads Archive

Sample statistics

All-
I have a question… of course…
I run update statistics with 50 sample each week.
Each early monring I run sp_updatestats.
Today, I had a view that became very slow. when I ran sp_updatestats or a plain update statistics on one of the tables involved the query didnt change. when I ran update statistics with 50 sample on that table, it flew. The question is how/should/can I increase the default sample size when sp_updatestats are run on this table? I thought it should take from the last update stats. Is this set when we run create statistics?
I want to keep this from occuring on this table that does recieve a lot of updates, but also dont have much of a weekday window for maintenace. Ideas? Mike Michael
MCDBA "The statistics on sanity are that one out of every four persons is suffering from some sort of mental illness. Think of your three best friends — if they’re okay, then it’s you!"

I appolgize.. it does not recieve a lot of inserts/updates.. that makes it even more questionable.. and opens up other issues? Michael
MCDBA "The statistics on sanity are that one out of every four persons is suffering from some sort of mental illness. Think of your three best friends — if they’re okay, then it’s you!"

Are there any computed columns or indexed views on this database?
Try with WITH FULLSCAN, NORECOMPUTE periodically, when automatic statistics with the default sampling rate are not sufficient, you can control statistics sampling rates, and creation and update time, explicitly. http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx is a good reference in this case. (I’m going blog this one, its interesting) Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
@http://www.askasqlguru.com/ This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

Satya, There is no computed columns or indexed views on this table. There is one indexed view on the db though. The curious thing is that there are so few updates to this table. The row count is close to 800K but the number of updates were 68 rows (obtained from the sysindexes table). so even if the sample was bigger I am unsure how running a 50 percent sample would make for better performance. There has to be something else that this is just lightly fixing. dont you agree? I say this since I totally rebuild all indexes on the weekend and update those stats<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> Mike Michael MCDBA "The statistics on sanity are that one out of every four persons is suffering from some sort of mental illness. Think of your three best friends — if they’re okay, then it’s you!"

also, the estimated row count for the query was almost the same as actual when comparing the actual plan against the estimated plan. Michael
MCDBA "The statistics on sanity are that one out of every four persons is suffering from some sort of mental illness. Think of your three best friends — if they’re okay, then it’s you!"

From BOL…
In SQL Server 2005, sp_updatestats updates only those statistics that require updating based on the rowmodctr information in the sys.sysindexes compatibility view; therefore, preventing unnecessary updates of unchanged items.
I believe you can use UPDATE STATISTICS command to use the sample instead of sp_updatestats…
MohammedU.
Microsoft SQL Server MVP
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Have you reviewed the reference I gave above, as it speaks all about update stats and sampling in this case.

quote:Originally posted by MikeEBS also, the estimated row count for the query was almost the same as actual when comparing the actual plan against the estimated plan. Michael
MCDBA "The statistics on sanity are that one out of every four persons is suffering from some sort of mental illness. Think of your three best friends — if they’re okay, then it’s you!"

Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
@http://www.askasqlguru.com/ This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

Satya, Yah. blew my mind<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> So much info. But I am still unsure. I keep thinking that it is not stats but something else since it is so little of changes. MohammedU. Yah I can run sample and do during weekend. I dont think I should have to run during week. thoughts? Mike Michael MCDBA "The statistics on sanity are that one out of every four persons is suffering from some sort of mental illness. Think of your three best friends — if they’re okay, then it’s you!"

How often you run with FULLSCAN, NORECOMPUTE option? Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
@http://www.askasqlguru.com/ This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

I run only sample 50 weekly. which should be OK. but I do need to recompute. I agree. I will put that into place for next week’s window.
The thing is this is not a table that grows a lot compared to a lot of others we have.
Michael
MCDBA "The statistics on sanity are that one out of every four persons is suffering from some sort of mental illness. Think of your three best friends — if they’re okay, then it’s you!"

I mean. actually that I need to resample. I think the recompute is good. Michael
MCDBA "The statistics on sanity are that one out of every four persons is suffering from some sort of mental illness. Think of your three best friends — if they’re okay, then it’s you!"

aww heck.. may as well do fullscan Michael
MCDBA "The statistics on sanity are that one out of every four persons is suffering from some sort of mental illness. Think of your three best friends — if they’re okay, then it’s you!"

also: This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Michael
MCDBA "The statistics on sanity are that one out of every four persons is suffering from some sort of mental illness. Think of your three best friends — if they’re okay, then it’s you!"

Thats what I have mentioned on my first reply to you to test [<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />]. You need a holiday [<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />] Satya SKJ Microsoft SQL Server MVP Writer, Contributing Editor & Moderator http://www.SQL-Server-Performance.Com</a> @<a target="_blank" href=http://www.askasqlguru.com/>http://www.askasqlguru.com/</a> <center>This posting is provided AS IS with no rights for the sake of knowledge sharing. <hr noshade size="1">Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.</center>

Yah.. I know. I just resist the fullscan due to time needed.<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> Michael MCDBA "The statistics on sanity are that one out of every four persons is suffering from some sort of mental illness. Think of your three best friends — if they’re okay, then it’s you!"

Instead full scan you can reindex tables with ONLINE = ON option if you are using EE…
MohammedU.
Microsoft SQL Server MVP
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

.. I bet still you need to perform UPDATE STATISTICS if SAMPLING is used. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
@http://www.askasqlguru.com/ This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

Using SE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ /> Michael MCDBA "The statistics on sanity are that one out of every four persons is suffering from some sort of mental illness. Think of your three best friends — if they’re okay, then it’s you!"

I don’t think EE and SE would matter in this case, as Online Index Operations are only limited to EE>
Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
@http://www.askasqlguru.com/ This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

But still the question remains, how can a table that gets very few updates need full every couple days to keep it fast? could it be bad indexing? I am considering that (I didnt do them – honest!)<img src=’/community/emoticons/emotion-1.gif’ alt=’

I guess this could be an index fragmentation than the table, take help from IndexFragmentation and DetectFragmentation articles.

I defrag the indexes each morning. I am stupified

]]>