1 / 48

Monitoring and Optimizing SQL Server 2005 Performance

Monitoring and Optimizing SQL Server 2005 Performance. Anil Desai. Speaker Information. Anil Desai Independent consultant (Austin, TX) Author of several SQL Server books Instructor, “Implementing and Managing SQL Server 2005” (Keystone Learning)

landen
Download Presentation

Monitoring and Optimizing SQL Server 2005 Performance

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Monitoring and Optimizing SQL Server 2005 Performance Anil Desai

  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 • Using SQL Profiler • Integrating with Windows System Monitor • Getting Performance Information • Dynamic Management Views (DMVs) • SQL Server Management Studio Reports • Using the Database Engine Tuning Advisor • Managing Processes, Locking, and Deadlocks • Partitioning and Physical Storage Design

  4. Performance Monitoring Approaches • Performance optimization process: • Establish a baseline • Identify bottlenecks • Make one change at a time • Measure performance • Repeat (if desired) • Recommendations • Optimize for real-world workloads • Monitor/review performance regularly • Focus on specific issues

  5. Overview of Performance Monitoring • System • Windows System Monitor • Alerts (Performance-Based) • SQL Server • SQL Profiler / SQL Trace • Activity Monitor • Dynamic Management Views (DMVs) • SQL Server Agent Alerts • Query-Level • Database Engine Tuning Advisor • Query Execution Plans

  6. 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)

  7. 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

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

  9. 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

  10. 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)

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

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

  13. 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

  14. 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

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

  16. 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

  17. 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

  18. Database-Level Reports • Examples: • Disk Usage • All Transactions • All Blocking Transactions • Index Usage Statistics • Top Transactions by Age • Schema Changes History

  19. 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

  20. 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

  21. 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

  22. Workloads • 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

  23. 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

  24. DTA Reports • Can export to XML files • Examples: • Workload analysis • Column access • Table access • View-Table Relations • Statement cost • Event frequency • Index Usage (current / recommended)

  25. 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

  26. 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

  27. 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

  28. 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)

  29. 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

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

  31. 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

  32. 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

  33. 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

  34. 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

  35. 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

  36. 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)

  37. Deadlock Graph

  38. Understanding Partitions • Physical distribution of data • Partitions can be on the same or different filegroups • Partitioning Methods • Horizontal Partitioning • Vertical Partitioning • Example: • Partition 1: Current Sales Data (Current Year) • Partition 2: Archived Sales Data

  39. Benefits of Partitioning • No special requirements for users or applications • Can partition tables and indexes • Fast transfer of data between partitions • Supports “Sliding” windows • Can improve query performance • Simplifies management of large tables • Increases maintenance performance (indexes)

  40. Designing Partitions • Reasons to implement partitioning • Large tables • Performance problems • Data usage (read-only historical data) • Partial backups • Supported in Enterprise and Developer Editions • Can have up to 1,000 partitions

  41. Implementing Partitions • Process: • Create a partition function • Create a partition scheme • Create a table using the scheme • Other options: • Managing partitioned data • Creating CHECK constraints

  42. Creating a Partition Function • Defines sets of data based on ranges within the partitioning column • Examples: Dates, Row IDs, alpha values • RANGE LEFT (default) / RIGHT • Computed columns must be persisted • Example: CREATE PARTITION FUNCTION fn_part1 (int) AS RANGE LEFT FOR VALUES (1000, 2000, 3000) • $PARTITION Function shows on which partition data would reside

  43. Creating a Partition Scheme • Defines storage options for each partition • Based on a single partition function • Can specify files or filegroups for storage • A filegroup can be used for multiple partitions • Example: CREATE PARTITION SCHEME Partition1 AS PARTITION PartitionFunction TO (FileGroup1, FileGroup2, FileGroup3)

  44. Creating Partitioned Tables • Specify the partition scheme when creating a table • CREATE TABLE … ON PartitionScheme • Data storage will be based on the partition scheme

  45. Creating Partitioned Indexes • “Aligned” partitioning • Index partitions are based on table partitions • Managed automatically by SQL Server • Optimizes switching performance • Manual partition management • Can use CREATE INDEX … ON clause • Partition column is automatically included in clustered and non-clustered indexes

  46. Managing Partitioned Data • Physical location of data may not be moved (only pointers are updated) • CHECK constraints can be used to manage data • “Sliding Windows” • Partition functions can be modified • Splitting: Adding a new partition • Merging: Combining two partitions • Partition switching • ALTER TABLE … SWITCH • Moves data between partitions

  47. Working with Partitioned Data • SQL Server Management Studio • Database Reports  Disk Space Used by Partitions  ObjectName • System Views: • Sys.Tables / Sys.Indexes • Sys.Partitions • Sys.Partition_Schemes • Sys.Partition_Parameters • Sys.Partition_Range_Values

  48. For More Information • Resources from Anil Desai • Web Site (http://AnilDesai.net) • E-Mail: Anil@AnilDesai.net • Keystone Learning Course: “Microsoft SQL Server 2005: Implementation and Maintenance (Exam 70-431)” • The Rational Guide to Managing Microsoft Virtual Server 2005 • The Rational Guide to Scripting Microsoft Virtual Server 2005

More Related