Monitoring and optimizing sql server 2005 performance
Download
1 / 48

Monitoring and Optimizing SQL Server 2005 Performance - PowerPoint PPT Presentation


  • 92 Views
  • Uploaded on

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)

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' Monitoring and Optimizing SQL Server 2005 Performance' - honorato-cunningham


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

Speaker information
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 [email protected]


Agenda and overview
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


Performance monitoring approaches
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


  • Overview of performance monitoring
    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


    Understanding 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

    • Examples:

      • Generate a list of the 100 slowest queries

      • Monitor all failed logins (Security)


    Sql server profiler architecture
    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


    Configuring trace events
    Configuring Trace Events

    • Groupings:

      • Event Categories

      • Event Classes

      • Events

    • Examples:

      • TSQL

      • Stored Procedures

      • Performance

      • Errors and Warnings

      • Security auditing


    Configuring trace columns
    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


    Trace output options
    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)


    Creating profiler traces
    Creating Profiler Traces

    • Launching SQL Profiler

    • Connecting to a database instance

    • Configuring output options

    • Create a trace definition

    • Specifying events, columns, and filters


    Other sql profiler options
    Other SQL Profiler Options

    • Creating new templates

    • Scripting trace definitions

    • Extracting SQL Server Events

      • Transact-SQL Events

      • ShowPlan Events

      • Deadlock Events


    Using system monitor with sql profiler
    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


    Windows performance monitor
    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


    Using dynamic management views dmvs
    Using Dynamic Management Views (DMVs)

    • Purpose:

      • Monitoring and troubleshooting

      • View server state and performance details

      • Returns relational result sets

    • Scopes:

      • Server level

      • Database level


    Dmv examples
    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


    Sql server management studio reports
    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


    Database level reports
    Database-Level Reports

    • Examples:

      • Disk Usage

      • All Transactions

      • All Blocking Transactions

      • Index Usage Statistics

      • Top Transactions by Age

      • Schema Changes History


    Monitoring sql server logs
    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


    Using sqldiag
    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


    Database engine tuning advisor
    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


    Workloads
    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


    Dta options
    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


    Dta reports
    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)


    Using the database engine tuning advisor
    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


    Understanding processes
    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


    Monitoring processes
    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


    Managing processes
    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)


    Understanding locking
    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


    Understanding blocking
    Understanding Blocking

    • Blocking

      • When transaction(s) must wait for a lock on a resource

      • LOCK_TIMEOUT setting (default = wait forever)

    • Locking Models:

      • Pessimistic

      • Optimistic


    Transaction isolation levels
    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


    Monitoring locking activity
    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


    Understanding the deadlock process
    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


    Avoiding deadlocks
    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


    Deadlock victims
    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


    Monitoring deadlocks
    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)



    Understanding partitions
    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


    Benefits of partitioning
    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)


    Designing partitions
    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


    Implementing partitions
    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


    Creating a partition function
    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


    Creating a partition scheme
    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)


    Creating partitioned tables
    Creating Partitioned Tables

    • Specify the partition scheme when creating a table

      • CREATE TABLE … ON PartitionScheme

      • Data storage will be based on the partition scheme


    Creating partitioned indexes
    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


    Managing partitioned data
    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


    Working with partitioned data
    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


    For more information
    For More Information

    • Resources from Anil Desai

      • Web Site (http://AnilDesai.net)

      • E-Mail: [email protected]

      • 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


    ad