This issue came up few weeks ago. I was asked to look into a time consuming batch job and the job was running for 5-6 hours every day and client wanted to reduce the execution time. This is what the job does every day
- Drop the indexes
- Load data into the tables
- Recreate the indexes
- Gather statistics
This looked fine to me at first. The stats procedure was running for a few hours and the client was getting worried on the execution time. Once I tuned the queries in loading procedure I decided to work on the stats gathering script.
Here is a copy of stats gathering script I got. I could see a lot of issues with the way it is written and executing.
Some of the issues are
- The procedure collects the stats of all the tables irrespective of whether it is really changed or not.
- It does the same with all indexes
- Here index stats are collected twice; once when the indexes are created and secondly by the procedure.
- Parallelism is not used.
Here is the modified script and know what it got completed a lot faster!!