Today, we worked on a service request that our customer reported a high latency running a single query. Following, I would like to share with you the lessons learned on that.
The first thing that we saw is our customer is running the following query.
SELECT count_big(*)
FROM [dbo].[table1]
WHERE Column1='7E8CDB83-36F6-41F1-86E5-FDE8EF121C5F'
Running this query we saw that this query took 400 seconds to complete. Why?
<OptimizerStatsUsage>
<StatisticsInfo Database="[f9c45481-159f-46f4-935f-3e2803bec642]" Schema="[dbo]" Table="[table1]" Statistics="[table1_index_partition]" ModificationCount="0" SamplingPercent="0.178939" LastUpdate="2023-02-23T13:41:57.88" />
<StatisticsInfo Database="[f9c45481-159f-46f4-935f-3e2803bec642]" Schema="[dbo]" Table="[table1]" Statistics="[table1_column2]" ModificationCount="0" SamplingPercent="0.176927" LastUpdate="2023-02-23T13:41:43.08" />
<StatisticsInfo Database="[f9c45481-159f-46f4-935f-3e2803bec642]" Schema="[dbo]" Table="[table1]" Statistics="[table_column1]" ModificationCount="0" SamplingPercent="0.176927" LastUpdate="2023-02-23T13:41:50.18" />
</OptimizerStatsUsage>
Checking the number of rows, plus the number of the statistics/indexes, configuration of the sample statistics, maxdop, SLO, buffer, Cardinality Estimation (SQL Server) - SQL Server | Microsoft Learn, etc.. of the database, we found that the almost time spent was updating the statistics. Also, we could seee other wait stats reported here sys.dm_os_wait_stats (Transact-SQL) - SQL Server | Microsoft Learn like PAGEIOLATCH, WAIT_ON_SYNC_STATISTICS_REFRESH, etc.
We have several options to mitigate, use NORECOMPUTE in the statistics if we have our own maintenance plan, if you are using partition use incremental statistics option, enable the auto update statistics asyncronous, etc.. but, all depends the cardinality estimation, windows maintenance for updating statistics, sample to use, etc..
Also, I would like to share that when Auto Update Statistics database option is enabled, SQL Server checks if the statistics are outdated every time if compiles or executes a query and updates them if needed. The auto update statistics database option is also enabled by default.
How SQL Server knows if the statistics are outdated based on the number of changes performed by INSERT, UPDATE, DELETE and MERGE commands, because SQL Server counts how many times the statistics columns were changed.
We have different scenarios where SQL Server will update the statistics, you could see more information here Statistics - SQL Server | Microsoft Learn
Also, I would like to mention that using Partition, SQL Server does not maintain individual statistics at the partition level. There is always the 200 groups of the statistics histogram, regardless if the data is partitioned or not.
SQL Server 2014 and 2016 we have a new feature called incremental statistics, which allows us to create per-partitions statistics. When you enable it, SQL Server starts to track the number of statistics columns at the partition level and marks statistics as outdates. Subsequent statistics updates would refresh statistics on the individual partition rather the entire table. This behaviour needs to be enabled with statistics_incemental index and incremental statistics options respectively.
Enjoy!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.