Discussion in 'Performance Tuning for DBAs' started by haridba, Oct 11, 2007.
How to know the statistics on tables and indexes are upto date.
Thanks in advance.
SELECT 'Index Name' = i.name,
'Statistics Date' = STATS_DATE(i.id, i.indid)
FROM sysobjects o, sysindexes i
WHERE o.name = 'your table' AND o.id = i.id
When you execute sp_updatestats, it actually shows you whether any updates on statistics happened or not under Messages tab
How do you know if the optimizer thinks it is out of date (date means very little sometimes) and how can you reach this info programtically rather than seeing it in the showplan? I am looking for a job I could run that some t-sql, loop thru stats for each table (or DMV) and tell me what the optimizer thinks needs a look?
This is where it lacks on SQL 2000, with 2005 yes you can hand in with DMVs.
Separate names with a comma.