September 21, 2009 at 10:30 pm
Hi,
Can anyone help? sp_updatestates is only sampling 1% of my data when executed. I recently discovered some poor performing queries and determined that my index statistics are up to date, but only 1% of the data was used to create the statistics. After manually rebuilding the statistics with UPDATE STATISTICS using the “FULLSCAN” option, the query response times increased significantly.
I have turned off Auto Update Statistics and then manfully rebuild all index statistics with code similar to this:
UPDATE STATISTICS tblOASIS_GLPosting CIX_tblOASIS_GLPosting WITH FULLSCAN
I then schedule sp_updatestats to run each night like this:
Exec sp_updatestats @resample='RESAMPLE'
Every time an index statistics is updated by sp_updatestats, it only uses 1% of the data to create it. My understanding is that sp_updatestats, when the @resample parameter is set to ‘RESAMPLE’, will use the last value as a sample rate that it was built with. In this case, it is 100% as I used FULLSCAN.
I have schedule sp_updatestats on two different databases. One database is partitioned and the other once is not. I only seem to have issues with the partitioned database.
I am running SQL 2005 Enterprise edition 64BIT on Windows 2003.
Any help or suggestions will be appreciated.
Thanks
September 22, 2009 at 1:51 am
Remember to take this into account.
sp_updatestats updates only the statistics that require updating based on the rowmodctr information in the sys.sysindexes catalog view, thus avoiding unnecessary updates of statistics on unchanged rows.
I would try instead of fullscan, specifying a percentage.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 22, 2009 at 7:42 pm
rowmodctr is not updated for partitioned tables...that is part of my problem.
See this link for more info: http://msdn.microsoft.com/en-us/library/ms190283.aspx
October 1, 2009 at 11:28 am
You are correct when you state that sp_updatestats will use the last sampling rate. BUT, in order to ensure that it will use the FULL from your UPDATE STATISTICS statement you would need to turn off auto update statistics as a database option. So I would guess that between the time you ran UPDATE STATISTICS and sp_updatestats, an auto update occurred which reset the sampling rate.
October 4, 2009 at 4:31 pm
I have turned off autostats before I did the fullscan.
October 4, 2009 at 4:41 pm
Is the Maintenance plan task "Update statistics " same as running sp_updatestats??
In SQL Server 2005, we do not need to do sp_updatestats or Maintenance plan task "Update statistics " as the statistics in SQL Server 2005 are updated automatically..
Is the above statement correct??
please clarify me...
October 4, 2009 at 4:53 pm
Sp_updatestate is a Microsoft system stored procedure and it uses UPDATE STATISTICS to update the stats. However, sp_updatestats only updates the statistics that requires updating. This is determined in the sp_updatestats stored procedure by a function called “stats_ver_current”. This function is not documented, nor can you view the code for it or use it in other stored procedures.
Updating stats automatically can be turned of in SQL server, 2000, 2005 and 2008.
To summarise, I need a method of updating stats using 100% of my data to calculate the stats, but only update the statistics that requires updating. This must be done on a partitioned database.
Does this make sense?
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply