2

We have a table with a 1 column Primary Key on and an UNIQUE Non-Clustered Index with 16 columns.

This table has more than 20695780 Rows and on every day new records inserted between 30000 and 35000.

After insertion process, we have many other processes those refer this table.

On every day, before proceeding further, after insert we do an Update Statistics with 30 Percent Sample on this table.

After this when we do rest processes then we are not facing problem and everything goes smoothly.

Now the problem is if we do Update Statistics then it is taking more than 15 Minutes.

We need a solution: - To make this "Update Statistics" faster OR Can we perform a parallel Update Stats on this table with other processes OR Instead of "Update Statistics" can we go for REBUILD INDEXES (need to know it will update all Statistics or not)? OR Any other solution by which we can save this 15 minutes.

Please advice.

Improve this question
1
  • 1
    What edition of SQL Server - Standard, Enterprise..? Rebuild indexes also updates their related stats, but not all column stats. Do you realize that updating stats is actually a pretty expensive operation and replacing it with a more expensive operation (rebuild indexes) doesn't really have many chances of making it faster? I'd give Ola Hallengren's maintenance scripts at least a try.
    – Marian
    Commented Dec 17, 2013 at 22:48

1 Answer 1

Reset to default
1

I would start by seeing if you have any statistics that aren't being used. Unfortunately that isn't possible directly but you could start by looking at your indexes and seeing if any of them are worth getting rid of. If you can eliminate some of your indexes and/or statistics that will improve your run time for the UPDATE STATISTICS. You can find out which indexes are actually in use by looking at sys.dm_db_index_usage_stats.

Here are a couple of links I used for reference although you can do a quick google search and find dozens.

Also as a general suggestion if you partition your table using a "partitioned view" so you have "old data" and "new data" then you can schedule your statistcs updates separetly. For example let's say you partition your data by year. You run the daily UPDATE STATISTICS on just the current years partition. If there are no updates on previous years data then you don't have to worry about statistics updates on those partitions at all. If there are updates on previous years data you can either

  1. Let them update themselves using the auto update functionality or
  2. Update them say once a week
  3. And/or update them during an off hour time.
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.