monitoring and optimizing sql server 2005 performance
Download
Skip this Video
Download Presentation
Monitoring and Optimizing SQL Server 2005 Performance

Loading in 2 Seconds...

play fullscreen
1 / 48

Monitoring and Optimizing SQL Server 2005 Performance - PowerPoint PPT Presentation


  • 94 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