Statistics in 2005

Today I saw the Webcast by Eric N. Hanson upgrade consideration for query optimizer stats.


Whets new on Statistics in 2005:

1. After upgrade from 2000 db. Statistics will be out of date as internal of stats is different in 2005, so if you see performance issue after upgrade update stats.

2. Statistics is on logical data so no need to update stats after defrag.

3. Aysnc stats in 2005, which update stats in background.

4. Stats now can be on computed columns.

5. String summary is now good, like now its stats can be generate for like ‘%abc%’ in a better way.

6. Minimum sample size is 8mb

7. In 2000 stats was limited to 16columns now in 2005 it is up to 32 columns.

8. Supports partition of table (new in 2005) also stats can be generated parallel for big tables.

9.  Sp_updatestats now update only changes data considering change in sys.indexes -> rowmodctr


Best practices:

1. Keep auto create stats and auto update stats On (Default ON)

2. Avoid local variable in condition -Alternatives: dynamic query with parameter or recompile option on query

3. Avoid table variable -alternatives use real table or tmp table.

* Auto update stats give SAMPLE stats.

* Statistics auto update after change of 20%

*  Sample stats may not efficient (check the query and increase the sample rate with update stats) could be for:

     Size of table is very large; the database table has many unique values.


Webcast was great Thanks Eric.




(This is my understanding of webcast.)

This entry was posted in What I learned today and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.