SQL Server Performance Audit and Tuning - PowerPoint PPT Presentation

View by Category
About This Presentation
Title:

SQL Server Performance Audit and Tuning

Description:

Title: Performance Tuning SQL Server Author: Jason Pack Last modified by: Jason Pack Created Date: 11/16/2005 12:43:01 AM Document presentation format – PowerPoint PPT presentation

Number of Views:51
Avg rating:3.0/5.0
Slides: 15
Provided by: JasonP169
Category:

less

Write a Comment
User Comments (0)
Transcript and Presenter's Notes

Title: 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 value GO RECONFIGURE WITH
    OVERRIDE GO, 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
About PowerShow.com