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

Platinum PowerPoint PPT Presentation


  • 126 Views
  • Uploaded on
  • Presentation posted in: General

Platinum. Learn & Enjoy [Put your phone on Vibrate!]. www.sqlbits.com. 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

Platinum

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

Platinum

Learn & Enjoy [Put your phone on Vibrate!]

www.sqlbits.com

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]

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

Introduction

  • 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 http://www.microsoft.com/downloads/details.aspx?FamilyId=1D3A4A0D-7E0C-4730-8204-E419218C1EFC&displaylang=en

  • 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

USE PUBS

GO

SELECT @@SPID AS [Windows 1 SPID]

GO

BEGIN TRANSACTION

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

WAITFOR DELAY '000:00:03'

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

ROLLBACK TRANSACTION

GO


Slide10 l.jpg

-- Script 2

USE PUBS

GO

SELECT @@SPID AS [Windows 2 SPID]

GO

BEGIN TRANSACTION

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

WAITFOR DELAY '000:00:03'

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

ROLLBACK TRANSACTION

GO


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

Gotchas

  • 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

    AS

    SELECT LEFT(SUBSTRING(CAST(textdata as VARCHAR(8000)),

    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],

    [Duration],

    [Reads],

    [Writes],

    [CPU]

    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 http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlpro04/html/sp04j1.asp


    Cleartrace l.jpg

    ClearTrace

    • Written by SQL Server MVP - Bill Graziano

    • Download from: http://www.cleardata.biz/cleartrace/download.aspx


    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 www.fiddlertool.com


    Resources l.jpg

    Resources

    • Profiling for Better Performance by Kimberly Tripp, MSDN webcast athttp://msevents.microsoft.com/cui/webcasteventdetails.aspx?eventid=1032278616&eventcategory=5&culture=en-us&countrycode=us

    • MSDN Webcasts http://msdn.microsoft.com/webcasts

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

      http://msdn2.microsoft.com/en-us/library/aa964139.aspx

    • Using SQL Server 2005 Profiler Brian Knight

      http://www.jumpstarttv.com/Media.aspx?vid=59

    • SQL Server Manageability Team Bloghttp://blogs.msdn.com/sqlrem/default.aspx


    Resources35 l.jpg

    Resources

    • Troubleshooting Performance Problems in SQL Server 2005 documenthttp://www.microsoft.com/prodtechnol/sql/2005/tsprfprb.mspx

    • Jasper Smith MVP - 1 Hour Trace http://www.sqldbatips.com/displaycode.asp?ID=7

    • Simon Sabine’s Taskpad Custom Report http://sqlblogcasts.com/blogs/simons/archive/2007/03/28/Update-to-the-taskpad-custom-report.aspx

    • Aaron Bertrand’s Show Blocking Custom Report http://sqlblog.com/blogs/aaron_bertrand/archive/2006/12/19/448.aspx


    Slide36 l.jpg

    Platinum

    www.SQLBits.com[Conference Web site]

    www.sqlbits.com

    www.SQLBlogCasts.com[Becoming the premier Blogging site for SQL professionals]

    Gold

    www.SQLServerFAQ.com[UK SQL Server Community Website]

    Silver

    UK SQL Bloggers

    cwebbbi.spaces.live.comsqlblogcasts.com/blogs/simonssqlblogcasts.com/blogs/tonyrogerson

    Feedback Forms!!


  • Login