UpdateStatistics
-
Hi,
We are using SQL Server 2016 SP2 CU1 EE. Using Ola Hallengren's IndexOptimize job for Index maintenance and for UpdateStatistics
EXECUTE dbo.IndexOptimize @Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@UpdateStatistics = 'ALL',
@SortInTempdb = 'Y',
@LogToTable = 'Y'Issue observed:
We have couple of stored procedures running slow and after troubleshooting found out that stats were stale. The table with stale stats has 8.9 million records.
Before updating with fullscan: Procedure was taking 20 sec.
After fullscan, procedure is completing in 1 sec.
But after daily IndexOptimize job, the stats went back to normal, i.e. same rows as before fullscan and procedure taking 20 sec again.
Can you please advise what will be the best approach to have latest stats here?
Thanks
- This topic was modified 4 years, 7 months ago by gana79950.
-
This doesn't appear to be a problem with the utility - rather, the problem appears to be auto-updated statistics. When you see the rows_sampled column as a small percentage of the total rows, that indicates an auto update stats process - and it utilized the default sampling rate.
When an index is rebuilt - the statistics are updated with a full scan. When an index is reorganized - the statistics are not updated. The utility should be set to update the statistics any time it decides to perform a reorganize and that should be configured to perform a full update.
One way to avoid this issue is to modify the statistic and for NO_RECOMPUTE. This then requires you to manually update the statistics with a full scan on a set schedule or they will quickly become stale.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
Is there any known bug with SQL 2016 SP2 CU1 with Auto-Update statistics?
Here are the database settings we have:
-
Hi Jeffrey,
Right after running IndexOptimise, I'm seeing that the rows_sampled column going back to a small percentage of the total rows.
Any thoughts?
-
I don't use this tool - so I cannot say whether or not it is using a sampling rate to update statistics. If the index is rebuilt - statistics will be updated with a full scan automatically - if the index is reorganized - statistics will NOT be updated.
With that said - the utility should identify when an index is being rebuilt or reorganized, and when reorganized it should also perform an update on the statistics. If that is not happening during the utility - then SQL Server is determining that the statistics need to be updated and is using the default sampling rate.
You need to determine if the utility is updating statistics with a sampling rate - or if SQL Server is doing that. Either way - the problem you are having is that the sampling rate isn't good enough, which is causing a poor execution plan to be generated.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs - gana79950 wrote:
Hi Jeffrey,
Right after running IndexOptimise, I'm seeing that the rows_sampled column going back to a small percentage of the total rows.
Any thoughts?
Yes... carefully read the documentation that Ola provides at https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html
and look for the "StatisticsSample" option.
You should also take a gander at the other options for "statistics".
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
p.s. Stop doing REORGANIZE on tables that don't have LOBs or tables that do have them but they don't need to be compacted. You're just blowing out your log file worse than any REBUILD would even if you're in the FULL recovery model. REORGANIZE can (and has done to me) and frequently only compacts indexes to the fill factor and cannot make any extra space above the fill factor. That can cause a shedload of page splits that should not happen. Those are also tough on the log.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Auto Update Statistics option of SQL triggers the update of statistics only after certain conditions are met. Hypothetically when around 10-20% of the total rows are added/modified in a table.
So the best approach could be to plan the Update Statistics periodically as part of the Database Maintenance activity.
-
The issue is only happening to Large tables with over few mil records.
I'm thinking to use PERSIST_SAMPLE_PERCENT or a separate job with fullscan for the tables in question for now.
Please let me know if any better ideas we can think of.
- gana79950 wrote:
The issue is only happening to Large tables with over few mil records.
I'm thinking to use PERSIST_SAMPLE_PERCENT or a separate job with fullscan for the tables in question for now.
Please let me know if any better ideas we can think of.
If this was my system - I would exclude these tables/indexes from the utility and manage them separately. I would build a separate process to rebuild the indexes on a set schedule (daily/weekly/monthly) and build a separate process to update statistics on a schedule. I would also set the statistics to NO_RECOMPUTE so they would never auto update...this way my process would update the stats as needed.
What you need to figure out is how much data modification is being done and how long that takes to affect the stats, and when that starts to become a problem for your queries. Ideally - you should be able to update stats no more than once a night but it may require more often if there is a lot of rows being modified.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply