About My SQL Server Performance Tuning - Day 1

Day 1:

  1. Collect hardware and software info

      a. CPU -> number of processors/cores, CPU speed, useable by SQL Server

      b. O/S -> platform, version, edition, 32/64 bit, service pack/patches

      c. Memory -> total available, allocated to SQL Server, speed (MHz)

      d. SQL Server -> version, edition, 32/64 bit, service pack/pacthes

      e. Hard Disk -> SAN/NAS/DAS, RAID, total space for each logical partition, available free space

      f. Network Interface -> bandwidth (LAN, Intranet, Extranet, Internet)

  2. Start SQL Trace to capture 100% data

       a. Columns: TextData, DatabaseID, HostName, SPID, ApplicationName, LoginName, Reads, Writes, Duration, StartTime, CPU, RowCounts

       b. Events: RPC:Completed, SQL:BatchCompleted, Exception

  3. Turn on Performance Monitor

       a. Processor

       b. Logical Disk

       c. SQL Server

       d. Memory

       e. Network Interface

       f. Process

  4. Understand business cycle

       a. Peak hours

       b. Peak days

       c. Peak seasons

End of the Day:

 1. Identify HIGH READS/WRITES/CPU/DURATION queries

 2. Identify frequent running queries

 3. Prepare baseline queries, which will be scheduled to run at certain interval on Day 2