Practical SQL Server Performance Monitoring & Optimizat - PowerPoint Presentation

Slide1

Practical SQL Server Performance Monitoring & Optimization

Anil

Desai

http://AnilDesai.net

Austin

CodeCamp

2010Slide2

Anil DesaiIndependent consultant (Austin, TX)

Author of

numerous IT booksInstructor, “Implementing and Managing SQL Server 2005” (Keystone Learning)Info: http://AnilDesai.net or Anil@AnilDesai.net

Speaker InformationSlide3

Performance Monitoring Overview

Monitoring Database Servers

Using SQL Profiler

Using the Database Engine Tuning Advisor

Application Design Tips

Managing Processes, Locking, and DeadlocksAgenda and OverviewSlide4

Performance Monitoring Overview

Developing processes and approaches for performance optimizationSlide5

Best Practices:

Optimize for real-world workloads

Monitor/review performance regularly

Focus on specific issues

Performance Monitoring ApproachesSlide6

Overview of Performance

Monitoring Tools and MethodsSlide7

Monitoring and Troubleshooting ScenariosSlide8

Monitoring SQL Server

Using SQL Server tools and features to monitor database activitySlide9

Available in all current versions of WindowsStatistics are organized into:

Objects

CountersInstancesData Collector SetsWindows Vista / Windows 7 / Windows Server 2008Used to report on performance data that is collected over timeIncludes built-in System Diagnostics and System Performance collectors and reports

Windows Performance MonitorSlide10

Backup Device

Device throughput Bytes/sec

Buffer ManagerBuffer cache hit ratioPage reads / secBuffer Partition

Free PagesCache Manager

Cache Hit RatioDatabasesActive TransactionsData File SizeLog Growths

Percent Log UsedTransactions / sec

Anil Desai10

Useful SQL Server Performance Counters

General StatisticsLogins | logouts / secUser ConnectionsLatches

Latch waits / sec

Locks

Lock requests / sec

Lock waits / sec

Memory Manager

Everything!

Replication

Depends on configuration

SQL Statistics

Batch requests / sec

SQL compilations / secSlide11

SQL Server Activity MonitorProvides a quick overview of database server activity

CPU, Processes, Resource Waits and Disk I/O

“Recent Expensive Queries”SQL Server Management Studio ReportsQuick overview of SQL Server usageCan export to Excel or PDF

SQL Server Management Studio ReportsSlide12

SQL Server Report Examples

Server-Level Reports

Database-Level Reports

Server DashboardMemory ConsumptionActivity – All Block Transactions

Activity – Top SessionsPerformance – Batch Execution StatisticsPerformance – Top Queries by Average CPUObject Execution Statistics

Disk UsageAll TransactionsAll Blocking TransactionsIndex Usage StatisticsTop Transactions by AgeSchema Changes HistorySlide13

Windows Event Logs / Event Viewer Application and System Event Logs

SQL Server Management Studio

SQL Server LogsCan configure max. # of log filesSQL Server Agent Error logsCan configure logging levels (Errors, Warnings, Information)Using the Log File ViewerCan Export / Load log information

Can search for specific errors/messages

Monitoring SQL Server LogsSlide14

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 databaseScopes:

Server levelDatabase level

Using Dynamic Management Views (DMVs)Slide15

DMV ExamplesSlide16

Data Collected:

System Information (MSINFO)

Windows Event LogsSQL Server configurationCommand-Line Utility (SQLDiag.exe)Stores output to files

Configuration file: SQLDiag.xmlCan run as a service (/R)

Can run in continuous modeUsing SQLDiagSlide17

Using SQL Profiler

Monitoring SQL Server ActivitySlide18

Purpose / Features: GUI for managing SQL Trace

Monitor important events

Capture performance data / resource usageReplaying of workloads / transactionsIdentifying performance bottlenecksCorrelation of data with System MonitorWorkloads for Database Tuning AdvisorExamples:

Generate a list of the 100 slowest queriesMonitor all failed logins (Security)

Understanding SQL ProfilerSlide19

SQL Profiler Terminology

Trace Definitions

EventsColumnsFiltersCreating and Managing SQL TracesSQL Profiler (GUI)System Stored Procedures (Transact-SQL)Trace Templates (Built-In)

Standard (Default), SP_CountsTSQL,

TSQL_Duration, TSQL_Grouped,TSQL_Replay, TSQL_SPsTuning

SQL Server Profiler ArchitectureSlide20

Anil Desai

20

SQL Profiler Terminology

Trace

A set of events, data columns and filters that specify what data should be collectedData can be saved to a file or a database tableTrace FileTrace data that is saved to a binary file

Default extension is “.trc”Trace TableA SQL Server database table in which trace information is storedProfiler will automatically create the structure of this table when you start running a new trace

Trace TemplateSaved specifications that can be used as the basis for new tracesE.g., an environment may have a “Security Monitoring template”, a “CRM Application Performance”, etc.

Default extension is “.tdf”Slide21

Groupings:

Event Categories

Event ClassesEventsExamples:TSQLStored Procedures

PerformanceErrors and Warnings

Security auditingConfiguring Trace EventsSlide22

Specifies the details to

be monitored/recorded

Configuring columnsColumns can be ordered and groupedValues can be filteredExamples of Columns:

StartTime / EndTime

TextDataDurationResource Usage (CPU, Reads, Writes)Information: User, Database, App. Names

Configuring Trace ColumnsSlide23

Interactive

Good for “live” monitoring of small sets of data

Trace Files (*.trc)Can enable file rollover based on size“Server processes trace data” optionTrace tableWill automatically create the table

Can set maximum number of rowsScheduling of traces (stop time)

Trace Output OptionsSlide24

Launching SQL ProfilerConnecting to a database instanceConfiguring output options

Create a trace definition

Specifying events, columns, and filtersRunning and viewing a traceDemo: Creating Profiler TracesSlide25

Creating new templates using SQL Profiler

Scripting

trace definitionssp_trace_createsp_trace_setfilter

sp_trace_GenerateEvent

sp_trace_SetEventsp_trace_SetStatusExtracting SQL Server EventsTransact-SQL Events

ShowPlan EventsDeadlock EventsOther SQL Profiler OptionsSlide26

Purpose / Goal:

Correlate server performance with database performance

Process:Define and start a counter logDefine and start a SQL Profiler traceImport Performance Data in SQL ProfilerRequired Trace properties

StartTimeEndTime

Using System Monitor with SQL ProfilerSlide27

Using the Database Engine Tuning Advisor

Analyzing workloads to optimize physical database structuresSlide28

Reviews sample workloads and makes performance recommendationsEvaluates Physical Design Structures (PDS)

Indexes (clustered, non-clustered)

Indexed ViewsPartitionsNumerous analysis optionsOutputGenerates modification scriptsGenerates Reports for later analysis

Database Engine Tuning AdvisorSlide29

FilesTransact-SQL Files

XML Files

Should represent commonly-used queriesSQL Profiler Trace Files / TablesUse Tuning built-in trace templateEvents: Transact-SQL BatchRemote Procedure Call (RPC)Columns: Event Class and Text Data

Workload SourcesSlide30

Limit tuning time Tuning OptionsAllowed Physical Design Structures (PDS)

Keep all/specific existing objects

Maximum storage spaceOnline or offline recommendationsPartitioningDTA Execution OptionsSlide31

Reports can be exported to XML files

Report Examples:

DTA ReportsSlide32

Process:

Generate a workload (file or table)

Select tuning optionsRun the analysisView reportsSave and/or apply recommendations

Running the DTA:Database Engine Tuning Advisor Application (GUI)

Dta.exe command-line utilityUsing the Database Engine Tuning AdvisorSlide33

Application Design Tips

Practical ways to improve database application performanceSlide34

Create an abstraction layer between the database and the presentation code

Separates presentation and logic (esp. in Web Apps)

Example: ADO.NET DatasetsDatabase design:Understand typical use-cases before designing the databaseCreate and enforce naming conventionsBalance write (OLTP) and read (reporting) performance requirements

Use strategic denormalization

Anil Desai34

Application Design TipsSlide35

Never include actions that require user input within a transactionUse connection pooling, whenever possible

Open connections late and close them early

Avoid unnecessary server round-tripsUse client-side caching whenever possibleOptimistic concurrencyPessimistic concurrencyDistribute processingSome operations are more efficient on the DB server (e.g., aggregations, sorting, etc.)

Application Design Tips (cont’d.)Slide36

Managing Processes, Locking, and Deadlocks

Troubleshooting common SQL Server performance problemsSlide37

ProcessesInteractive users

SQL Server Management Studio

Applications (Connection Pooling)SQL ProfilerDatabase Engine Tuning AdvisorReplicationService BrokerProcess IDs < 50 are system-related

Understanding ProcessesSlide38

SQL Server Activity Monitor

Processes (connected users)

Locks (by Process / by Object)Filtering optionsAuto-refresh optionSystem Stored Procedures / ViewsSys.DM_Exec_Sessions

Sys.DM_Exec_Requests

Sys.SysProcessessp_who / sp_who2Monitoring ProcessesSlide39

Process InformationCurrent Process ID:

@@SPID

Session Options: DBCC USEROPTIONSKilling ProcessesKILL ProcessID [WITH STATUSONLY]Viewing Last Activity

DBCC INPUTBUFFER(ProcessID)DBCC OUTPUTBUFFER(ProcessID)

Managing ProcessesSlide40

Coordinates multiple accesses to the same dataEnsures 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

Understanding LockingSlide41

BlockingWhen transaction(s) must wait for a lock on a resource

LOCK_TIMEOUT setting (default = wait forever)

Locking Models:PessimisticOptimisticUnderstanding BlockingSlide42

Balance of concurrency (performance) vs. consistency

Affects SELECT queries

SET TRANSACTION ISOLATION LEVELTransaction Isolation LevelsREAD UNCOMMITTED

READ COMMITTED (default)

REPEATABLE READSERIALIZABLESNAPSHOT

Row-Versioning:ALLOW_SNAPSHOT_ISOLATION READ_COMMITTED_SNAPSHOT

Transaction Isolation LevelsSlide43

Activity Monitor

SQL Profiler

Locks Event CategorySystem Monitor: SQL Server  Locks ObjectSystem Views

Sys.DM_Tran_LocksSys.DM_Exec_Requests

System Stored Proceduressp_LockMonitoring Locking ActivitySlide44

Deadlocks:

Two or more tasks permanently block each other based on resource locks

Default resolution is within 5 secondsDeadlock victim Transaction is rolled-backProcess receives a 1205 errorExample:

Process 1 locks the Customers table and requires access to the

Orders TableProcess 2 locks the Orders table and requires access to the Customers Table

Understanding the Deadlock ProcessSlide45

Minimize transaction timesCommit / Rollback transactions as quickly as possibly

Avoid user-related time within a transaction

Access objects in a consistent orderChange the transaction isolation levelUse a lower level isolation level, if appropriateUse snapshot-based isolation levels

Avoiding DeadlocksSlide46

Deadlock priorities:SET DEADLOCK_PRIORITY (LOW, NORMAL, HIGH,

integer

)Deadlock resolution:Lower priority is killed firstIf equal priorities, least expensive transaction becomes the victimApplication or user should attempt to re-run the transaction

Deadlock VictimsSlide47

SQL Server Error Log

SQL Profiler

Locks Event CategoryLock:Deadlock ChainLock:DeadlockDeadlock GraphEvents Extraction Trace Property

Export deadlock XML (.xdl) fileViewing Deadlock Files

SQL Server Management Studio (File  Open  SQL Deadlock Files (*.xdl)Monitoring DeadlocksSlide48

Deadlock GraphSlide49

Questions & Discussion

For more information:

http://AnilDesai.net Anil@AnilDesai.net