Reading Sql Server Internals -- Kalen Delaney....

 

 

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.

 

Reference:

http://technet.microsoft.com/en-us/library/cc966419.aspx

www.microsoft.com/webcasts

 

(This is my understanding of webcast.)

 

Reference :

Yesterday I learn saw buckwoody's Webcast on 1032391435_Real-World DBA Improvements for SQL Server 2008 .Its great.

 

1.Manage by Policies: Like if we want to restirct the policy as all store procedure should start with ups_ we can manage this. we can manage complince

2.Manage with insight: Management warehouse database,

3.Manage interactivity:Activity monitor, Object search, windows powershell, T-sql intellisence, multi-server queries, T-sql Debugger.

*Activity monitor will not work for sql server 2000 and old servers.

 

Thanx Buck woody... it was great.

 

Reference :www.microsoft.com\webcasts

 

I am also on twitter now... its realy good.

 

@hellovinay

 

Thanx.

Vinay