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
8/15/2012

3
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
8/15/2012

47
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