1 / 36

Identifying Performance Problems with SQL Profiler

Learn about using SQL Profiler as a powerful tool for diagnosing SQL performance issues, integrated with other tools for a complete monitoring toolkit. Discover how to analyze performance data and optimize database performance efficiently.

rozannej
Download Presentation

Identifying Performance Problems with SQL Profiler

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 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]

  2. Identifying Performance Problems with SQL Profiler Martin Bell SQL Server MVP

  3. 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

  4. 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

  5. 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

  6. 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

  7. SQL Profiler • Improved interface for creating a profile • Added filtering capability • Deadlock Graphs • Showplan XML • Re-configure trace without loosing data • Include Perfmon Data

  8. Deadlock Graphs • Better detection of deadlocks • No need for blocker scripts or other methods • Easily identify blocking problems

  9. -- 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

  10. -- 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

  11. 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.

  12. Define Profiler Templates • Save your own templates if you frequently require to profile certain events/columns

  13. 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

  14. 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

  15. 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!

  16. Gotchas • Using SQL Profiler on database server can take resources • Uses temp directory to storage – make sure this is not on the system disc!

  17. 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!

  18. Running traces • Use fn_trace_setstatus for the given trace id to change the status • 0 stops the trace • 1 starts the trace • 2 closes the trace

  19. Show running traces • Use fn_trace_getinfo to return information on the traces loaded and running. Property column: • 1 Trace options. • 2 – Trace file rolls over • 4 – Shutdown trace on error • 8 – Produce a Blackbox trace • 2 File name • 3 Max size • 4 Stop time • 5 Current trace status

  20. 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)

  21. Analysing the results • Read80Trace (SQL Server 2000) • Manual Loading and Analysis • ClearTrace utility

  22. 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

  23. 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

  24. 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

  25. 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

  26. ClearTrace • Written by SQL Server MVP - Bill Graziano • Download from: http://www.cleardata.biz/cleartrace/download.aspx

  27. 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

  28. 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)

  29. Visual Studio Team Suite • Unit and Load test Web applications • Simulate multiple users • Programmable to cater for runtime different etc

  30. Microsoft Fiddler • Debugging Proxy • Save as Visual Studio WebTest • Download from www.fiddlertool.com

  31. 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

  32. 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

  33. 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!!

More Related