December 28, 2007 at 5:00 am
what is use of update statistics ?
how can we use ...
pls expalin with example....
ragards
paresh
December 28, 2007 at 5:12 am
SQLServer (like any rdbms) uses statistics to determine an accessplan for your query.
If there are no statistics available it will use predefined distribution assumptions.
At database level you can set autoupdatestatistics on (default) or off.
If you turn it of, you'll have to execute sp_updatestats to gather or refresh statistics.
Statistics are build for indexed columns or you can build statistics for unindexed columns yourself using create statistics.
As your database is being used (insert/update/delete/reindex) statistical values may change, so there may be more optimal accesspaths for your queries.
If you statistics do not contain statistics for the actual data, sqlserver may choose a less optimal accesspath to server you. This will cost time, IO, locking, ...
It is advised to set autoupdatestatistics ON unless you suffer it's overhead to much.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 31, 2007 at 1:32 am
Statistics determine the selectivity of the indexes. If an indexed column has unique values then the selectivity of that index is more, as opposed to an index with non-unique values. Query optimizer uses these indexes in determining whether to choose an index or not while executing a query. Some situations under which you should update statistics:
1) If there is significant change in the key values in the index
2) 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
3) Database is upgraded from a previous version.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply