Warning of No statistics on a column

Question

For a query execution plan is showing warning of no statistics on a column but the statistics for that column exists.

Answer 1

Please read Paul's reply

https://dba.stackexchange.com/questions/102467/warning-for-missing-statistics-in-execution-plan

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence

Answer 2

I created one statistics including all the columns appeared in the preceding filter operation but the warning is still there.

Answer 3

Ok, but have you checked out that optimizer created good execution plan? 

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence

Answer 4

I cant say whether the plan is good, however it looks ok to me. the warning is seen.

Answer 5

Can you show the whole query? On what columns a NCI is defined?

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence

Answer 6

Following is the query:

 

SELECT @Result = COALESCE(@Result + ', ', '') +  ofacKey
    FROM (
        SELECT distinct TOP 20  
            OFAC.ofacKey  
        FROM blacklist OFAC (nolock)
        WHERE
        ( 
            (ofac.contact=@sContactNo)
            OR (ofac.dob=@iDate    AND ofac.idNumber=@sIdNo AND ofac.idPlaceIssue=@sIdIssuedPlace) 
            OR (
                    (
                        OFAC.name2 = @newFnameComb1
                        OR OFAC.name2 = @newFnameComb2
                        OR OFAC.name2 = @newFnameComb3
                        OR OFAC.name2 = @newFnameComb4
                        OR OFAC.name2 = @newFnameComb5
                        OR OFAC.alias2 = @newFnameComb1
                        OR OFAC.alias2 = @newFnameComb2
                        OR OFAC.alias2 = @newFnameComb3
                        OR OFAC.alias2 = @newFnameComb4
                        OR OFAC.alias2 = @newFnameComb5                        
                    ) 
                    AND ofac.idNumber=@sIdNo
            )

            OR    (
                    ofac.dob=@iDate AND (
                                        OFAC.name2        = @newFnameComb1
                                        OR OFAC.name2    = @newFnameComb2
                                        OR OFAC.name2    = @newFnameComb3
                                        OR OFAC.name2    = @newFnameComb4
                                        OR OFAC.name2    = @newFnameComb5                            
                                        OR ofac.alias2    = @newFnameComb1
                                        OR OFAC.alias2    = @newFnameComb2
                                        OR OFAC.alias2    = @newFnameComb3
                                        OR OFAC.alias2    = @newFnameComb4
                                        OR OFAC.alias2    = @newFnameComb5    
                                        )
                )            
            OR ofac.membershipId=@membershipId
            )        
    )X

Following is the index:

CREATE NONCLUSTERED INDEX [NcIdx_contact_dob_idNumber_idPlaceIssue_name2_alias2_membershipId_ofackey] ON [dbo].[blacklist]
(
[ofacKey] ,
[contact] ,
[dob] ,
[idNumber] ,
[idPlaceIssue] ,
[name2] ,
[alias2] ,
[membershipId] 
)

Answer 7

One note, why do not  you have ORDER BY clause as your SELECT has TOP clause?

Can  you check if someone turned auto create stats off for a database?

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence

Answer 8

auto stats is on. there is no order by clause because that is not required in this result.

Answer 9

And last question , can you post SELECT @@VERSION output?

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence

Answer 10

Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64)   Oct 28 2016 18:17:30   Copyright (c) Microsoft Corporation  Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Standard 6.3 <X64> (Build 14393: ) (Hypervisor) 

Answer 11

Hi Curendra,

 

Have you rebuild index  or reorganize index ? We should update column statistics after index rebuild as well. Please use the following code to update column statistics if the issue  still exists.

 

Update STATISTICS [your table] WITH COLUMNS;

 

Best regards,

Dedmon Dai

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail]

Answer 12

Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64)   Oct 28 2016 18:17:30   Copyright (c) Microsoft Corporation  Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Standard 6.3 <X64> (Build 14393: ) (Hypervisor) 

You are a bit behind on updates. You should apply Service Pack 2, and preferrably also the latest CU, which is CU 10.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 13

SELECT @Result = COALESCE(@Result + ', ', '') +  ofacKey

Beware that the correct result of the construct above is undefined. That is, you may get what you expect, or you may get some thing else.

Use this instead:

SELECT @Result =   (SELECT ofacKey + ', '
   FROM ...
   FOR XML PATH(''), TYPE).value('.', 'nvarchar(MAX)')
SELECT @Result = substring(@Result, 1, len(@Result) - 1)

Yes, the syntax is anything but intuitive, but its results are guaranteed in difference to what you have.

On SQL 2017 you can use the more normal string_agg function, but it is not available on SQL 2016.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 14

Update STATISTICS [your table] WITH COLUMNS;

 

Hi Dedmon Dai,

Your suggestion worked fine when I ran the query in SSMS. The warning is not seen in execution plan.

But in the query_store, the warning is still there.

Answer 15

Please install SP2 on SQL Server 2016

https://support.microsoft.com/en-us/help/3177534/how-to-obtain-the-latest-service-pack-for-sql-server-2016

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence

Answer 16

Would you please try to apply the latest sp and cu?

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail]

Answer 17

I would update sql server and test but unfortunately I am not authorized.

Answer 18

I would update sql server and test but unfortunately I am not authorized.

When Dedmon says "you" he does not mean you personally, but the organisation you work for. That is, you should forward this recommendation to the team who is responsible for keeping the machines updated.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 19

Your suggestion worked fine when I ran the query in SSMS. The warning is not seen in execution plan.

But in the query_store, the warning is still there.

What do you mean the last passage? Obviously, the warning will not be removed from earlier data in Query Store. Or do you mean that you see plans saved in Query Store with this warning after you ran UPDATE STATISTICS?

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 20

I ran the update and I checked the query_store to see the execution plan for the instances of the query after the update. 

I have used recompile option. But I can see that warning even today. 

However there is no warning if I run the query in ad hoc.