March 8, 2021 at 6:28 pm
This is a new thread but relates directly to
https://qa.sqlservercentral.com/forums/topic/index-statistic-is-empty-how-is-this-possible
My post in that thread and for background
"
I too am on SQL2016 CU15 , db 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 against the 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."
I sat down to try and replicate this issue.
Steps:
2. Create indexes
3. check index and stats properties - no surprise stats are blank - sql know table has just been created
4. load data 800k+ rows
5. check index and stats properties - stats are EMPTY
6. Run Query that uses table and specific index
7. check index and stats properties - stats are EMPTY
8. update statistics on index_001
9. check index and stats properties - stats for index_001 populated
Auto update stats = true
Another oddity, Looking at the query plan, it uses the correct index and estimated number of rows = 1800+- and actual = 9 where is it getting these numbers from?
If I truncate, drop indexes (except clustered), insert data, create indexes, stats get populated(except clustered). If I then run test as first explained above, Stats do NOT appear to get updated, but the modification_counter is exactly equal to twice as many rows as inserted.
Any wise people out there have any ideas?
March 9, 2021 at 7:00 am
how to delete, or drop, statistics from tables and views in SQL Server Statistics on indexes cannot be dropped by using DROP STATISTICS. Right-click the statistics object that you want to delete and select Delete.
March 9, 2021 at 1:39 pm
To tell you where the row estimates are coming from, I need to see the execution plan, query and statistics. However, in general, the row counts come from the statistics. Depending on the query, that can be row counts from the histogram, or, the density graph. Here's a video from Erin Stellato explaining statistics. She's an amazing resource.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
March 10, 2021 at 5:31 pm
I did some more testing and investigations. turns out the queries running against target table is using "with(nolock)" table hints. This, in my understanding, from the testing is what's causing the problem. the queries are doing dirty reads, resulting (in my testing) with completely incorrect stats due to too low sampling. I am making an assumption that the occurrence of completely blank stats, is a special case of the query running after truncate, during insert but before first row is written to memory / buffer
April 1, 2022 at 7:12 am
This was removed by the editor as SPAM
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply