Index statistic is empty - how is this possible?
-
I have a simple index on a table - just one column in the index plus one included column. There is an existing statistic with the same name (so it was created by SQL when the index was created). The statistic has no histogram, no density vector, no updated time or sampled date, no mod counter - basically DBCC SHOW_STATISTICS returns nothing at all.
The index is being used by queries and I can also force it to be used using an index hint. It is not disabled. It is not filtered. It is not a columnstore index. The index is not set for norecompute. The database does has auto update stats enabled. If I look at sys.columns, sys.stats, and dm_db_stats_properties for this static, I get this:
If I manually update the statistic, it will update and the histogram and other data will become populated. Likewise if I rebuild the index.
The problem is I normally update stats using Olla's script with OnlyModifiedStatistics=Y. Because the modification counter is null, Olla's script never updates this statistic.
Over the weekend, our queries stopped using this index and started doing a table scan, which led me to believe we reached a tipping point where this table reached a certain size and SQL changed the query plan. This is what led me to looking into statistics and how I found this situation.
I don't understand how the statistics information can not exist. I am on SQL 2016 SP2-CU1. The database is in SQL 2016 compatibility mode.
Does anyone have any ideas?
-
The only way that stats won't exist is if there is no data in the table when the index gets created AND you have turned off auto-update stats AND you don't have a manual process running to update the stats in some manner. If there is data there, you should see stats when you create an index. If there is no data and then it gets added, it goes through the normal stats update process. Stats updates will only occur after a query calls, so you may have loaded data into a table, but the stats won't actually update until the query runs. However, if you've run queries, you should see the stats get updated if the data has been modified.
I'm typing this sitting at a table with Kathi Kellenburger and Kendra Little. None of us can come up with any other scenario that leads to this. It's possible there's a bug, but not one any of us are aware of.
Kendra is doing some more searches.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Thanks for the research. There is definitely data in the table - over 9 million rows and they've been there for months, if not years. After posting this, I found an earlier backup where the stats were present. So I have a backup from where the stats were there then, 24 hours later, a backup from when they are gone. I have query store enabled and I'm able to see where there is a query plan that uses the index (and stats) and then when it stops using it (presumably when they went away). Query store shows a last used time and first used time for the plans so, based on that, I've narrowed it down to about a 2 second window when I think they went away somehow. I haven't been able to find anything that happened during that 2 second window that might cause this.
And, as I said, I've checked the usual suspects - auto-update stats is on, etc.
-
Man, I'm sorry. I've got nothing. You might be looking at some kind of bug.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Bummer. Thanks to all three of you for thinking about this! If you all are stumped, I don't feel bad about not having a clue. Well, I kinda do, but at least I'm in good company 🙂
-
Just to follow up on this. I found out how this could happen. The BOL entry for UPDATE STATISTICS says:
We recommend against specifying 0 PERCENT or 0 ROWS. When 0 PERCENT or ROWS is specified, the statistics object is updated but does not contain statistics data.
I tested this and, yes, if you update a stat with 0 percent sampling, the stat exists, but the density vector and histogram are deleted.
-
I realize this thread is two years old....
I too am on Sql 2016 in Compatibility mode 2012. In the past four months we have had three occurrences of this problem.
We are truncating a table and repopulating it. +-800k rows via SSIS package. This is a daily process, so the issue is random. The empty statistic shows itself by pegging CPU at 100% with "common" queries. Query plan has warning of columns without statistics.
I used update statistics <table>.<statistics> with sample 0 rows , on a "good" DB to prove that this empty stat was the cause of the performance problem.
During the truncate and reload, there are potentially queries running aging table - at least trying to.
Not knowing enough about how SQL handles statistics updates, Is it possible that the truncate , being meta-data operation, causes the Statistic pages to be "soft deleted" . Second query is allowed to run, causing bad plan as stat sees no data. table loads but stats believes its already up to date. i.e. some sort of internal serialization change? or an issue with new query optimizer in compatibility mode.
-
You may have kind of outlined the issue. You truncated the data, which will empty the statistics automatically. The SELECT query runs after the truncate, but before the data load fires an automatic statistics update. You get zero row estimates or empty stats. After the data loads, stats don't update until a query fires to cause a recompile. This won't help existing queries that are already running.
Or, maybe you have auto update statistics disabled and you don't get an update during the data load.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
BTW, you'll get a lot more attention by posting your stuff to a new question. The only people likely to respond to this are those of us who are following this question.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Thanks Grant
I have created new thread with some more info:
https://www.sqlservercentral.com/forums/topic/empty-index-statistics-object
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply