Recently I was asked to investigate the update statistics process on a particular database on the SQL Sever 2019 box. It is taking longer and longer and interfering with other jobs that occur at a later stage.
I investigate what tables are getting updated and there are 3 really big table (and their audits, giving a total of 6) that are very big. There are then lots of other smaller tables (which don't really take long)
Basically I am going to isolate the 6 really big tables from the other smaller tables. The smaller tables weekly update statistics process runs fine (even "with fullscan") - it does not take long.
I want to develop a separate update statistics process for the big 6 tables with a schedule of perhaps late Friday so it can run over (or perhaps the Fridays of bank holiday weekends perhaps as it gives an extra day if the process takes a long time).
I have some facts & figures on them below. They all have "with fullscan" on them but 1 of them (the biggest) has a sample of 50% and also has some sort of pre-existing "alter index" command there...
I would like to do something more intelligent on these tables as regards the sampling rate and hence am looking for any advice that you may be able to offer. The tables are increasing in size over time and hence the sampling rate may have to be dynamically calculated. I would also like to record the times.
Tall order I know but would be most appreciative for any advice/guidance/suggestions/code samples that you may be able to offer. Please note I'm not a DBA just a developer but with a pretty decent level of SQL...
Many thanks for any assistance/guidance…
| TableName | TableSizeInKB | IndexSizeInKB | TableSizeIn+IndexSizeIn |
|---|---|---|---|
| TBL_A_Audit | 47905416 | 77448 | 47982864.000000GB |
| TBL_A | 20029224 | 86472 | 20115696.000000GB |
| TBL_B_Audit | 27379304 | 45760 | 27425063.999488GB |
| TBL_B | 1425880 | 1069848 | 2495728.000000GB |
| TBL_C_Audit | 7351992 | 12816 | 7364807.999488GB |
| TBL_C | 1601560 | 185424 | 1786983.999488GB |
Pre-existing “tbl_A_audit” commands
ALTER INDEX [PK_TBL_A_Audit] ON [dbo].[TBL_A_Audit] REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
UPDATE STATISTICS [dbo].[TBL_A_Audit]
WITH SAMPLE 50 PERCENT