Transcript Performance Tuning SQL Server

SQL Server Performance
Audit and Tuning
Jason Pack
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
Performance Monitor
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
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
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 value]
GO
RECONFIGURE WITH OVERRIDE
GO, to change the settings
Database Configuration

Right-click the
database in
Enterprise
Manager, select
Properties, then
Options
Database Configuration (2)

For reporting databases, set to Readonly

Database auto-grow

Transaction log auto-grow
• Estimate well, auto-grow creates virtual
files, increasing recovery time
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
Application Tuning


Most important aspect in improving
performance
Not always possible
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
Don’t return more data than you need
SQL Profiler

Events
• Stored Procedures

RPC:Completed
• TSQL


SQL:BatchComplete
d
Filters
• Duration > 5000
ms
• No system events

Data Columns
•
•
•
•
•
•
•
•
Duration (group by)
Event Class
Database Identifier
TextData
CPU
Writes
Reads
SPID
SQL Profiler (2)
References

SQL-Server-Performance.Com, Brad McGehee, February 2005
•

Microsoft MSDN, Meier, J. D., Vasireddy, S., Babbar, A., et al., May
2004
•

http://msdn.microsoft.com/library/default.asp?url=/library/enus/dnpag/html/scalenetchapt14.asp
SQL-Server-Performance.Com, Geert Vanhove, June 2005
•

http://www.sql-server-performance.com/sql_server_performance_audit10.asp
http://www.sql-server-performance.com/gv_monitoring_8_steps.asp
Microsoft MSDN
•
http://msdn.microsoft.com/library/default.asp?url=/library/enus/createdb/cm_8_des_05_2ri0.asp