360 likes | 510 Views
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].
E N D
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 Martin Bell SQL Server MVP
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 • 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 • 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 • 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 • Improved interface for creating a profile • Added filtering capability • Deadlock Graphs • Showplan XML • Re-configure trace without loosing data • Include Perfmon Data
Deadlock Graphs • Better detection of deadlocks • No need for blocker scripts or other methods • Easily identify blocking problems
-- 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
-- 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
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 • Save your own templates if you frequently require to profile certain events/columns
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 • 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 • 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 • 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 • 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 • 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
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
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 • Read80Trace (SQL Server 2000) • Manual Loading and Analysis • ClearTrace utility
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 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 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 • 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 • Written by SQL Server MVP - Bill Graziano • Download from: http://www.cleardata.biz/cleartrace/download.aspx
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 • 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 • Unit and Load test Web applications • Simulate multiple users • Programmable to cater for runtime different etc
Microsoft Fiddler • Debugging Proxy • Save as Visual Studio WebTest • Download from www.fiddlertool.com
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
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
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!!