About Me

My photo
Northglenn, Colorado, United States
I'm primarily a BI Developer on the Microsoft stack. I do sometimes touch upon other Microsoft stacks ( web development, application development, and sql server development).

Monday, October 15, 2007

Optimizing SQL Server CPU Performance

http://www.microsoft.com/technet/technetmag/issues/2007/10/SQLCPU/default.aspx

Some interesting FYI facts from the article:

  • In performance value: high-end dual-core processor > RAM > fibre optics > disk drive
  • A data page in SQL Server is 8KB.
  • An extent in SQL Server is made up of eight 8KB pages, making it equivalent to 64KB.
  • Pulling a data page that is already cached from the buffer pool, at peak performance, should take under half a millisecond; retrieving a single extent from disk should take between 2 and 4 milliseconds in an optimal environment.

Check CPU utilization with PerfMon by monitoring: % Processor Time (<80%),>

Some optimizations are:
Query plan reuse
Reducing compiles and recompiles
Sort operations
Improper joins
Missing indexes
Table/index scans
Function usage in SELECT and WHERE clauses
Multithreaded operation

Resources for information on query plan reuse:
Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005 (microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx)
Optimizing SQL Server Stored Procedures to Avoid Recompiles (sql-server-performance.com/rd_optimizing_sp_recompiles.asp)
Query Recompilation in SQL Server 2000 (msdn2.microsoft.com/aa902682.aspx)

No comments: