SQL Server Performance Audit and Tuning

1
SQL Server Performance Audit and Tuning

• Jason Pack

2
Overview

• Using Performance Monitor
• Hardware and Operating System Performance
• SQL Server Configuration Settings
• Database Configuration Settings
• Index Performance Tuning
• Application Tuning
• SQL Profiler on Queries

3
Performance Monitor
4
Performance Monitor (2)

• Typical counters to monitor
• Pages per second (memory)
• Want to average less than 20
• Available bytes (memory)
• Should be over 5MB (on dedicated machine)
• Percent disk time (physical disk)
• Over 55 for 10 minutes indicates a bottleneck
• Percent processor time
• Over 80 for 10 minutes indicates a bottleneck

5
Tuning Hardware and Operating System Performance

• More RAM Good
• Check disk fragmentation
• Separate operating system files and SQL Server
  data files
• Be sure OS has newest SP
• Server should be configured as stand-alone server
• Turn off unnecessary services

6
SQL Server Configuration Settings

• In general, no need to modify
• If you have to
• SP_CONFIGURE, will show your settings
• SP_CONFIGURE 'configuration name',
  configuration setting valueGORECONFIGURE WITH
  OVERRIDEGO, to change the settings

7
Database Configuration

• Right-click the database in Enterprise Manager,
  select Properties, then Options

8
Database Configuration (2)

• For reporting databases, set to Read-only
• Database auto-grow
• Transaction log auto-grow
• Estimate well, auto-grow creates virtual files,
  increasing recovery time

9
Index Performance Tuning

• Run the Index Tuning Wizard
• Only available in Enterprise edition
• Start with busiest database, and largest tables
• Every table, in every database, should have a
  clustered index on the PK
• Allows the data to be stored, physically, in order

10
Application Tuning

• Most important aspect in improving performance
• Not always possible

11
Application Tuning (2)

• Use stored procedures whenever possible
• Include SET NOCOUNT ON in stored procedure
• Keep transactions as short as possible
• Choose OLE DB over ODBC, as it is generally
  faster
• Dont return more data than you need

12
SQL Profiler

• Events
• Stored Procedures
• RPCCompleted
• TSQL
• SQLBatchCompleted
• Filters
• Duration gt 5000 ms
• No system events

• Data Columns
• Duration (group by)
• Event Class
• Database Identifier
• TextData
• CPU
• Writes
• Reads
• SPID

13
SQL Profiler (2)
14
References

• SQL-Server-Performance.Com, Brad McGehee,
  February 2005
• http//www.sql-server-performance.com/sql_server_p
  erformance_audit10.asp
• Microsoft MSDN, Meier, J. D., Vasireddy, S.,
  Babbar, A., et al., May 2004
• http//msdn.microsoft.com/library/default.asp?url
  /library/en-us/dnpag/html/scalenetchapt14.asp
• SQL-Server-Performance.Com, Geert Vanhove, June
  2005
• http//www.sql-server-performance.com/gv_monitorin
  g_8_steps.asp
• Microsoft MSDN
• http//msdn.microsoft.com/library/default.asp?url
  /library/en-us/createdb/cm_8_des_05_2ri0.asp