sql-serverstatisticsdatabase-tuning

SQL Server : How to detect appropriate timing to update table/index statistics

May I ask is there any way to get to know appropriate timing to update table/index statistics?

Recently performance is getting worse with one of major data mart table in our BI-DWH, SQL Server 2012. All indexes are cared every weekend to reorganize/rebuild, according to their fragmentation percentage and now they're under 5% as avg_fragmentation_in_percent.

So we detect that's caused by obsolete table/index statistics or table fragmentations or so.

Generally, we set autostats on and that Table/index stats were updated at July 2018, maybe still it's not time to update according to their optimizer, since that table is huge, total record is around 0.7 billions, daily increase about 0.5 million records.

Here is PK statistics and actual record count of that table.

-- statistics

dbcc show_statistics("DM1","PK_DM1")

Name    Updated Rows        Rows            Sampled     Steps   Density     AveragekeylengthString      Index   Filter Expression   Unfiltered Rows
------------------------------------------------------------------------------------------------------------------------------------------------------
PK_DM1  07 6 2018  2:54PM   661696443       1137887     101         0                       28          NO          NULL                661696443

-- actual row count

select count(*) row_cnt from DM1;

row_cnt
-------------
706723646

-- Current Index Fragmmentations

SELECT a.index_id, name, avg_fragmentation_in_percent  
FROM sys.dm_db_index_physical_stats (DB_ID(N'DM1'), 
      OBJECT_ID(N'dbo.DM1'), NULL, NULL, NULL) AS a  
    JOIN sys.indexes AS b 
      ON a.object_id = b.object_id AND a.index_id = b.index_id;   
GO  

index_id    name    avg_fragmentation_in_percent
--------------------------------------------------
1        PK_DM1             1.32592173128252
7        IDX_DM1_01         1.06209021193359
9        IDX_DM1_02         0.450888386865285
10       IDX_DM1_03         4.78448190118396

So there is less than 10%, but over 45 millions difference between the statistics row counts and actual record counts. I'm wondering if it can be worth to update the table/index stats manually in this case.

If there are any other information you decided the appropriate timing to update the stats, any advice would be so much appreciated.

Thank you.

-- Result

Thanks to @scsimon advice, I checked all index statistics in detail and main index was missing RANGE_HI_KEY -- that index based on registration date and there was no range after July 2018 last updated statistics. (The claim was made by user when he searched for 2018 September records)

So I decided to update table/indexes statistics and confirmed the same query was improved from 1 hour 45 mins to 3.5 mins.

Deelpy appreciated all of the advices to my question.

Best Regards.

Solution

  • Well you have auto-update statistics to on so that's good. Also, each time an index is rebuilt, the statistics are recomputed. SQL Server 2008R2 onward, until 2016, has the same behavior as TF 2371 meaning the large table takes fewer rows to need changing to auto compute. Read more here on that.

    Also you are showing stats for a single index not the whole table. That index could be filtered. And, remember that the Total number of rows sampled for statistics calculations. If Rows Sampled < Rows, the displayed histogram and density results are estimates based on the sampled rows. You can read more on that here

    Back to the core problem of performance... you are focusing on statistics and the indexes which isn't a terrible idea, but it's not necessarily the root cause. You need to identify what query is running slow. Then, get help with that slow query but following the steps in that blog, and others. The big one here is to ask a question about that query with the execution plan. The problem could be the indexes, or it could be: