SQL Server Performance Tuning Baseline

1 GE Title or job number 8/15/2012

I. Performance Monitoring Overview II. Monitoring Database Servers III. Using SQL Profiler IV. Using the Database Engine Tuning Advisor V. Application Design Tips VI. Managing Processes, Locking, and Deadlocks 2 GE Title or job number 8/15/2012

Best Practices: Establish a baseline  Optimize for real- world workloads Repeat Identify (if desired) bottlenecks  Monitor/review performance regularly Measure Make one  Focus on specific performance change at a time issues 4 GE Title or job number 8/15/2012

System/OS SQL Server Query-Level Activity Monitor Windows Performance Database Engine Monitor Tuning Advisor SQL Profiler / SQL Trace Database Engine Tuning Advisor Alerts (Performance- Based) Query Execution Plans Dynamic Management Views (DMVs) 5 GE Title or job number 8/15/2012

• Users are reporting database timeouts Server-Level • Intermittent transaction timeouts Issues • “The server/application seem sluggish” Application- • “Application A is running more slowly Specific than usual” Issues • “The End-of-Month report is taking too long to run.” • Ad-hoc reports are running slowly Other Issues • CPU, memory, disk, or network alerts are being generated 6 GE Title or job number 8/15/2012

Available in all current versions of Windows Statistics are organized into:  Objects  Counters  Instances Data Collector Sets  Windows Vista / Windows 7 / Windows Server 2008  Used to report on performance data that is collected over time  Includes built-in System Diagnostics and System Performance collectors and reports 8 GE Title or job number 8/15/2012

Backup Device General Statistics  Device throughput Bytes/sec  Logins | logouts / sec Buffer Manager  User Connections  Buffer cache hit ratio Latches  Page reads / sec  Latch waits / sec Buffer Partition Locks  Free Pages  Lock requests / sec  Lock waits / sec Cache Manager Memory Manager  Cache Hit Ratio  Everything! Databases Replication  Active Transactions  Depends on configuration  Data File Size  Log Growths SQL Statistics  Percent Log Used  Batch requests / sec  Transactions / sec  SQL compilations / sec 9 GE Title or job number 8/15/2012

SQL Server Activity Monitor Provides a quick overview of database server activity CPU, Processes, Resource Waits and Disk I/O “Recent Expensive Queries” SQL Server Management Studio Reports Quick overview of SQL Server usage Can export to Excel or PDF 10 GE Title or job number 8/15/2012

Server Dashboard Disk Usage Memory Consumption All Transactions Activity – All Block Transactions All Blocking Transactions Activity – Top Sessions Index Usage Statistics Performance – Batch Execution Top Transactions by Age Statistics Schema Changes History Performance – Top Queries by Average CPU Object Execution Statistics Database-Level Reports 11 GE Title or job number 8/15/2012

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 12 GE Title or job number 8/15/2012

Purpose:  Monitoring and troubleshooting  View server state and performance details  Returns relational result sets (use standard SELECT statements) Full list can be viewed in “Views  System Views” section of the properties of the database Scopes:  Server level  Database level 13 GE Title or job number 8/15/2012

Query Execution / Processes •sys.dm_exec_requests Storage Engine sys.DM_DB_File_Space_Usage Indexes sys.DM_DB_Index_Operational_Stats sys.DM_DB_Index_Physical_Stats Disk I/O sys.DM_IO_Pending_IO_Requests sys.DM_IO_Virtual_File_Stats Other Categories Schema information | Common Language Runtime (CLR) Database Mirror / Clustering | Replication 14 GE Title or job number 8/15/2012

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 15 GE Title or job number 8/15/2012

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) 17 GE Title or job number 8/15/2012

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 18 GE Title or job number 8/15/2012

Trace  A set of events, data columns and filters that specify what data should be collected  Data can be saved to a file or a database table Trace File  Trace data that is saved to a binary file  Default extension is “.trc” Trace Table  A SQL Server database table in which trace information is stored  Profiler will automatically create the structure of this table when you start running a new trace Trace Template  Saved specifications that can be used as the basis for new traces  E.g., an environment may have a “Security Monitoring template”, a “CRM Application Performance”, etc.  Default extension is “.tdf” 19 GE Title or job number 8/15/2012

Groupings: Event  Event Categories Categories  Event Classes  Events Examples: Event Classes  TSQL  Stored Procedures  Performance  Errors and Warnings  Security auditing Events 20 GE Title or job number 8/15/2012

Specifies the details to be monitored/recorded Configuring columns  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 21 GE Title or job number 8/15/2012

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) 22 GE Title or job number 8/15/2012

 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 23 GE Title or job number 8/15/2012

Creating new templates using SQL Profiler Scripting trace definitions  sp_trace_create  sp_trace_setfilter  sp_trace_GenerateEvent  sp_trace_SetEvent  sp_trace_SetStatus Extracting SQL Server Events  Transact-SQL Events  ShowPlan Events  Deadlock Events 24 GE Title or job number 8/15/2012

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 25 GE Title or job number 8/15/2012

Reviews sample workloads and makes performance recommendations Evaluates Physical Design Structures (PDS)  Indexes (clustered, non-clustered)  Indexed Views  Partitions Numerous analysis options Output  Generates modification scripts  Generates Reports for later analysis 27 GE Title or job number 8/15/2012

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 28 GE Title or job number 8/15/2012

Limit tuning time Tuning Options  Allowed Physical Design Structures (PDS)  Keep all/specific existing objects  Maximum storage space  Online or offline recommendations  Partitioning 29 GE Title or job number 8/15/2012

Reports can be exported to XML files Report Examples: Workload View-Table Column access Table access analysis Relations Index Usage Statement cost Event frequency (current / recommended) 30 GE Title or job number 8/15/2012

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 31 GE Title or job number 8/15/2012

Create an abstraction layer between the database and the presentation code  Separates presentation and logic (esp. in Web Apps)  Example: ADO.NET Datasets Database design:  Understand typical use-cases before designing the database  Create and enforce naming conventions  Balance write (OLTP) and read (reporting) performance requirements  Use strategic denormalization 33 GE Title or job number 8/15/2012

Never include actions that require user input within a transaction Use connection pooling, whenever possible  Open connections late and close them early  Avoid unnecessary server round-trips Use client-side caching whenever possible  Optimistic concurrency  Pessimistic concurrency Distribute processing  Some operations are more efficient on the DB server (e.g., aggregations, sorting, etc.) 34 GE Title or job number 8/15/2012

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 36 GE Title or job number 8/15/2012

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 37 GE Title or job number 8/15/2012

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) 38 GE Title or job number 8/15/2012

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 39 GE Title or job number 8/15/2012

Blocking  When transaction(s) must wait for a lock on a resource  LOCK_TIMEOUT setting (default = wait forever) Locking Models:  Pessimistic  Optimistic 40 GE Title or job number 8/15/2012

Balance of concurrency (performance) vs. consistency  Affects SELECT queries  SET TRANSACTION ISOLATION LEVEL Transaction Isolation Levels  READ UNCOMMITTED  READ COMMITTED (default)  REPEATABLE READ  SERIALIZABLE  SNAPSHOT Row-Versioning:  ALLOW_SNAPSHOT_ISOLATION  READ_COMMITTED_SNAPSHOT 41 GE Title or job number 8/15/2012

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 42 GE Title or job number 8/15/2012

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 43 GE Title or job number 8/15/2012

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 44 GE Title or job number 8/15/2012

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 45 GE Title or job number 8/15/2012

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) 46 GE Title or job number 8/15/2012

SQL Server Performance Tuning Baseline

SQL Server Performance Tuning Baseline

More Related Content

What's hot

Viewers also liked

Similar to SQL Server Performance Tuning Baseline

More from ► Supreme Mandal ◄

Recently uploaded

In this document

SQL Server Performance Tuning Baseline