August 6, 2008 at 10:40 am
Hi,
I have an multiple index on my tables (SS 2k5), i have the checked the option of "Automatically Update the Statistics" in the index properties window. Now
1-) do i have to schedule a job to update the statistics explicitly?or
2-) sql server will do the work for me, if yes then when will it update the statistics?after every DML operation?or will it schedule it implicitly?
Thanks,
Usman
August 6, 2008 at 11:02 am
The answer depends on how the database is set up, check if the following query returns 1 for is_auto_update_stats_on or is_auto_update_stats_async_on:
[font="Courier New"]select name, database_id, is_auto_update_stats_on, is_auto_update_stats_async_on
from master.sys.databases[/font]
When auto update stats is on, the database engine determines when it believes the statistics data is out of date (about 20% of the rows have been updated according to books online) and will recalculate the statistics at that time. On a production system though, depending on how many rows are in your table, this could result in a delay of a query as the query optimizer waits for the statistics to be recalculated before determining the plan. That's when you would use:
[font="Courier New"]ALTER DATABASE x SET AUTO_UPDATE_STATISTICS_ASYNC ON [/font]
which causes the query execution to not wait for statistics.
For more information check out the topic "Index Statistics" in books online.
August 6, 2008 at 11:10 am
First I'd like to make clear that Auto update statistics does not reorganize your indexes. So you should schedule a job to do those. The database option auto update statistics will update statistics based on 3 conditions which are used to determine if statistics are outdated.
If the rowcount in the table has gone from zero to any nonzero value, statistics are outdated.
If the number of rows in the table was under 500, and at least 500 rows have been modified (added/deleted/edited), statistics are outdated.
If the number of rows in the table was over 500, and at least 500 plus 20% of the rowcount rows have been modified (added/deleted/edited), statistics are outdated.
If you think these conditions are not enough, then you can schedule your own job to update statistics more often.
If you want to see when statistics have been updated the last time use DBCC SHOW_STATISTICS
[font="Verdana"]Markus Bohse[/font]
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply