Have you ever encountered the dreaded “Could not locate statistics” error in SQL Server? You’re not alone. Recently, I tackled this exact issue with a stored procedure that left me scratching my head. Let me walk you through how I solved it.

Understanding the Error

When SQL Server can’t find statistics it needs for query optimization, it throws an error like this:

Could not locate statistics '_WA_Sys_0000000D_446B1014' 
in the system catalogs. 
[SQLSTATE 42000] (Error 2767)  DBCC execution completed. 
If DBCC printed error messages, contact your system administrator.
[SQLSTATE 01000] (Error 2528)  DBCC execution completed. 
If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Error 2528)....

The error indicates that SQL Server tried to use or update statistics named ‘_WA_Sys_0000000D_446B1014’ (which is an auto-generated system statistics name) but couldn’t find it. This typically happens when statistics become corrupted or orphaned, preventing SQL Server from creating efficient query plans. The ‘0000000D’ in the statistics name suggests it’s for the 13th column in the table (counting in hex).

The Stored Procedure

Based on the error message and knowing it’s related to statistics on the [dbo].[TABLE] table, this error likely occurred during the UPDATE operation near the end of the procedure.

• The UPDATE operation looked something like this:

UPDATE A
SET A.[ContactID] = B.[ContactID],
...
FROM DATABASE.dbo.TABLE AS A 
JOIN #TEMPB AS B
    ON A.Contact_ID = B.Contact_ID
    AND B.[StartDateTime] >= @LookbackDate

This UPDATE is more likely the culprit because:

1. The query optimizer needs statistics on ContactID to help determine the best execution plan.
2. There’s a complex chain of joins earlier that populated #TEMPB table involving ContactID lookups.

The optimizer would try to use these statistics to determine the most efficient way to perform the UPDATE, and failing to find them would trigger this error. My assumption is the error happened while trying to build the execution plan for this operation rather than during actual data modification.

The fact that it’s looking for statistics specifically on ContactID makes sense given how central this column is to the data consolidation process in this stored procedure.

The Troubleshooting Journey

Step 1: The Simple Fix Attempt

My first instinct was to update the specific statistics:

UPDATE STATISTICS [dbo].[TABLE] ([_WA_Sys_0000000D_446B1014]) WITH FULLSCAN

Step 2: The Broader Approach

I tried updating all statistics on the table:

UPDATE STATISTICS [dbo].[TABLE] WITH FULLSCAN

Still no luck. When this didn’t work, I knew we had a deeper issue. Time to dig deeper!

Step 3: Database Integrity Check

To rule out corruption, I ran:

DBCC CHECKTABLE ('dbo.TABLE') 
WITH PHYSICAL_ONLY, ALL_ERRORMSGS, NO_INFOMSGS;

No corruption! Thank goodness.

I also checked if the statistics existed using this script and it was there.

SELECT OBJECT_NAME(object_id) AS TableName, name AS StatisticName
FROM sys.stats
WHERE name = '_WA_Sys_0000000D_446B1014';

Step 4: The Nuclear Option

After confirming no corruption, I took the final step:

1. Dropped the problematic statistics
2. Recreated them fresh:

DROP STATISTICS dbo.TABLE.[_WA_Sys_0000000D_446B1014];

CREATE STATISTICS [_WA_Sys_0000000D_446B1014]
ON dbo.TABLE (ContactID);

Success! The stored procedure resumed normal operation.

Root Cause and Prevention

The _WA_Sys_* statistics are automatically created by SQL Server for query optimization. Metadata corruption or improper operations (e.g., interrupted updates or index operations) can lead to such errors.

To prevent similar issues:

1. Regularly run DBCC CHECKDB to identify and resolve database-wide corruption

DBCC CHECKDB (‘DatabaseName’) WITH NO_INFOMSGS, ALL_ERRORMSGS;

2. Include regular maintenance tasks like updating statistics, rebuilding indexes, and taking backups.
3. Monitor and address errors or warnings in the SQL Server logs promptly.

Best Practices and Lessons Learned

• Always start with the least invasive solution
• Document each troubleshooting step
• Verify database integrity before making major changes
• Keep track of which column the statistics belong to (in this case, ContactID)
• Consider regular statistics maintenance to prevent similar issues

Conclusion

While missing statistics can be frustrating, having a systematic approach to troubleshooting makes all the difference. Remember to back up your database before making changes, and always test in a non-production environment first when possible.

Next time you encounter this error, you’ll have a proven roadmap to resolution. Happy troubleshooting!