## January 2012 - Posts

Vital Statistics

I was asked the other day if there was any DMVs similar to the index fragmentation DMVs but for statistics. Unfortunately the answer is no! You can tell when a statistic was last by using DBCC SHOW_STATISTICS e.g.

DBCC SHOW_STATISTICS ( 'Sales.ContactCreditCard' , _WA_Sys_00000002_1A14E395 )

Which returns the updated date in the first set of results:

 Name Updated Rows Rows Sampled Steps Density Average key length String Index Filter Expression Unfiltered Rows _WA_Sys_00000002_1A14E395 Jan 15 2012  8:46PM 19118 19118 4 1 4 NO NULL 19118

But this may get a little tiring if you have a few statistics that you need to check. It is easier to look at the sys.stats table and STATS_DATE function using a query similar to:

SELECT sc.name, o.name
, STATS_DATE(o.object_id,s.stats_id) AS [stats_date], s.*
FROM sys.stats s
JOIN sys.objects o ON o.object_id = s.object_id
JOIN sys.schemas sc ON sc.schema_id = o.schema_id

This will enable you to look at statistics for all tables or can be limited to look at one table using a where clause:

SELECT sc.name, o.name
, STATS_DATE(o.object_id,s.stats_id) AS [stats_date], s.*
FROM sys.stats s
JOIN sys.objects o ON o.object_id = s.object_id
JOIN sys.schemas sc ON sc.schema_id = o.schema_id
WHERE o.Name = ‘Contacts’

Using this you can tell how old the stats are but not necessarily how out of date they are. For instance if the data in the table is static, the statistics may never need to be updated.

So how can you tell if they may be out of date?

I mulled over the possibility of using the rowmodctr and rowcnt values in sysindexes to determine how many rows had changed compared to how many rows there is in the table. Looking at Books Online for sysindexes it says the following for rowmodctr

In earlier versions of SQL Server, the Database Engine maintained row-level modification counters. Such counters are now maintained at the column level. Therefore, the rowmodctr column is calculated and produces results that are similar to the results in earlier versions, but are not exact.

If you use the value in rowmodctr to determine when to update statistics, consider the following solutions:

• Do nothing. The new rowmodctr value will frequently help you determine when to update statistics because the behavior is reasonably close to the results of earlier versions.
• Use AUTO_UPDATE_STATISTICS. For more information see, Using Statistics to Improve Query Performance.
• Use a time limit to determine when to update statistics. For example, every hour, every day, or every week.
• Use application-level information to determine when to update statistics. For example, every time the maximum value of an identity column changes by more than 10,000, or every time a bulk insert operation is performed.

If you use sp_updatestats to update the statistics, you can get the messages like:

Updating [Sales].[ContactCreditCard]
[PK_ContactCreditCard_ContactID_CreditCardID], update is not necessary...
[_WA_Sys_00000002_1A14E395], update is not necessary...
0 index(es)/statistic(s) have been updated, 2 did not require update.

when the statistics don’t need updating. So how does it determine this?

The answer is that it only updates the statistics when rowmodctr is non-zero i.e. any changes to the data. it will also update statistics if the are not version current (I assume this when a database has been upgraded).

if ((@ind_rowmodctr <> 0)
or ((@is_ver_current is not null)
and (@is_ver_current = 0
)))
begin

--

-- cut --

--
end
else
begin

--raiserror('    %s, update is not necessary...', -1, -1, @ind_name_quoted)
raiserror(15653, -1, -1, @ind_name_quoted
)
select @skipped_count = @skipped_count + 1
end

So it seems you may still need to develop your own solution to determine when statistics need updating if you need more control of when they are updated.

Filed under: