sql server 2005 performance diagnosis and tuning using sql

Enterprise Database Administration & Deployment SIG ▪ 313M ▪ Sept 29, 2005 ▪ 10:15 AM

SQL Server 2005 SQL Server 2005 Performance Diagnosis and Performance Diagnosis and Tuning using SQL Tools and Tuning using SQL Tools and DMVs DMVs

Shawn NesserShawn Nesser

(Borrowed from Vipul Shah)(Borrowed from Vipul Shah)


Session Objectives And Session Objectives And Key TakeawaysKey Takeaways

Session Objectives: Session Objectives: How Tools and DMVs can be used to monitor How Tools and DMVs can be used to monitor and tune SQL Server 2005and tune SQL Server 2005

Walk away with demo scripts you can useWalk away with demo scripts you can use

Key Takeaways:Key Takeaways:Maximize benefit from Tools and DMVs for Maximize benefit from Tools and DMVs for monitoring and tuning SQL Server 2005monitoring and tuning SQL Server 2005


AgendaAgenda

New Capabilities in SQL Server 2005New Capabilities in SQL Server 2005

Management Studio ReportsManagement Studio Reports

Identify Top SQL (delta)Identify Top SQL (delta)

I/O TuningI/O Tuning

Contention TuningContention Tuning

SQL Server ProfilerSQL Server Profiler

Demo Code is at end of slides (see Notes)Demo Code is at end of slides (see Notes)


New Capabilities in SQL New Capabilities in SQL 2005?2005?

Dynamic Management Views (DMVs)Dynamic Management Views (DMVs)Views on memory structures in the EngineViews on memory structures in the Engine

Monitor activity inside the EngineMonitor activity inside the Engine

Trace EventsTrace EventsNew trace events that complete the picture New trace events that complete the picture provided by DMVsprovided by DMVs

Light-weight, ON-by-default trace to capture Light-weight, ON-by-default trace to capture schema and configuration changesschema and configuration changes

Dedicated Administration ConnectionDedicated Administration Connection


New Capabilities in SQL New Capabilities in SQL 2005? (continued)2005? (continued)

Database Engine Tuning AdvisorDatabase Engine Tuning AdvisorGives Partitions recommendations Gives Partitions recommendations

Indexes and Indexed Views recommendationsIndexes and Indexed Views recommendations

Statistics recommendationsStatistics recommendations

Tunes multiple databasesTunes multiple databases

Time-bound and Storage-bound tuningTime-bound and Storage-bound tuning

XML ShowPlan XML ShowPlan MissingIndexes tagMissingIndexes tag

Indexes that query optimizer would use if availableIndexes that query optimizer would use if available


New Capabilities in SQL New Capabilities in SQL 2005? (continued)2005? (continued)

Management Studio contains performance Management Studio contains performance reportsreports

Reports are available at server levelReports are available at server level

Reports are available at database levelReports are available at database level

SQL Server Profiler allows for correlation SQL Server Profiler allows for correlation with Performance Counterswith Performance Counters

Enhances ability to isolate problemsEnhances ability to isolate problems


Database Management Database Management ViewsViewsViews built on top of internal structuresViews built on top of internal structures

Ideal for tracking performanceIdeal for tracking performance

dm_repl_* Replication

dm_broker_* SQL Service Broker

dm_fts_* Full Text Search

dm_qn_* Query Notifications

dm_clr_* Common Language Runtime

dm_exec_* Execution of user code and associated

connections

dm_os_* Memory, locking & scheduling

dm_tran_* Transactions & isolation

dm_io_* I/O on network and disks

dm_db_* Databases and database objects

Component LevelServer Level


Default TraceDefault TraceLight-weight tracing is ON by defaultLight-weight tracing is ON by default

Captures schema changes and Captures schema changes and configuration changesconfiguration changes

Trace is captured in rolling window of 5 Trace is captured in rolling window of 5 trace files 20MB each (maximum of trace files 20MB each (maximum of 100MB)100MB)

Old trace files are deletedOld trace files are deleted

100MB limit is never exceeded100MB limit is never exceeded

New trace file is created after every re-startNew trace file is created after every re-start

No more than 5 trace files are availableNo more than 5 trace files are available


Management Studio ReportsManagement Studio Reports

Reports get data from DMVs and Default Reports get data from DMVs and Default Trace filesTrace files

Use these Reports for an overviewUse these Reports for an overview

Server-level ReportsServer-level ReportsSelect SQL Server instance node in Object Select SQL Server instance node in Object Explorer, select ViewExplorer, select ViewSummary menu, and Summary menu, and Report drop-listReport drop-list

Database-level ReportsDatabase-level ReportsSelect a database node in Object Explorer, Select a database node in Object Explorer, select Viewselect ViewSummary menu, and Report Summary menu, and Report drop-listdrop-list

Management Studio Management Studio ReportsReports

Vipul ShahVipul ShahProgram ManagerProgram ManagerSQL ServerSQL Server


Tracking Server ActivityTracking Server ActivityTrack activity by Wait typesTrack activity by Wait types

Waits indicate where server spends its activityWaits indicate where server spends its activity

Focus on wait event with highest wait timeFocus on wait event with highest wait time

Track SQL statementsTrack SQL statements

How do you track Top SQL and Waits?How do you track Top SQL and Waits?““Performance – Top Queries” report in Performance – Top Queries” report in Management Studio shows you top queries Management Studio shows you top queries since instance start timesince instance start time

Use DMVs to track activity in an intervalUse DMVs to track activity in an intervalQueryStatsDelta script is an exampleQueryStatsDelta script is an example


Tracking Server ActivityTracking Server Activity(continued)(continued)

““QueryStatsDelta” scriptQueryStatsDelta” scriptUses dm_exec_query_statsUses dm_exec_query_stats

Takes before and after snapshots and diffs themTakes before and after snapshots and diffs them

Accounts for new statements in the intervalAccounts for new statements in the interval

Orders top SQL by elapsed timeOrders top SQL by elapsed time

Shows exact SQL statement by extracting it from batch Shows exact SQL statement by extracting it from batch texttext

Uses dm_os_wait_statsUses dm_os_wait_statsTakes before and after snapshots and diffs themTakes before and after snapshots and diffs them

Removes Idle waits and Trace waitsRemoves Idle waits and Trace waits

Orders top wait events by wait durationOrders top wait events by wait duration

Identify Top SQLIdentify Top SQL



Tuning I/O Intensive Tuning I/O Intensive SQLSQL

Use “MissingIndexes” tag in XML Show Plan Use “MissingIndexes” tag in XML Show Plan for single statement-focused analysisfor single statement-focused analysis

Very useful when workload is not available or Very useful when workload is not available or workload is incompleteworkload is incomplete

Missing indexes is NOT a recommendationMissing indexes is NOT a recommendation

Optimizer would have used these indexes if Optimizer would have used these indexes if availableavailable

Remember – creating these indexes may slow Remember – creating these indexes may slow Inserts, Updates and DeletesInserts, Updates and Deletes

Track index usage if you choose to create a missing indexTrack index usage if you choose to create a missing index

Weight the costs-benefits of the indexWeight the costs-benefits of the index


Tuning I/O Intensive SQL Tuning I/O Intensive SQL (continued)(continued)

Use Database Engine Tuning Advisor for Use Database Engine Tuning Advisor for comprehensive workload analysiscomprehensive workload analysis

Feed workload input to DTAFeed workload input to DTACollect workload using “Tuning” template in SQL Server Collect workload using “Tuning” template in SQL Server Profiler, orProfiler, or

Collect workload as SQL scriptCollect workload as SQL script

Select the database for workload analysisSelect the database for workload analysis

Select the databases to tuneSelect the databases to tune

Set a time-boundSet a time-bound

Review output from analysisReview output from analysis

Review reports to make educated choiceReview reports to make educated choice

I/O TuningI/O Tuning



How do I track index How do I track index usage?usage?How do I track index usage?How do I track index usage?

Use dm_db_missing_index_group_stats, Use dm_db_missing_index_group_stats, dm_db_missing_index_groups, dm_db_missing_index_groups, dm_db_missing_indexesdm_db_missing_indexes

Identify frequently used and less frequently Identify frequently used and less frequently used indexesused indexes

Do not drop index without careful Do not drop index without careful consideration !consideration !


Detecting ContentionDetecting Contention““Activity – All Blocking Transactions” report Activity – All Blocking Transactions” report in Management Studio is excellent for in Management Studio is excellent for seeing blocked transactions at that seeing blocked transactions at that momentmoment

““Blocked Process Report” trace eventBlocked Process Report” trace eventTo set 5 second blocked process reportTo set 5 second blocked process report

sp_configure 'blocked process threshold', sp_configure 'blocked process threshold', 55

““Deadlock Graph” trace eventDeadlock Graph” trace event

DMV dm_os_wait_stats shows aggregate DMV dm_os_wait_stats shows aggregate duration spent on lock waitsduration spent on lock waits


Tuning ContentionTuning ContentionSQL Server 2000 – tradeoff between SQL Server 2000 – tradeoff between consistency and concurrencyconsistency and concurrency

SQL Server 2005 - use Row-Versioning SQL Server 2005 - use Row-Versioning based isolation levelsbased isolation levels

Eliminates lockingEliminates locking

Uses version store to keep old row versionsUses version store to keep old row versions

No tradeoff between consistency and No tradeoff between consistency and concurrencyconcurrency

Read Committed Snapshot isolation levelRead Committed Snapshot isolation levelProvides statement level consistencyProvides statement level consistency

Snapshot isolation levelSnapshot isolation levelProvides transaction level consistencyProvides transaction level consistency


Tuning Contention (continued)Tuning Contention (continued)

Use Read Committed Snapshot isolation Use Read Committed Snapshot isolation levellevel

Most existing applications require no changeMost existing applications require no change

OFF by defaultOFF by defaultTurn it ON per databaseTurn it ON per database

Works with distributed transactionsWorks with distributed transactions

Detecting Contention and Detecting Contention and DeadlocksDeadlocks



Performance Correlation in Performance Correlation in ProfilerProfiler

SQL Server ProfilerSQL Server ProfilerUse to Trace eventsUse to Trace events

Use to Replay eventsUse to Replay events

Correlate Performance Counters to isolate Correlate Performance Counters to isolate problems quicklyproblems quickly

Performance Counter Performance Counter Correlation in ProfilerCorrelation in Profiler



Thank you!Thank you for attending this session and the 2005 PASS

Community Summit in Grapevine! Please help us improve the quality of our conference by completing your

session evaluation form. Completed evaluation forms may be given to the room monitor as you exit or to staff at the

registration desk.

Create TablesCreate Tables

Requires AdventureWorks databaseRequires AdventureWorks database Script is in Notes sectionScript is in Notes section

Scripts

Create Scheduled JobCreate Scheduled Job

Scripts


Create Stored ProceduresCreate Stored Procedures

Scripts

Requires AdventureWorks databaseRequires AdventureWorks database Creates SPs and Functions for TopSQL and TopWaitsCreates SPs and Functions for TopSQL and TopWaits Script is in Notes sectionScript is in Notes section

Workload Without DMLWorkload Without DML

Scripts


Workload With DMLWorkload With DML

Scripts


Demo QueriesDemo Queries

Scripts


ReaderReader

Scripts

Requires Snapshots databaseRequires Snapshots database Script is in Notes sectionScript is in Notes section

WriterWriter

Scripts

Requires Snapshots databaseRequires Snapshots database Script is in Notes sectionScript is in Notes section

First Part of DeadlockFirst Part of Deadlock

Scripts


Second Part of DeadlockSecond Part of Deadlock

Scripts


sql server 2005 performance diagnosis and tuning using sql

Documents