SQL Server Performance Monitoring & Optimization

1. SQL Server Performance Monitoring & Optimization Anil Desai Austin CodeCamp 2008

2. Speaker Information • Anil Desai • Independent consultant (Austin, TX) • Author of several SQL Server books • Instructor, “Implementing and Managing SQL Server 2005” (Keystone Learning) • Info: http://AnilDesai.net or Anil@AnilDesai.net

3. Agenda and Overview • Performance Monitoring Overview • Monitoring SQL Server • Using SQL Profiler • Using the Database Engine Tuning Advisor • Managing Processes, Locking, and Deadlocks

4. Performance Monitoring Overview Developing processes and approaches for performance optimization

5. Performance Monitoring Approaches • Best Practices: • Optimize for real-world workloads • Monitor/review performance regularly • Focus on specific issues

6. Overview of Performance Monitoring Tools and Methods

7. Monitoring SQL Server Using various SQL Server features to monitor database activity

8. SQL Server Management Studio Reports • Overviews of SQL Server usage • Can export to Excel or PDF • Server-Level Report Examples: • Server Dashboard • Memory Consumption • Activity – All Block Transactions • Activity – Top Sessions • Performance – Batch Execution Statistics • Performance – Top Queries by Average CPU • Object Execution Statistics

9. Database-Level Reports • Examples: • Disk Usage • All Transactions • All Blocking Transactions • Index Usage Statistics • Top Transactions by Age • Schema Changes History • New reports added in Service Packs • Ability to use custom reports

10. Monitoring SQL Server Logs • Windows Event Logs / Event Viewer • Application and System Event Logs • SQL Server Management Studio • SQL Server Logs • Can configure max. # of log files • SQL Server Agent Error logs • Can configure logging levels (Errors, Warnings, Information) • Using the Log File Viewer • Can Export / Load log information • Can search for specific errors/messages

11. Using Dynamic Management Views (DMVs) • Purpose: • Monitoring and troubleshooting • View server state and performance details • Returns relational result sets • Scopes: • Server level • Database level

12. DMV Examples • Examples: • Database Engine • Sys.DM_DB_File_Space_Usage • Indexes • Sys.DM_DB_Index_Operational_Stats • Sys.DM_DB_Index_Physical_Stats • I/O Related • Sys.DM_IO_Pending_IO_Requests • Sys.DM_IO_Virtual_File_Stats • Common Language Runtime • Database Mirroring • Transactions

13. Using SQLDiag • Data Collected: • System Information (MSINFO) • Windows Event Logs • SQL Server configuration • Command-Line Utility (SQLDiag.exe) • Stores output to files • Configuration file: SQLDiag.xml • Can run as a service (/R) • Can run in continuous mode

14. Using SQL Profiler Monitoring SQL Server Activity

15. Understanding SQL Profiler • Purpose / Features: • GUI for managing SQL Trace • Monitor important events • Capture performance data / resource usage • Replaying of workloads / transactions • Identifying performance bottlenecks • Correlation of data with System Monitor • Workloads for Database Tuning Advisor • Examples: • Generate a list of the 100 slowest queries • Monitor all failed logins (Security)

16. SQL Server Profiler Architecture • SQL Profiler Terminology • Trace Definitions • Events • Columns • Filters • Creating and Managing SQL Traces • SQL Profiler (GUI) • System Stored Procedures (Transact-SQL) • Trace Templates (Built-In) • Standard (Default), SP_Counts • TSQL, TSQL_Duration, TSQL_Grouped,TSQL_Replay, TSQL_SPs • Tuning

17. Configuring Trace Events • Groupings: • Event Categories • Event Classes • Events • Examples: • TSQL • Stored Procedures • Performance • Errors and Warnings • Security auditing

18. Configuring Trace Columns • Specify the details to be recorded • Columns can be ordered and grouped • Values can be filtered • Examples of Columns: • StartTime / EndTime • TextData • Duration • Resource Usage (CPU, Reads, Writes) • Information: User, Database, App. Names

19. Trace Output Options • Interactive • Good for “live” monitoring of small sets of data • Trace Files (*.trc) • Can enable file rollover based on size • “Server processes trace data” option • Trace table • Will automatically create the table • Can set maximum number of rows • Scheduling of traces (stop time)

20. Creating Profiler Traces • Launching SQL Profiler • Connecting to a database instance • Configuring output options • Create a trace definition • Specifying events, columns, and filters • Running and viewing a trace

21. Other SQL Profiler Options • Creating new templates • Scripting trace definitions • Extracting SQL Server Events • Transact-SQL Events • ShowPlan Events • Deadlock Events

22. Using System Monitor with SQL Profiler • Purpose / Goal: • Correlate server performance with database performance • Process: • Define and start a counter log • Define and start a SQL Profiler trace • Import Performance Data in SQL Profiler • Required Trace properties • StartTime • EndTime

23. Windows Performance Monitor • Can monitor local or remote computers • Performance Statistics: • Objects • Counters • Instances • Modes: • System Monitor • Performance Logs and Alerts • Counter Logs • Trace Logs • Alerts

24. Using the Database Engine Tuning Advisor Analyzing workloads to optimize physical database structures

25. Database Engine Tuning Advisor • Can make performance-related recommendations • Replaces the “Index Tuning Wizard” • Evaluates Physical Design Structures (PDS) • Indexes (clustered, non-clustered) • Indexed Views • Partitions • Numerous analysis options • Output • Generates modification scripts • Generates Reports for later analysis

26. Workload Sources • Files • Transact-SQL Files • XML Files • Should represent commonly-used queries • SQL Profiler Trace Files / Tables • Use Tuning built-in trace template • Events: • Transact-SQL Batch • Remote Procedure Call (RPC) • Columns: Event Class and Text Data

27. DTA Options • Limit tuning time • Tuning Options • Allowed Physical Design Structures (PDS) • Keep all/specific existing objects • Maximum storage space • Online or offline recommendations • Partitioning

28. DTA Reports • Reports can be exported to XML files • Report Examples:

29. Using the Database Engine Tuning Advisor • Process: • Generate a workload (file or table) • Select tuning options • Run the analysis • View reports • Save and/or apply recommendations • Running the DTA: • Database Engine Tuning Advisor Application (GUI) • Dta.exe command-line utility

30. Managing Processes, Locking, and Deadlocks Troubleshooting common SQL Server performance problems

31. Understanding Processes • Processes • Interactive users • SQL Server Management Studio • Applications (Connection Pooling) • SQL Profiler • Database Engine Tuning Advisor • Replication • Service Broker • Process IDs < 50 are system-related

32. Monitoring Processes • SQL Server Activity Monitor • Processes (connected users) • Locks (by Process / by Object) • Filtering options • Auto-refresh option • System Stored Procedures / Views • Sys.DM_Exec_Sessions • Sys.DM_Exec_Requests • Sys.SysProcesses • sp_who / sp_who2

33. Managing Processes • Process Information • Current Process ID: @@SPID • Session Options: DBCC USEROPTIONS • Killing Processes • KILL ProcessID [WITH STATUSONLY] • Viewing Last Activity • DBCC INPUTBUFFER(ProcessID) • DBCC OUTPUTBUFFER(ProcessID)

34. Understanding Locking • Coordinates multiple accesses to the same data • Ensures ACID Properties for transactions (Atomic, Consistent, Independent, Durable) • Contention can reduce performance • Locking granularity: • Row-Level, Page-Level, Table-Level, etc. • Lock Modes: • Shared, Exclusive, etc. • Lock escalation

35. Understanding Blocking • Blocking • When transaction(s) must wait for a lock on a resource • LOCK_TIMEOUT setting (default = wait forever) • Locking Models: • Pessimistic • Optimistic

36. Transaction Isolation Levels • Balance of concurrency (performance) vs. consistency • Affects SELECT queries • SET TRANSACTION ISOLATION LEVEL • Transaction Isolation Levels • READ UNCOMMITTED • READ COMMITTED (default) • REPEATABLE READ • SERIALIZABLE • Row-Versioning: • ALLOW_SNAPSHOT_ISOLATION • READ_COMMITTED_SNAPSHOT

37. Monitoring Locking Activity • Activity Monitor • SQL Profiler • Locks Event Category • System Monitor: • SQL Server  Locks Object • System Views • Sys.DM_Tran_Locks • Sys.DM_Exec_Requests • System Stored Procedures • sp_Lock

38. Understanding the Deadlock Process • Deadlocks: • Two or more tasks permanently block each other based on resource locks • Default resolution is within 5 seconds • Deadlock victim • Transaction is rolled-back • Process receives a 1205 error • Example: • Process 1 locks the Customers table and requires access to the Orders Table • Process 2 locks the Orders table and requires access to the Customers Table

39. Avoiding Deadlocks • Minimize transaction times • Commit / Rollback transactions as quickly as possibly • Avoid user-related time within a transaction • Access objects in a consistent order • Change the transaction isolation level • Use a lower level isolation level, if appropriate • Use snapshot-based isolation levels

40. Deadlock Victims • Deadlock priorities: • SET DEADLOCK_PRIORITY (LOW, NORMAL, HIGH, integer) • Deadlock resolution: • Lower priority is killed first • If equal priorities, least expensive transaction becomes the victim • Application or user should attempt to re-run the transaction

41. Monitoring Deadlocks • SQL Server Error Log • SQL Profiler • Locks Event Category • Lock:Deadlock Chain • Lock:Deadlock • Deadlock Graph • Events Extraction Trace Property • Export deadlock XML (.xdl) file • Viewing Deadlock Files • SQL Server Management Studio (File  Open  SQL Deadlock Files (*.xdl)

42. Deadlock Graph

43. For More Information • Resources from Anil Desai • Web Site (http://AnilDesai.net) • E-Mail: Anil@AnilDesai.net • ReportingServicesGuru.com • Keystone Learning Course: “Microsoft SQL Server 2005: Implementation and Maintenance (Exam 70-431)”

44. Questions & Discussion