Performance  Monitoring & Optimization Steps

               Agenda  and Overview

• Performance Monitoring Overview

• Monitoring Database Servers

• Using SQL Profiler

• Using the Database Engine Tuning Advisor

• Application Design Tips

• Managing Processes, Locking, and Deadlocks

Windows Performance Monitor 

Using SQL Server tools and features to monitor database activity     

Available in all current versions of Windows 

• Statistics are organized into: 

  • Objects 

  • Counters 

  • Instances 

• Data Collector Sets 

  • Windows Vista / Windows 7 / Windows Server 2008 R2 

  • Used to report on performance data that is collected over time 

  • Includes built-in System Diagnostics and System Performance collectors and reports 

      SQL Server Performance Counter

Backup Device

• Device throughput Bytes/sec

Buffer Manager

• Buffer cache hit ratio

• Page reads / sec

Buffer Partition

• Free Pages

Cache Manager

• Cache Hit Ratio

Databases

• • Active Transactions

  • Data File Size

  • Log Growths

  • Percent Log Used

  • Transactions / sec

General Statistics

• • Logins | logouts / sec 

  • User Connections 

Latches

• Latch waits / sec 

Locks

• • Lock requests / sec 

  • Lock waits / sec 

Memory Manager

• • Everything! 

Replication

• Depends on configuration 

SQL Statistics

• Batch requests / sec 

• SQL compilations / sec 

  Database-Level Reports 

• }        Disk Usage

• }        All Transactions

• }        All Blocking Transactions

• }        Index Usage Statistics

• }        Top Transactions by Age

• }        Schema Changes History

Server -Level Reports

• }        Server Dashboard

• }        Memory Consumption

• }        Activity – All Block Transactions

• }        Activity – Top Sessions

• }        Performance – Batch Execution Statistics

• }        Performance – Top Queries by Average CPU

• }        Object Execution Statistics

 Using SQL Profiler

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 

  • SQL Server 2008 Profiler offers 180 different events it can monitor. For each event, it can also collect up to 64 different types of data.

Examples: 

     Generate a list of the 100 slowest queries 

      Monitor all failed logins (Security) 

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 

Configuration Trace Events

Groupings:

• • Event Categories  

  • Event Classes 

  • Events 

Examples:

• • TSQL 

  • Stored Procedures 

  • Performance 

  • Errors and Warnings 

  • Security auditing 

Configuration Trace Columns

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

Other SQL Profiler Options

Creating new templates using SQL ProfilerScripting 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