October 28, 2014 at 6:34 am
When I run this Glenn Berry's query on my server:
-- When were Statistics last updated on all indexes? (Query 51) (Statistics Update)
SELECT o.name, i.name AS [Index Name],
STATS_DATE(i.[object_id], i.index_id) AS [Statistics Date],
s.auto_created, s.no_recompute, s.user_created, st.row_count
FROM sys.objects AS o WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK)
ON o.[object_id] = i.[object_id]
INNER JOIN sys.stats AS s WITH (NOLOCK)
ON i.[object_id] = s.[object_id]
AND i.index_id = s.stats_id
INNER JOIN sys.dm_db_partition_stats AS st WITH (NOLOCK)
ON o.[object_id] = st.[object_id]
AND i.[index_id] = st.[index_id]
WHERE o.[type] = 'U'
ORDER BY STATS_DATE(i.[object_id], i.index_id) ASC OPTION (RECOMPILE);
-- Helps discover possible problems with out-of-date statistics
-- Also gives you an idea which indexes are the most active
I get around 400 outputs. out of them 200 have 'Statistics Date' is 'Null' around 100+ date are older than 3 months to a year. Only around 50 have 'Statistics Date' within a month.
My question is what should I do with the 'Null' dates ? And also what to do with 3+ months old dates?
My server is up for around 3 months.
Thanks in advance.
October 28, 2014 at 6:42 am
The ones with dates of NULL are statistics on empty tables.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 28, 2014 at 6:54 am
Thank for your quick reply. Would you please tell me what is the 'baseline' here. Should I update old statistics older than month or so? or..
October 28, 2014 at 6:58 am
There isn't one.
Statistics 2 years old could be fine if the table is static. Statistics 2 hours old could be outdated if the table changes fast.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 28, 2014 at 7:31 am
On most databases, most of the time, if you have automatic statistics maintenance enabled and you have auto-create statistics enabled, you're getting the statistics you need and probably don't have to do anything. You can't just look at the statistics and understand if you need statistics maintenance. You also have to know how active your data is, as Gail has pointed out, and you need to understand how your queries are behaving. All that data is combined with the information from your statistics to determine if you need additional statistics maintenance or not.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
October 28, 2014 at 11:34 am
I ran the script and got Statistics Date=NULL where table has data.
name Index NameStatistics Dateauto_createdno_recomputeuser_createdrow_count
......
CLIENTPK_CLIENT NULL 0 0 0 177697
October 28, 2014 at 11:46 am
It is NULL when Statistics never been updated
October 28, 2014 at 1:14 pm
Barcelona10 (10/28/2014)
It is NULL when Statistics never been updated
Nope, they're 'updated' at the time they are created if at the time the table has data. Bear in mind that statistics are actually never updated. An update statistics recreates the stats object.
You have auto update turned off at the database level?
Probably also NULL if the index is disabled.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 28, 2014 at 2:54 pm
auto update is ON
index is enabled.
last Update:(never)
probably when it is "never" or other than date then NULL
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply