Thursday, August 23, 2007

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 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)

No comments:

Post a Comment