About My SQL Server Performance Tuning - Day 2

Day 2:

 1. Group similar queries together (by replacing parameters with @var1...n)

     a. Calculate AVERAGE READS, WRITES, CPU and DURATION for each group of queries

         -> serve as baseline of tuning

         -> tuned queries should be executed with less resource, which means lower than baseline

     b. Count frequency of execution for each group of queries

         -> perform impact analysis

     c. Select queries that can represent overall picture of database performance

         -> Approach 1: select a single query from each group that is close to AVERAGE

         -> Approach 2: select the slowest query from each group

         -> Approach 3: select MOD (highest frequency) query from each group (have to divide same group of queries into multiple ranges)

 2. Schedule a SQL Job to run selected queries at certain interval (hourly, 2-hourly etc.)

     -> make sure we cover both peak and non-peak hours of a day

     -> one job step runs ONLY ONE query

 3. Turn on SQL Trace

 4. On separate environment, prepare scripts to tune queries

     a. Index tuning

         i.   Update statistics

         ii.  Rebuild index (adjust fill factor if necessary)

         iii. Create/Drop indexes

         -> SQL Server 2005 has a dynamic management view to check index usage

         -> remove unused indexes

         -> check recommendation for missing index

     b. Create plan guide

     c. Create/Remove/Move data/log files

     d. TempDB adjustment

     e. Create database maintenance plans

End of the day:

 1. Capture all values for selected baseline queries