Writing Applications that make SQL Server 2005 Fly

Shu J Scott

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

Goal

• 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

Agenda

• 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

Demo

• 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 1^st 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)