New Zealand's new Copyright Law presumes 'Guilt Upon Accusation' and will Cut Off Internet Connections without a trial. is against this unjust law - help us

A Space for All Things .NET Related

Writing Applications that make SQL Server 2005 Fly

By James Hippolite, in , posted: 23-Aug-2007 07:24

Shu J Scott

  • Program Manager for Query Processing
  • SQL Server Relational Engine
  • Microsoft
  • (Part of Tech Ed 2007)


  • Reduce the cost of managing database applications
    • Understand SQL server Query Optimizer specifics
    • Help the Optimizer produce quality query plans
    • Avoid problems through design/implementation best practices


  • Make app run fast independent of the platform
  • SQL Server specific technical
    • SQL Server Query compilation Overview
    • Necessary conditions of quality query plans
    • Importance of good stats
    • Importance of indexes
    • Influence optimizer behaviour with query hints
  • Q&A

Platform-independent Techniques To Make App Run Faster

  • Good logical DB design
  • Good physical DB design
  • Proper hardware (memory, I/O, processing power)
  • Minimize # of round-trop messages between application and DB server
  • Use set-oriented power of query processor
  • Etc.

SQL Server 2005 Query Compilation Overview

  • New batch
  • Cache lookup
  • Parsing
  • For each statement
    • Plan guide match
    • Forced autoparam
      • Cache Lookup
      • Algebrization
      • Optimization
      • Cache new plan
    • Simple autoparam

Importance of Good Stats

  • Good Stats (leads to)
  • Good Estimates (leads to)
  • Good Plans (leads to)
  • Good query performance


  • What happens when QO has to guess selectivity?
  • For ad hoc queries, don’t use local variables, use static values
  • For procs, parameters are fine.

Best Practices for Managing Query Plan

  • Use AutoStats!
    • On by default
      • “no stats” causes bad plans
      • It is right much more often than it is wrong
    • If you must turn it off, don’t turn it off for whole DB
      • Selectively disable it for certain columns or tables
    • Watch out for read-only DB preventing auto-create/update of stats
  • Use FULLSCAN if Needed
    • Full scan gives best-quality histograms
    • Hard to determine what % is ideal, so just use FULLSCAN to eliminate sampling rate as a potential problem
    • You can usually afford the time to do FULLSCAN during your nightly batch window
    • FULLSCAN stats are gathered in parallel
  • Avoid Use of Local Variables in Queries
  • Avoid Updating SP Parameters Prior to Using It in a Query
    • Stored proc and all queries in it are complied with the parameter values 1st passed to it – parameter sniffing
  • Avoid Using a Function with Column Input in a Predicate
  • Avoid Use of Multi-statement TVFS and Table Variables in Plan-sensitive Queries

(here my battery died)


Other related posts:
Teach Yourself Azure in 4 hours
Geek Post Monthly Newsletter Volume 2 Issue 10

Add a comment

Please note: comments that are inappropriate or promotional in nature will be deleted. E-mail addresses are not displayed, but you must enter a valid e-mail address to confirm your comments.

Are you a registered Geekzone user? Login to have the fields below automatically filled in for you and to enable links in comments. If you have (or qualify to have) a Geekzone Blog then your comment will be automatically confirmed and shown in this blog post.

Your name:

Your e-mail:

Your webpage:

JamesHip's profile

James Hippolite
New Zealand

Welcome to my technical blog. 

Here, I attempt to distill the Microsoft Certified Professional Developer knowledge I have accumulated since first qualifying MCP in 1996.  This blog started on 13 September 2007 as an off-shoot from my mixed up personal blog.  But it took a shot in the arm from Scott Hanselman's talk at TechEd New Zealand 08 "32 Ways To Make Your Blog Suck Less".