sql server performance audit and tuning n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
SQL Server Performance Audit and Tuning PowerPoint Presentation
Download Presentation
SQL Server Performance Audit and Tuning

Loading in 2 Seconds...

play fullscreen
1 / 14

SQL Server Performance Audit and Tuning - PowerPoint PPT Presentation


  • 184 Views
  • Uploaded on

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.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

SQL Server Performance Audit and Tuning


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
    Presentation Transcript
    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]GORECONFIGURE WITH OVERRIDEGO, to change the settings

    7. Right-click the database in Enterprise Manager, select Properties, then Options Database Configuration

    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 • Don’t return more data than you need

    12. Events Stored Procedures RPC:Completed TSQL SQL:BatchCompleted Filters Duration > 5000 ms No system events Data Columns Duration (group by) Event Class Database Identifier TextData CPU Writes Reads SPID SQL Profiler

    13. SQL Profiler (2)

    14. References • SQL-Server-Performance.Com, Brad McGehee, February 2005 • http://www.sql-server-performance.com/sql_server_performance_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_monitoring_8_steps.asp • Microsoft MSDN • http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_05_2ri0.asp