|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Tuesday, March 02, 2010 3:16 PM
Points: 8,
Visits: 166
|
|
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
|
|
|
|
|
SSCrazy
Group: General Forum Members
Last Login: Friday, November 27, 2009 5:32 AM
Points: 2,683,
Visits: 1,003
|
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Tuesday, March 02, 2010 3:16 PM
Points: 8,
Visits: 166
|
|
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
|
|
|
|
|
Grasshopper
Group: General Forum Members
Last Login: Tuesday, March 09, 2010 10:18 AM
Points: 10,
Visits: 65
|
|
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.
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Tuesday, March 02, 2010 3:16 PM
Points: 8,
Visits: 166
|
|
| I have turned off autostats before I did the fullscan.
|
|
|
|
|
SSC Veteran
Group: General Forum Members
Last Login: Monday, March 08, 2010 9:32 AM
Points: 205,
Visits: 512
|
|
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...
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Tuesday, March 02, 2010 3:16 PM
Points: 8,
Visits: 166
|
|
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?
|
|
|
|
