Slide1 l.jpg
This presentation is the property of its rightful owner.
Sponsored Links
1 / 36

Platinum PowerPoint PPT Presentation

  • Uploaded on
  • Presentation posted in: General

Platinum. Learn & Enjoy [Put your phone on Vibrate!]. Group BY: [Food and Drink at Reading Bowl, see you there!]. Gold. Feedback Forms: [Voucher for £30 book on return of Form]. Silver. Lunch Time Sessions: [Idera in Everest, Quest in Memphis, Grok in Chic 1 and 2].

Download Presentation


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

Slide1 l.jpg


Learn & Enjoy [Put your phone on Vibrate!]

Group BY: [Food and Drink at Reading Bowl, see you there!]


Feedback Forms: [Voucher for £30 book on return of Form]


Lunch Time Sessions: [Idera in Everest, Quest in Memphis, Grok in Chic 1 and 2]

Ask The Experts[Sessions need to finish on time, take questions to the ATE area]

Identifying performance problems with sql profiler l.jpg

Identifying Performance Problems with SQL Profiler

Martin Bell SQL Server MVP

Introduction l.jpg


  • SQL Profiler is the most important tool for diagnosing poorly performing SQL in a system

  • SQL 2005 has improved SQL Profiler to make it even more useful

  • Combined with other tools you can build a complete performance monitoring toolkit

Perfmon counters l.jpg

Perfmon Counters

  • New counters covers areas including CLR information, Service Broker, Notification Services

  • sys.dm_os_performance_counters and sys.sysperfinfo

  • Import perfmon data into SQL Profiler

Server dashboard l.jpg

Server Dashboard

  • Perfmon reports release with SP1

  • Performance at a glance, based on dynamic views

    • sys.dm_os_performance_counters

    • sys.dm_exec_query_stats

Performance dashboard custom reports l.jpg

Performance Dashboard Custom Reports

  • Download from

  • Requires SP2

  • Common performance problems that the dashboard reports may help to resolve include:- CPU bottlenecks (and what queries are consuming the most CPU)- IO bottlenecks (and what queries are performing the most IO).- Index recommendations generated by the query optimizer (missing indexes)- Blocking- Latch contention

Sql profiler l.jpg

SQL Profiler

  • Improved interface for creating a profile

  • Added filtering capability

  • Deadlock Graphs

  • Showplan XML

  • Re-configure trace without loosing data

  • Include Perfmon Data

Deadlock graphs l.jpg

Deadlock Graphs

  • Better detection of deadlocks

  • No need for blocker scripts or other methods

  • Easily identify blocking problems

Slide9 l.jpg

-- Script 1






SELECT * FROM AUTHORS WITH (UPDLOCK) -- Results Returned immediately

WAITFOR DELAY '000:00:03'

SELECT * FROM TITLES WITH (UPDLOCK) -- Results Returned immediately



Slide10 l.jpg

-- Script 2






SELECT * FROM TITLES WITH (UPDLOCK) -- Results Returned immediately

WAITFOR DELAY '000:00:03'

SELECT * FROM AUTHORS WITH (UPDLOCK) -- Results Not Returned Immediately



Slide11 l.jpg

Msg 1205, Level 13, State 51, Line 8

Transaction (Process ID 57) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Define profiler templates l.jpg

Define Profiler Templates

  • Save your own templates if you frequently require to profile certain events/columns

Changing events without loosing data l.jpg

Changing Events without loosing Data

  • Unlike SQL 2000 you can pause the SQL profile, change what is being logged and then resume the profile without loosing the previous data

Xml showplan l.jpg

XML Showplan

  • This will help to diagnose query problems, less need to cut/paste into Management Studio

  • Maintain old query plans and able to compare current plans to older ones

Importing perfmon data l.jpg

Importing Perfmon Data

  • If you have collected perfmon counters while a trace is running they can be imported into the SQL profiler

  • Better collect statement level information

    • sp:StatementStart

    • sp:StatementComplete

  • Do not trace a high number of events for a long period or you may fill up the file system!

Gotchas l.jpg


  • Using SQL Profiler on database server can take resources

  • Uses temp directory to storage – make sure this is not on the system disc!

Server side traces l.jpg

Server Side Traces

  • Script from SQL Profiler

  • Schedule from SQL Agent

  • Specify a duration

  • Automate the collection and analysis

  • Output to a file on different spindles!

Running traces l.jpg

Running traces

  • Use fn_trace_setstatus for the given trace id to change the status

    • 0 stops the trace

    • 1starts the trace

    • 2closes the trace

Show running traces l.jpg

Show running traces

  • Use fn_trace_getinfo to return information on the traces loaded and running. Property column:

    • 1Trace options.

      • 2 – Trace file rolls over

      • 4 – Shutdown trace on error

      • 8 – Produce a Blackbox trace

  • 2File name

  • 3Max size

  • 4Stop time

  • 5Current trace status

  • Loading your own traces l.jpg

    Loading your own traces

    If you wish to load your own traces into SQL Profiler has the option to save a loaded trace file as a trace table, or you can use the function fn_trace_gettable e.g.

    SELECT IDENTITY(bigint, 1, 1) AS RowNumber, *

    INTO dbo.trc_20061206_1

    FROM ::fn_trace_gettable('c:\trc_20061206_1.trc', default)

    Analysing the results l.jpg

    Analysing the results

    • Read80Trace (SQL Server 2000)

    • Manual Loading and Analysis

    • ClearTrace utility

    Manual analysis l.jpg

    Manual Analysis

    SELECT LEFT(CAST(TextData AS VARCHAR(8000)),25) AS [Procedure],

    COUNT(*) AS [Number of Calls],

    SUM([Duration]) AS [Total Duration],

    AVG([Duration]) AS [Average Duration],

    SUM([Reads]) AS [Total Reads],

    AVG([Reads]) AS [Average Reads],

    SUM([Writes]) AS [Total Write],

    AVG([Writes]) AS [Average Write],

    SUM([CPU]) AS [Total CPU],

    AVG([CPU]) AS [Average CPU]

    FROM dbo.trc_200612031629

    GROUP BY LEFT(CAST(TextData AS VARCHAR(8000)),25)

    ORDER BY SUM([Duration]) DESC

    Procedure analysis l.jpg

    Procedure Analysis

    CREATE VIEW dbo.vw_trc_200612031629



    CHARINDEX('usp', CAST(textdata as VARCHAR(8000)) ),

    CASE WHEN CHARINDEX(' ',CAST(textdata as VARCHAR(8000)), CHARINDEX(‘usp', CAST(textdata as VARCHAR(8000)) ) ) > 0 THEN

    CHARINDEX(' ',CAST(textdata as VARCHAR(8000)), CHARINDEX(‘usp', CAST(textdata as VARCHAR(8000)) ) )

    - CHARINDEX(‘usp', CAST(textdata as VARCHAR(8000)) )

    ELSE LEN(CAST(textdata as VARCHAR(8000))) END

    ),60) as [Procedure],





    FROM dbo.trc_200612031629

    WHERE CHARINDEX(‘usp', CAST(textdata as VARCHAR(8000))) > 0

    Procedure analysis25 l.jpg

    Procedure Analysis

    SELECT [Procedure],

    COUNT(*) AS [Number of Calls],

    SUM([Duration]) AS [Total Duration],

    AVG([Duration]) AS [Average Duration],

    SUM([Reads]) AS [Total Reads],

    AVG([Reads]) AS [Average Reads],

    SUM([Writes]) AS [Total Write],

    AVG([Writes]) AS [Average Write],

    SUM([CPU]) AS [Total CPU],

    AVG([CPU]) AS [Average CPU]

    FROM dbo.vw_trc_200612031629

    GROUP BY [Procedure]

    ORDER BY SUM([Duration]) DESC

    Cleaning data l.jpg

    Cleaning Data

    • You may also want to clean up the data, this is usually easier if you are using stored procedure as you should not need to use sp_prepare

    • Andrew Zanevsky wrote an interesting article on Trace Scrubbing article from SQL Server Professional in SQL Server Professional magazine

    Cleartrace l.jpg


    • Written by SQL Server MVP - Bill Graziano

    • Download from:

    Replaying traces l.jpg

    Replaying Traces

    • This can be very useful if you do not have the facilities to use a tool such as Visual Team Test or you only want to stress the database and not the GUI

    • Use standard Replay Template

    • Replay from file or table (but can not load file using fn_trace_gettable

    • Can manipulate trace data in table and export it

    Limitations of replaying traces l.jpg

    Limitations of Replaying Traces

    • Not imitating users therefore stress is different to live system

    • May not replay correctly (see considerations for replay in Books Online)

    Visual studio team suite l.jpg

    Visual Studio Team Suite

    • Unit and Load test Web applications

    • Simulate multiple users

    • Programmable to cater for runtime different etc

    Microsoft fiddler l.jpg

    Microsoft Fiddler

    • Debugging Proxy

    • Save as Visual Studio WebTest

    • Download from

    Resources l.jpg


    • Profiling for Better Performance by Kimberly Tripp, MSDN webcast at

    • MSDN Webcasts

    • Using Visual Studio 2005 to Perform Load Testing on a SQL Server 2005 Reporting Services Report Server

    • Using SQL Server 2005 Profiler Brian Knight

    • SQL Server Manageability Team Blog

    Resources35 l.jpg


    • Troubleshooting Performance Problems in SQL Server 2005 document

    • Jasper Smith MVP - 1 Hour Trace

    • Simon Sabine’s Taskpad Custom Report

    • Aaron Bertrand’s Show Blocking Custom Report

    Slide36 l.jpg

    Platinum[Conference Web site][Becoming the premier Blogging site for SQL professionals]

    Gold[UK SQL Server Community Website]


    UK SQL Bloggers

    Feedback Forms!!

  • Login