April 5, 2017 at 10:59 am
Recently I noticed some of my query plans had the yellow warning triangle indicating missing statistics.
I have created the suggested missing statistics but now I am wondering whether there are other missing stats
EXEC sp_autostats <table>
Revealed the lastupdated column on Primary key columns was null - is this expected?
Then DBCC SHOW_STATISTICS ('Table', 'PK_Column')
Shows all nulls in the header and no entries in the other 2 tables
Which leads me to think Primary key columns don't need statistics?
All the PKs I checked were IDENTITY columns.
Is there a standard way of finding missing stats
Or failing that way of querying every key and index column and cross referencing to see if stats exist
April 5, 2017 at 11:21 am
There are was to capture the missing stats. First thing that comes to mind is to run an Extended Event session to capture all of the missing stats notifications.
A question that comes to mind is whether auto create stats is enabled for the database or not. Can you verify if auto stats creation is enabled?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 6, 2017 at 1:09 am
Yes it turns out that auto create statistics, and auto update statistics are off. I know our DBA runs a daily job to update stats, but obviously that won't do anything about missing stats.
April 6, 2017 at 2:11 pm
Tom Brown - Thursday, April 6, 2017 1:09 AMYes it turns out that auto create statistics, and auto update statistics are off. I know our DBA runs a daily job to update stats, but obviously that won't do anything about missing stats.
That solves that mystery.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply