December 12, 2011 at 7:41 am
when to use update statistics sql server ???
December 12, 2011 at 9:57 am
- at db upgrade time. (after restore, together with all other db maintenance)
we have auto update stats enabled.
We check every now and then how old statistics are and rebuild them if we find they are to old and should already have been refreshed due to inserts/updates/deletes not triggering the autoupdate criteria.
SELECT quotename(object_schema_name(OBJECT_ID)) + '.' + quotename(object_name(OBJECT_ID)) as ObjectName
, quotename(name) AS statistics_name
, STATS_DATE(OBJECT_ID, stats_id) AS statistics_update_date
, stats_id
, object_id
FROM sys.stats
-- WHERE OBJECT_ID = OBJECT_ID('[dbo].[objects]')
order by statistics_update_date desc
, ObjectName ;
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution đ
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 12, 2011 at 2:51 pm
Truely, there is no single "correct" answer to when statistics should be updated, and your post reminds me that I've thought about writing a few words on statistics. Kimberly Tripp has a whole hour of MCM video on the subject statistics. Let me try to make a short version for you: As long as the distribution of values does not change significantly over time, you can pretty much ignore the statistics. So, If you have a uniqueidentifier as a primary key, this is random as long as it is populated with values from newid() or a similar source. Thus, the statistics for this column can normally be totally ignored. For columns where the distribution of values DOES change significantly over time though, statistics should not be neglected. A very good example is an identity column. All new values to an identity colymn is outside of what's covered by the statistics. So, given you have some 20 million records in the table, the statistics won't be updated until there are 4 million updates to the table (20 percent). In this scenario, let's look at the following query:
select * from table where id between 20000000 and 22000000
The query optimizer will estimate that a single row will be returned. What is the most efficient way of doing this? To use an index seek. The problem is, the query does not return one row, but 2000001 rows, that is 2000001 seek operations, most likely causing some 6 to 8 million logical reads. A scan would be way more efficient.
So, my advice would be: Do update the statistics if the change of values actually does make a change for the query optimizer, otherwise you can safely leave it.
December 12, 2011 at 10:23 pm
abhishek_dwivedi03 (12/12/2011)
when to use update statistics sql server ???
You can schedule it with your index maintenace task. As other SSC guys said above there is no "exact" answer to this, you should do it as per your perticular environment. Take help from the script provided by ALZDBA.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply