HiveBrain v1.2.0
Get Started
← Back to all entries
snippetsqlModerate

How to check when statistics was last executed?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
lasthowexecutedstatisticswhenwascheck

Problem

We've been having a number of issues with our indexes lately which our DBA team has attributed to statistics not having been run recently. This has made me wonder - how can I check if statistics have been recently updated via SQL Management Studio?

I apologize if this question isn't explaining this very well - I've only been introduced to statistics until now and prior to this would look to indexes whenever I've had performance related issues.

Edit:

I'm using the following but receiving a syntax error:

use *databasename*
exec sp_autostats *schema.tablename*


The error I'm receiving is:

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '.'.


Why is this?

Solution

For your edit - You need to enclose table name into single quotes:

EXEC sp_autostats 'tablename'


and for the original question

First - find the statistics you want to check:

Second - see its properties, and there you will see the last updated timestamp:

Or you may want to execute the following query:

SELECT t.name TableName, s.[name] StatName, STATS_DATE(t.object_id,s.[stats_id]) LastUpdated 
FROM sys.[stats] AS s
JOIN sys.[tables] AS t
    ON [s].[object_id] = [t].[object_id]
WHERE t.type = 'u'

Code Snippets

EXEC sp_autostats 'tablename'
SELECT t.name TableName, s.[name] StatName, STATS_DATE(t.object_id,s.[stats_id]) LastUpdated 
FROM sys.[stats] AS s
JOIN sys.[tables] AS t
    ON [s].[object_id] = [t].[object_id]
WHERE t.type = 'u'

Context

StackExchange Database Administrators Q#13167, answer score: 15

Revisions (0)

No revisions yet.