Recently, I got a question from a client where they had a SQL Server Agent job which was performing update statistics with full scan. The job was running successfully and had been running since long time. Since past few they started noticing that this job is taking longer time, than what it used to take before. Run time of the job doubled in last few runs and it continued taking longer time to finish.
I have verified and found that this job was created from a maintenance plan. Maintenance plan has update statistics task which is basically performing statistics update of all the objects(all table statistics, all column Statistics, and all index statistics) with FULL SCAN.
Some of the reasons why there is an increase in the duration of this job could be
– Number of databases has increased on the SQL Server instance. As this job covers all databases, so increase in databases could increase the durations of this job.
– Increase in the size of the Data in few or all of the databases. It is common that databases grow over a period of time and this will lead to increase in time taken for update statistics to complete, thus increasing the duration of the job run.
– Changes in the CPU usage, Memory usage or IO(Reads/Writes) usage patterns on few or all databases which can increase the time taken to complete the job.
– Changes in the access Patterns of the data in few or all databases can show affect which may cause locking/blocking with the update statistics jobs.
– Any other jobs are running at the same time and now Update statistics and new jobs have to share the available resources on the system, thus can lead to increase in job duration.
To be able to understand or find which of these above mentioned is the cause of the increase in the run time of the job, we need to have historical data when the job was running fast. The historical data which we need would include performance monitor counters related CPU, Memory, Disk and Network. SQL trace files to understand what other queries are running while this job is running. Wait statistics, Locking and Blocking details to see if there are any waits for Update Statistics job. It is most often are not available unless there is some third party monitoring tool is installed or custom monitoring is configured.
Also, we can look into other options to see how we can improve the speed of Update Statistics job by making changes to it. As we know that this job is using maintenance plan Update Statistics task and performing with FULL SCAN, we can look into possibilities of changing from maintenance plan in to a customized solution, where we update statistics only if there are any changes to the rows data in a table or column. Also, if the number of changes are small, we may perform the update statistics with Sampling as well. There are already some automated tools/Scripts available to achieve this, one of which is a popular solution Ola Hallengren’s Index and Statistics Maintenance Script. Test this solution on a test server and see the results and then implement the same on the production, if you are satisfied with the results on the test server.
I have migrated the update statistics job from maintenance plan solution to Ola Hallengren’s Statistics Maintenance Script and noticed that the run time of the job was reduced by 70%.
Same issue can also happen with other maintenance jobs like rebuild indexes or for any application related jobs as well and the cause of slowness could be due to one of the above mentioned causes. Similar troubleshooting approach should help in identifying the cause and resolving the issue by fixing the identified problem or by improving the maintenance job itself.
Hope this was helpful.
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.