1

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.

Sachiko
  • 808
  • 1
  • 12
  • 31
  • How do you know that your performance issues are due to stats issues? – MJH Oct 09 '18 at 13:04
  • 1
    I'd say you need a good reason to deviate from recommended guidelines: https://learn.microsoft.com/en-us/sql/relational-databases/indexes/reorganize-and-rebuild-indexes?view=sql-server-2017 – user1443098 Oct 09 '18 at 13:06
  • Thanks for your reference, @user1443098. But I think index fragmentation seems low. If there is misunderstanding, please let me know. – Sachiko Oct 09 '18 at 13:15
  • IIUC, you asked when to update statistics and indexes. I'd set autostats on and reorg/rebuild indexes based on the guidelines except in special circumstances. And, I've seen a few of those! – user1443098 Oct 09 '18 at 13:22
  • @user1443098 thanks again :) – Sachiko Oct 09 '18 at 13:40

1 Answers1

1

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:

  • Memory contention / misallocation
  • CPU bottleneck
  • Parallelism (maybe your have MAXDOP set to 0)
  • Slow disks
  • Little memory, causing physical reads
  • The execution plan isn't optimal anymore and perhaps, you need to recompile that query
  • etc, etc etc... this is where the execution plan and wait stats will shed light
S3S
  • 24,809
  • 5
  • 26
  • 45
  • 1
    Many thank for your kind and clear advice, @scsimon. As you kindly mentioned, performance issue can be caused by many factors, not only simple statistics and index -- actually memory contention, slow disks and low disk freespace can be other causes at our sysytem. I'll check our data mart tables more deeply according to 'Inside the Statistics Histogram & Density Vector', you kindly introduced, it's so interesting :) Thank you so much. – Sachiko Oct 09 '18 at 13:39
  • 1
    after refereing your documents and some index missing 'RANGE_HI_KEY' which key is based on registration date. So I decided to update the statistics and confirmed the performance was improved so much. Thank you so much for all of your useful infromation :) (I'll update the result on my thread just for your information) – Sachiko Oct 09 '18 at 14:19
  • 1
    No worries at all @Sachiko – S3S Oct 09 '18 at 14:24