39

I have a situation that I can't understand. My SQL Server execution plan tells me that I have missing statistics on the table, but the statistics are already created:

Warning

But if we look at the table, we will see that there is a statistic that has been created automatically:

enter image description here

Can somebody help to understand how it can be?

Auto_Update and Auto_Create statistics is turned on on the current DB.

I am using SQL Server 2014.

Improve this question
0

3 Answers 3

Reset to default
24

The warning does not always correspond to missing single-column statistics.

There is no easy way to precisely determine the exact statistic the optimizer went looking for and did not find in all cases, but it is almost always a multi-column statistic that would provide some correlation information across multiple equality predicates in the query.

Perhaps in your case it corresponds exactly with the predicates in the Filter following the scan where the warning appears.

It would be nice if SQL Server reported all columns for the 'missing statistic' warning, but that is not how it works today, unfortunately.

A certain amount of trial-and-error may be necessary to identify the multi-column statistic you need to create to remove the warning. Note that multi-column statistics cannot be created automatically. Despite the warning, you may or may not find the quality of the estimate improves even if the missing statistic is provided.

Improve this answer
10

Since it seems clear that a statistic already exists for the particular column, I offer two possibilities:

  1. Object Explorer is pointed to a different database - I'm suspicious because the database has DEV in the name, so it's possible that in one case you're looking at dev and in another you're not.
  2. The plan being used is from before the statistic was created (either it was generated earlier or, less likely, something prevented a recompile to be triggered when the statistic was created).

Please validate that the simple answer (1) is not the issue, and try issuing the same query with OPTION (RECOMPILE) to either confirm or rule out (2).

Improve this answer
1
  • 1
    Aaron Bertnand, thanks a lot for your reply. But Object Explorer is pointed to the current db and adding OPTION (RECOMPILE) does not fix the issue. I want to add also that my query is executing a stored procedure.
    – Artashes Khachatryan
    Commented May 27, 2015 at 7:34
8

I have just noticed that when you get such warnings about statistics, if you run your query or procedure with the settings below:

SET SHOWPLAN_ALL ON

enter image description here

you can see what are the missing statistics:

enter image description here

and using the scripts here you can have a look at what stats are currently in place, and if missing you can add them.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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