Answered by:
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.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 AMAnswerer -
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 AMAnswerer -
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 PMAnswerer -
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
)
)XFollowing 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 AMAnswerer -
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 AMAnswerer -
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
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 AMAnswerer -
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.comThursday, 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.
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?But in the query_store, the warning is still there.
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