slide1
Download
Skip this Video
Download Presentation
Platinum

Loading in 2 Seconds...

play fullscreen
1 / 36

Group BY: [Food and Drink at Reading Bowl, see you there] - PowerPoint PPT Presentation


  • 165 Views
  • Uploaded on

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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Group BY: [Food and Drink at Reading Bowl, see you there]' - hao


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

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]

introduction
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
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
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
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
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
Deadlock Graphs
  • Better detection of deadlocks
  • No need for blocker scripts or other methods
  • Easily identify blocking problems
slide9
-- 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
-- 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
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
Define Profiler Templates
  • Save your own templates if you frequently require to profile certain events/columns
changing events without loosing data
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
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
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
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
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
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
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
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
Analysing the results
  • Read80Trace (SQL Server 2000)
  • Manual Loading and Analysis
  • ClearTrace utility
manual analysis
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
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
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
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
ClearTrace
  • Written by SQL Server MVP - Bill Graziano
  • Download from: http://www.cleardata.biz/cleartrace/download.aspx
replaying traces
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
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
Visual Studio Team Suite
  • Unit and Load test Web applications
  • Simulate multiple users
  • Programmable to cater for runtime different etc
microsoft fiddler
Microsoft Fiddler
  • Debugging Proxy
  • Save as Visual Studio WebTest
  • Download from www.fiddlertool.com
resources
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
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

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

ad