question

narendba avatar image
narendba asked

Update staticstics in sql server 2005?

Can any one explayn briefly about exactly what is updat staticstics ( sp_updatestats)... Is there any diagrmatic explanation or video for it with examples.... How the flow is going on and what type of information updated using update staticstics?
sql-server-2005statisticsquery-planselectivity
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Yes, whenver statistics are updated....queries agsinst those statistics are recompiled.
1 Like 1 ·
After updating the staticstics by auto update statistics on... Automatically is it Dropping the execution plane and recreating it in Procedure Cache?
0 Likes 0 ·
Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
Ok, first something on what kind of animals statistics are, and how SQL Server handles them. Statistics are objects that SQL Server optimizer uses to produce a good enough query plan for a query. The default setting in SQL Server is to automatically create statitics and to automatically update statistics. Statistics are created for either indexes or for non-indexed columns. When you look at an estimated query plan, you'll see lots of "Estimated number of rows". That estimation is based upon the statistics for the underlying columns involved in a query's WHERE-, JOIN- and other clauses. As data changes in a table, the statistics might become old. There are certain rules for when SQL Server considers statistics too old (for example 20% of the rows for a certain statistics have changed). If Auto Update Stats is set to on, SQL Server will automatically update the statistics when: - Statistics for an underlying column are old. - A query references a column for which the old statistics object is needed. The problem with this is that SQL Server will update the statistics when a query is entered and SQL Server tries to produce a query plan. For a large table, update statistics will take a while and for an online query, it's not OK to wait for several seconds. Statistics can be manually updated, using the UPDATE STATISTICS command. UPDATE STATISTICS can either sample a percentage of the rows or scan all rows. To scan all rows, use **UPDATE STATISTICS table WITH FULLSCAN**. If no sample ration is given, SQL Server will use whatever sample ratio it finds most suitable (which is not always optimal, but is in most cases good enough). And now on to sp\_updatestats: sp\_updatestats will update statistics for all tables in a database. If the parameter @resample has the value 'resample', it will use whatever sample ration was last used to update statistics. If that argument is not given, sp_updatestats will use whatever sample ratio SQL Server finds most suitable.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

After updating the staticstics by auto update statistics on... Automatically is it Dropping the execution plane and recreating it in Procedure Cache?
0 Likes 0 ·
Kev Riley avatar image
Kev Riley answered
Here's a decent reference from Christian Bolton's session at SQLBits : http://sqlbits.com/Sessions/Event5/Vital_Statistics
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.