March 22, 2017 at 12:54 pm
Just want to know, Do I need to update statistics on Cluster Index ? I see, last statics was updated on 04/20/2016 on cluster index but non-cluster indexes are up to date.
Thanks
March 22, 2017 at 2:43 pm
Unfortunately the easiest way to look at this is using the old sys.sysindexes catalog view which has the rowmodctr column. SQL Server won't automatically update statistics until 20% + 500 rows have been modified, which could be a long time for large tables:
https://www.sqlskills.com/blogs/erin/understanding-when-statistics-will-automatically-update/
Here's a way to look at the tables and get an idea of rows modified to help determine if you want to update statistics, I use this kind of technique with a scheduled job to force statistics to update based on the rowmodctr value and executing UPDATE STATISTICS command on those tables:
SELECT OBJECT_NAME(i.object_id) AS TableName, MIN(STATS_DATE(i.object_id, i.index_id)) AS OldestStatisticsDate, MAX(si.rowmodctr) AS RecordsModified, MAX(si.rowcnt) AS TotalRecords, MAX(si.rowmodctr)*100.0/MAX(si.rowcnt) AS pct
FROM sys.indexes i
INNER JOIN sys.objects o ON i.object_id=o.object_id
INNER JOIN sys.sysindexes si ON i.object_id=si.id AND i.index_id=si.indid
WHERE o.TYPE <> 'S'
AND STATS_DATE(i.object_id, i.index_id) IS NOT NULL
AND si.rowcnt > 0
AND si.rowmodctr > 0
GROUP BY i.object_id
ORDER BY RecordsModified DESC
March 22, 2017 at 3:51 pm
If the data is changing though, statistics that are a year old is concerning. I agree with the approach the Chris has to see what has been modified. However, assuming we're talking about substantial sets of data (let's call this, as a wildly inaccurate measure, 100,000 rows) I'll bet a goodly amount of a clustered index has changed and needs to be updated. This is especially true since we're talking 2012 and you're not using the new cardinality estimation engine.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply