Using sp_updatestats to update statistics

Last Post 23 Oct 2009 02:34 AM by sumerian. 3 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
sumerian
New Member
New Member

--
21 Oct 2009 03:36 AM
Hi there fellow SQL DBA's. Wonder if you can answer the following question relating to SQL 2005.

From what I have read in books online it appears best to update statistics using the stored procedure sp_updatestats instead of UPDATE STATISTICS xxxx . The reason for this is that it is intelligent enough to look at the sys.sysindexes view and depending on the rowmodctr column value decide whether the statistics should or should not be updated and as such will be updated using the sampling ratio from the old statistics.

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.

My question is - how does sp_updatestats determine the value or threshold in order to update the statistics? Is it based on a percentage of the number of rows changed in the table, fragmentation levels or something else?

All help and comments appreciated.

Ronnie
sumerian
New Member
New Member

--
22 Oct 2009 06:14 AM
Gunneyk - Many thanks for the white paper link which I will read.

The fact that the rowmodctr is no longer used is of interest to me. I am trying to find a way to get the statistics updated as quickly as possible. Running sp_updatestats does seem a lot faster than UPDATE STATISTICS and I am trying to figure out why!!!
sumerian
New Member
New Member

--
22 Oct 2009 06:23 AM
Had a look at the white paper and it clearly states that the rowmodctr is used:

quote:

sp_updatestats: In Microsoft SQL Server 2005, sp_updatestats updates only those statistics that require updating based on the rowmodctr information in the sys.sysindexes compatibility view, thus eliminating unnecessary updates of unchanged items. For databases running under compatibility level 90 or higher, sp_updatestats preserves the automatic UPDATE STATISTICS setting for any particular index or statistics.

sumerian
New Member
New Member

--
23 Oct 2009 02:34 AM
Andrew - thanks again for taking the time to reply. It is appreciated and I can clearly understand what you meant via your first post.

We currently have maintenance jobs that rebuild indexes on a Sunday (AM) and update the statistics on a Wed (AM). The job in question was a 2005 Maintenance plan and it was using the UPDATE STATISTICS t-sql code to automatically update the statistics by default. By using this way was taking an eternity as we would be rebuilding large indexes again where very little or no activity had been seen since the rebuild and it was starting to impact on daily processing.

We have tables in our database that have billions of rows and our daily processing includes a large number of inserts / updates and deletes. My understanding of the auto-update of the statistics was that at least 20% of the rows in the table had to be changed before the stats were updated. Because of the very large number of rows in the tables this quota was not being met and as such the stats were not being kept as healthy as they could be.

Hence the decision to use the sp_updatestats stored proc as it would only update the statistics where the rowmodctr had changed since the last index rebuild. When I implemented this we saw a dramatic increase in the stats being updated. Where the job would normally run for 6 hours using the UPDATE STATISTICS xxxx it only took 18 minutes.

Hope this helps some way in seeing what I am trying to accomplish.


Acceptable Use Policy
---