What is Statistic in Sql Server Part - 2

Interview Question : Does sp_updatestats update the statistics of a table if I just run it or does it use specific formula??

What is Statistic in Sql Server Part - 1

What is Statistic in Sql Server Part - 3

                      Now this is the tricky, Most of you will tell me it will use a formula to update statistics. Which is completely wrong, you are telling about “auto update stats” and I am asking about sp_updatestats.

                      So what the answer???

Books online says sp_updatestats updates only the statistics that require updating based on the
rowmodctr information in the sys.sysindexes catalog view, thus avoiding unnecessary updates of
statistics on unchanged rows ie “sp_updatestats will update a statistic as long as it has had one row
modified”.

 Reference: http://msdn.microsoft.com/en-us/library/dd535534%28v=sql.100%29.aspx

 Still confused?? , try to update just one row in table of 1000 rows and you will find statistics is updated and if no rows is modified no statistics is updated.

 

Query to find how many dml operations happen on objects

-------------SQL 2008 r2 and further ------------------

SELECT [sch].[name] + '.' + [so].[name] AS [TableName] ,
[ss].[name] AS [Statistic],
[sp].[last_updated] AS [StatsLastUpdated] ,
[sp].[rows] AS [RowsInTable] ,
[sp].[rows_sampled] AS [RowsSampled] ,
[sp].[modification_counter] AS [RowModifications],
([sp].[modification_counter]/[sp].[rows]) * 100 AS [RowModifications_ %]
FROM [sys].[stats] [ss]
JOIN [sys].[objects] [so] ON [ss].[object_id] = [so].[object_id]
JOIN [sys].[schemas] [sch] ON [so].[schema_id] = [sch].[schema_id]
OUTER APPLY [sys].[dm_db_stats_properties]([so].[object_id],
[ss].[stats_id]) sp
WHERE [so].[type] = 'U' And [sp].[rows] > 0
AND [sp].[modification_counter] > 0
ORDER BY [sp].[last_updated] DESC;

 

-------------SQL 2005 ----------------------

SELECT [o].[name], [si].[indid], [si].[name], [si].[rowmodctr], [si].[rowcnt], [o].[type]
 ,([si].[rowmodctr] /[si].[rowcnt]) *100 as [% of data modified]
FROM [sys].[objects] [o]
JOIN [sys].[sysindexes] [si] ON [o].[object_id] = [si].[id]
WHERE ([o].[type] = 'U' OR [o].[type] = 'IT')
AND [si].[indid] > 0
AND [si].[rowmodctr] <> 0
and rowcnt >0
ORDER BY [o].[type] DESC, [o].[name];

What is Statistic in Sql Server Part - 1

What is Statistic in Sql Server Part - 3