How often do you update statistics in SQL Server 2000?

Question

I am wondering if you helped update the statistics earlier and how did you find out to update them?

Answer 1

exec sp_updatestats 

Yes, updating statistics can be very useful if you find that your queries are not working as well as they should. This is evidenced by checking the query plan and notifying when, instead of searching for the index, for example, a table scan or index scan is performed. All of this assumes that you have configured your indexes correctly.

There is also the UPDATE STATISTICS command, but I personally have never used it.

Answer 2

Typically, a statistics update is added to the maintenance plan (as in the maintenance plan defined by Enterprise Manager). Thus, this happens on a schedule - daily, weekly, independently.

SQL Server 2000 uses statistics to make the right decisions about query execution, so they definitely help.

It is recommended that you rebuild your indexes at the same time (DBCC DBREINDEX and DBCC INDEXDEFRAG).

Answer 3

If you rebuild indexes, the statistics of these indexes are automatically rebuilt. If your time frame allows, then performing the UPDATE STATISTICS part of a maintenance plan is a good idea, as often as at night (if your indexes are rebuilt less often than this).

SQL Server: To determine if outdated statistics are causing poor query execution, enable “Query-> Display Estimated Execution plan” (CTRL-L) in Management Studio and run the query. Open another window, insert the same query and turn on the “Control Plan” (CTRL-M) in the Query-> Display ActualExecution plan (CTRL-M) and run the query. If the execution plans are different, then the statistics are most likely out of date.

Answer 4

Updating statistics becomes necessary after the following events:
- Entries are inserted into your desk
- Entries are deleted from your desk
- Entries are updated in your table

If you have a large database with millions of records that receives a lot of emails per day, you should probably determine the time without a peak for planning index updates.

In addition, you need to consider your type of traffic. If you have many (millions) of records in tables with many foreign key dependencies, and you have a large proportion of records to read, you might want to turn off automatic statistics recounting (NOTE: this feature will be removed in a future version of SQL Server, but for SQL Server 2000 you should be fine). This tells the engine not to recompile statistics for each INSERT, DELETE, or UPDATE and makes these actions much more efficient.

Indexes are not a laughing matter. They are the heart and soul of the artist database.