Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers

up vote 5 down vote accepted

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:

choose statistics

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

enter image description here

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'
share|improve this answer
    
Thank-you very much –  ElvisLikeBear Feb 16 '12 at 7:02
add comment

The best way to get information about statistics is through the command

DBCC SHOW_STATISTICS (<tablename>,<indexname>)

That will return information not just about when the stats where updated, but their size, density, how selective they are, and the histogram that shows the distribution of data. With all that, you can determine if those stats are up to date and effective.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.