UPDATE STATISTICS

[BobLoblaws]

What is a good rule of thumb for how often to run the UPDATE STATISTICS command on a table?

Thanks,

 

[bperry]

A default installation of SQL Server will automatically keep index statistics reasonably up-to-date. Beyond that, you can do UPDATE STATISTICS yourself if there has been a big change in the undelying data. ie after a large number of rows have been deleted or added, or if the index values of a column for a large number of rows has changed.

Here's BOL:
-------------
SQL Server keeps statistics about the distribution of the key values in each index and uses these statistics to determine which index(es) to use in query processing. Users can create statistics on nonindexed columns by using the CREATE STATISTICS statement. Query optimization depends on the accuracy of the distribution steps:

If there is significant change in the key values in the index, rerun UPDATE STATISTICS on that index.

If a large amount of data in an indexed column has been added, changed, or removed (that is, if the distribution of key values has changed), or the table has been truncated using the TRUNCATE TABLE statement and then repopulated, use UPDATE STATISTICS.

To see when the statistics were last updated, use the STATS_DATE function.

 

[BobLoblaws]

What is considered a 'large' amount of data?

Right now, approx 18,000 records are added per table each day. The moron who designed all the databases did a horrible job, and performance is almost non-existent.

I am trying to find ways to improve the performance without redesigning the entire system.

Thanks,

 

[bperry]

'Large' depends on the %, no doubt.
18K out of 100,000 quite different than 10,000,000

I believe there are ways to exanine the statistics, to try and assess the status of them. (The STATS_DATE function would be one easy thing to consider.)

However, the short cut would be (as I'm sure you've tried):

- evaluate performance,
- load 18K
- re-evaluate performance .....any change?
- Update Stats
- re-evaluate performance .....any change?

That should tell you whether the stats have any bearing on the performance issues, or whether you'll have to dig deeper. There are so maaaanny issues: database design, query design, table sizes, number and type of indexes, competing applications (if any), server hardware, network traffic.......