none
Warning of No statistics on a column RRS feed

  • Question

  • For a query execution plan is showing warning of no statistics on a column but the statistics for that column exists.
    Tuesday, October 22, 2019 9:33 AM

Answers

  • 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 MSDNFSF@microsoft.com

    • Marked as answer by Curendra Thursday, October 24, 2019 5:43 AM
    Wednesday, October 23, 2019 8:37 AM

All replies

  • 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

    Tuesday, October 22, 2019 9:51 AM
    Answerer
  • I created one statistics including all the columns appeared in the preceding filter operation but the warning is still there.
    Tuesday, October 22, 2019 11:05 AM
  • 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

    Tuesday, October 22, 2019 11:52 AM
    Answerer
  • I cant say whether the plan is good, however it looks ok to me. the warning is seen.

    Tuesday, October 22, 2019 12:07 PM
  • 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

    Tuesday, October 22, 2019 12:51 PM
    Answerer
  • 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] 
    )

    • Edited by Curendra Wednesday, October 23, 2019 4:51 AM
    Wednesday, October 23, 2019 4:50 AM
  • 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

    Wednesday, October 23, 2019 5:11 AM
    Answerer
  • auto stats is on. there is no order by clause because that is not required in this result.
    Wednesday, October 23, 2019 5:15 AM
  • 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

    Wednesday, October 23, 2019 5:17 AM
    Answerer
  • 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) 
    Wednesday, October 23, 2019 5:58 AM
  • 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 MSDNFSF@microsoft.com

    • Marked as answer by Curendra Thursday, October 24, 2019 5:43 AM
    Wednesday, October 23, 2019 8:37 AM
  • 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, esquel@sommarskog.se

    Wednesday, October 23, 2019 9:27 PM
  • 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, esquel@sommarskog.se

    Wednesday, October 23, 2019 9:32 PM

  • 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.

    Thursday, October 24, 2019 3:33 AM
  • 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

    Thursday, October 24, 2019 4:16 AM
    Answerer
  • 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 MSDNFSF@microsoft.com

    Thursday, October 24, 2019 5:35 AM
  • I would update sql server and test but unfortunately I am not authorized.
    Thursday, October 24, 2019 6:26 AM
  • 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, esquel@sommarskog.se

    Thursday, October 24, 2019 10:59 AM
  • 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, esquel@sommarskog.se

    Thursday, October 24, 2019 11:01 AM
  • 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.


    • Edited by Curendra Friday, October 25, 2019 4:04 AM
    Friday, October 25, 2019 4:03 AM