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
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.)