Statistics gathering issue

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

  1. Drop the indexes
  2. Load data into the tables
  3. Recreate the indexes
  4. 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.

stats_before

Some of the issues are

  1. The procedure collects the stats of all the tables irrespective of whether it is really changed or not.
  2. It does the same with all indexes
  3. Here index stats are collected twice; once when the indexes are created and secondly by the procedure.
  4. Parallelism is not used.

Here is the modified script and know what it got completed a lot faster!!

stats_after

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s