5

We’ve been utilizing Ola Hallengren's maintenance scripts for our index optimization for years on our OLTP databases. (we are on sql server 2008 R2) 2 weeks ago though one of the DBA’s here has created a maintenance job to update statistics separately and this runs weekly before the Index optimization job. It seems that after this was created we are getting performance issues on a handful of stored procedures. I am not too familiar with stat maintenance, so I am wondering, is it really necessary to run a weekly job separately to update all statistics?
The DBA here seemed to think this was needed but I feel that we started having badly performing stored procedures the morning after the maintenance jobs are running and I constantly needed to recompile the same stored procedures at least 1 or 2 times per day for the following few days then the issue just stops. My theory is pointing to the statistics updates that occurred before but I am having trouble finding the reason why. Don't statistics get updated after a certain amount of changes or during an index rebuild anyway? Why would recalculating them separately cause an issue?

Would anyone be able to offer some clarity as to why this would happen to the same stored procedures every time statistics get updated?

This is the command being used for the statistics job:

EXECUTE dbo.IndexOptimize 
    @Databases = 'DB_PRO', 
    @FragmentationLow = NULL, 
    @FragmentationMedium = NULL, 
    @FragmentationHigh = NULL, 
    @UpdateStatistics = 'ALL', 
    @OnlyModifiedStatistics = 'Y' , 
    @TimeLimit = 3600, 
    @LogToTable = N'Y'; 

Then the Index Maintenance job runs with the following :

    sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE [dbo].[IndexOptimize] 
    @Databases = 'DB_PRO', @TimeLimit = 10800, @LogToTable = 'Y'" -b
  • 1
    You will need post the command you run for Ola's script and what is included maintenance plan in order to get a valid answer. Based on what you mentioned so far, there may be some redundant work being done. Read this: blogs.msdn.microsoft.com/psssql/2015/03/06/… – SqlWorldWide Aug 22 '17 at 17:15
  • Not related to your question directly, but you should run Stats Updates AFTER your Index Maintenance routines as Index Rebuild Operations update statistics automatically with a 100% sample rate. The Update Stats routine would only need to run against those stats that weren't automatically affected (but are still stale). – John Eisbrener Aug 22 '17 at 18:19
  • @TracyM your comment helps. Later on I will add another answer with explaining what 2 of the commands are doing and overlap between these 2. Not so easy to point which part (if at all) is causing performance issue. – SqlWorldWide Aug 22 '17 at 18:30
  • Have you tried removing the statistics update job and seeing if your expected performance profile returns on these handful of stored procedures? Does the performance on these procedures remain consistently poor, or does it ever get back to expected levels? – swasheck Aug 22 '17 at 18:39
  • Also, if you could post the execution plans of a "good" run and a "bad" run that'd be helpful. Here's a good location: brentozar.com/pastetheplan – swasheck Aug 22 '17 at 18:54
5

Question: I am not too familiar with stat maintenance, so I am wondering, is it really necessary to run a weekly job separately to update all statistics?

Yes, you need to maintain your statistics if you have large tables. Read here how Auto update statistics works:

Auto Update Statistics Option Statistics are checked before query compilation or before executing a cached query plan. Statistics are considered out-of-date when:

There was a data change on an empty table. The number of rows in the table was 500 or less at the time of statistics creation and the column modification counter of the leading column of the statistics object has changed by more than 500 since then.

The table had more than 500 rows when the statistics were gathered, and the column modification counter of the leading column of the statistics object has changed by more than 500 + 20% of the number of rows in the table when the statistics were gathered.

A table in TempDB with less than 6 rows had at least 6 row modifications.

Question: Don't statistics get updated after a certain amount of changes or during an index rebuild anyway?

Rebuilding an index, for example by using ALTER INDEX … REBUILD will also update index statistics with the equivalent of using WITH FULLSCAN unless the table is partitioned, in which case the statistics are only sampled* (applies to SQL Server 2012 and later). Rebuilding indexes does not update column statistics.

Reorganizing an index, for

example using ALTER INDEX … REORGANIZE does not update any statistics.

Question: Why would recalculating them separately cause an issue?

Might be because your sample rate is to low and the statistics is not accurate. Use FULLSCAN to see if that helps. It might also be that the Cardinality Estimation misinterprets the statistics (can happen in SQL Server 2014), if this is the case try trace flag 9481 to disable cardinal estimator in SQL Server 2014.

The StatisticsSample parameter of Ola Hallengrens script give you this:

Indicate, as a percentage, how much of a table is gathered when updating statistics. A value of 100 is equivalent to a full scan. If no value is specified, then SQL Server automatically computes the required sample.

  • Thank you for your answer. The DBA was on vacation and after I spoke to him is also not sure what is causing the issue. I did a good amount of of research on this subject. and decided the best thing (as others suggested) was to just disable the weekly stats update job and see if anything happens over the weekend. This Monday morning the Database was fine, no time outs or slowness over the weekend or this morning.We will re-evaluate this job and the samples being taken. – Tracy M Aug 28 '17 at 21:27
2

If the UPDATE STATISTICS command you are running manually is not sampling enough rows, it could be causing bad estimates, which could in turn cause poor performance until statistics are updated automatically. With the given information, it is not possible to know if this is the cause of the problem you are reporting--you'll need to provide much more information.

Turn on showplan and run a query before the stats get updated. Check the actual vs. estimated rows returned. Then update the stats, and run the query again. If actual vs. estimated rows is further off than in the previous test, then it is quite likely your manual stats update is mucking things up a bit.

1

First let me explain what both scripts are doing. I do not use Ola's solution. My answer is based on the documentation on his site.

I took both commands from your comment.

Command you were running which you called "maintenance scripts for our index optimization" was actually not doing any index optimization.

EXECUTE dbo.IndexOptimize 
@Databases = 'DB_PRO', 
@FragmentationLow = NULL, 
@FragmentationMedium = NULL, 
@FragmentationHigh = NULL, 
@UpdateStatistics = 'ALL', 
@OnlyModifiedStatistics = 'Y' , 
@TimeLimit = 3600, 
@LogToTable = N'Y'; 

When you pass NULL for @FragmentationLow, Medium or High it means Do not perform index maintenance.

Based on three other parameter values you passed it will Update statistics only if any rows have been modified since the most recent statistics update. It will use a sample number of rows to update stats.

Lets look at the the other command that your DBA added.

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d 
master -Q "EXECUTE [dbo].[IndexOptimize] 
@Databases = 'DB_PRO', @TimeLimit = 10800, @LogToTable = 'Y'" -b

If I understood correctly this will Reorganize index. Rebuild index online if reorganizing is not supported on an index. Rebuild index offline if reorganizing and online rebuilding are not supported on an index. This is the default for a medium-fragmented index.for any index fragmented between 5% and 30%.

Also Rebuild index online. Rebuild index offline if online rebuilding is not supported on an index. This is the default for a high-fragmented index. for indexes fragmented over 30%.

This command will not explicitly update any statistics. But some index statistics will be updated during rebuild operation as described here.

I do not see any harm being done by the sequence of these two scripts. 2nd script will overwrite sample statistics with fullscan (index stats only) if it does a rebuild.

You mentioned the stored procedure were running fine before DBA added the maintenance job, meaning stored procedure was running fine with update statistics using sample.

Theoretically DBA's step should not contribute anything that will make your store procedures run slower. But as other's mentioned, only way to find out is to compare actual execution plan for both run and see the difference.

I also suggest you talk to the DBA, read Ola's documentation and merge these two commands into one.

  • Appreciate your answer, and that does make sense, but the way the scripts are running is the: EXECUTE dbo.IndexOptimize @Databases = 'DB_PRO', @FragmentationLow = NULL, @FragmentationMedium = NULL, @FragmentationHigh = NULL, @UpdateStatistics = 'ALL', @OnlyModifiedStatistics = 'Y' , @TimeLimit = 3600, @LogToTable = N'Y'; Runs first (this is the statistics update) Then the other command runs afterwards to update indexes, so I thought it would be in the correct order. – Tracy M Aug 23 '17 at 13:20
  • Ok I assumed it other way. I will modify my answer later today. This order is better than the reverse. Because that way sample update will be replaced by fullscan. Hope you can get to the bottom by looking at the plan. – SqlWorldWide Aug 23 '17 at 14:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.