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:
Post a Comment