Could an update on all statistics separately cause bad performance?

Question

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

Answer 1

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.

Answer 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.

Answer 3

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.