Programming4us
         
 
 
SQL Server

SQL Server 2008 : Profiler Usage Scenarios (part 1) - Analyzing Slow Stored Procedures or Queries & Deadlocks

3/19/2011 9:13:19 AM
Analyzing Slow Stored Procedures or Queries

After you identify that a particular stored procedure is running slowly, what should you do? You might want to look at the estimated execution plan for the stored procedure, looking for table scans and sections of the plan that have a high cost percentage. But what if the execution plan has no obvious problems? This is the time you should consider using the SQL Profiler.

You can set up a trace on the stored procedure that captures the execution of each statement within it, along with its duration, in milliseconds. Here’s how:

1.
Create a new trace, using the TSQL_Duration template.

2.
Add the SP:StmtCompleted event from the stored procedure event class to the trace.

3.
Add a filter on the Duration column with the duration not equal to 0. You can also set the filter to a larger number to exclude more of the short-running statements.

If you plan to run the procedure from SSMS, you might want to add a filter on the SPID column as well. Set it equal to the process ID for your session; the SPID is displayed at the bottom of the SSMS window next to your username, in parentheses. This traces only those commands that are executed from your SSMS query editor window.

When you run the trace and execute the stored procedure, you see only those statements in the procedure that have nonzero duration. The statements are listed in ascending duration order. You need to look to the bottom of the Profiler output window to find your longer-running statements. You can isolate these statements, copy them to SSMS, and perform a separate analysis on them to determine your problem.

You can also add showplan events to your Profiler trace to capture the execution plan as the trace is running. SQL Server now has showplan events that capture the showplan results in XML format. Traces with this type of XML output can have a significant impact on server performance while they are running but make the identification of poorly performing statements much easier. When you are tracing stored procedure executions, it is a good idea to add a filter on the specific stored procedure you are targeting to help minimize the impact on performance.

After you run a trace with an XML showplan event, you can choose to extract the showplan events to a separate file. To do so, in the SQL Server Profiler you select File, Export, Extract SQL Server Events, Extract Showplan Events. At this point, you can save the showplan events in a single file or to a separate file for each event. The file(s) is saved with a SQLPlan file extension. This file can then be opened in SSMS, and the graphical query execution plan is displayed.

Deadlocks

Deadlocks are a common occurrence in database management systems (DMBSs). In simple terms, deadlocks occur when a process (for example, SPID 10) has a lock on a resource that another process (for example, SPID 20) wants. In addition, the second process (SPID 20) wants the resource that the first process has locked. This cyclic dependency causes the DBMS to kill one of the processes to resolve the deadlock situation.

Resolving deadlocks and identifying the deadlock participants can be difficult. In SQL Server 2008 and past versions, trace flag 1204 can be set to capture the processes involved in the deadlock. The output is text based but provides valuable information about the types of locks and the statements that were executing at the time of the deadlock. In addition to this approach, SQL Server 2008 offers the capability to capture detailed deadlock information via the SQL Server Profiler. This type of tracing can be accomplished as follows:

1.
Create a new trace, using a Blank template; this leaves the selection of all the events, data columns, and filters to you.

2.
Add the Locks:Deadlock graph event to the trace from the Locks category. An additional tab named Event Extraction Settings appears on the Trace Properties window.

3.
Click the Save Deadlock XML Events Separately check box. This causes the deadlock information to be written to a separate file. You could also export the results after the trace has been run by using the File, Export option.

When you run this trace, it captures any deadlock event that occurs and writes it to the XML file specified. To test this, you can open two query editor windows and execute the following statements, in the order listed, and in the query window specified:

-- In Query Window # 1
--Step1
USE ADVENTUREWORKS2008
GO
BEGIN TRAN
UPDATE HumanResources.Employee SET ModifiedDate = GETDATE()

-- In Query Window # 2
--Step2
USE ADVENTUREWORKS2008
GO
BEGIN TRAN
UPDATE HumanResources.Department SET ModifiedDate = GETDATE()
SELECT * FROM HumanResources.Employee

-- In Query Window # 1
--Step3
SELECT * FROM HumanResources.Department

When the deadlock occurs, the results pane for one of the query windows contains a message similar to the following:

Msg 1205, Level 13, State 51, Line 3
Transaction (Process ID 55) was deadlocked on lock resources with another
process and has been chosen as the deadlock victim. Rerun the transaction.

When the row with the Deadlock graph event is selected in the Profiler output grid, a graph like the one shown in Figure 1 is displayed.

Figure 1. Output from the Deadlock graph event.

The Deadlock graph event contains a wealth of information about the deadlock occurrence. The oval nodes represent the processes involved in the deadlock. The oval with an X mark across it is the deadlock victim that had its process killed. The other oval represents the process that was allowed to complete when the deadlock was resolved. The boxes in the middle of the graph display lock information about the specific objects involved in the deadlock.

The graph is interactive and displays relevant information about the processes that were running when the deadlock occurred. For example, when you mouse over the oval nodes, pop-up text appears, displaying the SQL statement that was executing at the time of the deadlock. This is the same type of information that is displayed when the aforementioned trace flag is used, but the graph tends to be easier to decipher.

Other -----------------
- SQL Server 2008 : Defining Server-Side Traces
- SQL Server 2008 : SQL Server Profiler - Replaying Trace Data
- SQL Server 2008 : SQL Server Profiler - Saving and Exporting Traces
- SQL Server 2008 : SQL Server Profiler - Creating Traces
- SQL Server 2008 : SQL Server Profiler Architecture
- SQL Server 2008: Administering Database Objects - Working with Tables (part 7) - Partitions
- SQL Server 2008: Administering Database Objects - Working with Tables (part 6) - Compression
- SQL Server 2008: Administering Database Objects - Working with Tables (part 5) - Sparse Columns
- SQL Server 2008: Administering Database Objects - Working with Tables (part 4) - Check Constraints
- SQL Server 2008: Administering Database Objects - Working with Tables (part 3) - Foreign Key Constraints
- SQL Server 2008: Administering Database Objects - Working with Tables (part 2) - Primary Key Constraints & Unique Constraints
- SQL Server 2008: Administering Database Objects - Working with Tables (part 1) - Default Constraints
- SQL Server 2008: Administering Database Objects - Working with Database Snapshots
- Programming with SQL Azure : WCF Data Services (part 3)
- Programming with SQL Azure : WCF Data Services (part 2) - Creating the Client Application
- Using XML in SQL Server 2008: Relational Data As XML - The FOR XML Modes (part 4) - EXPLICIT Mode
- Using XML in SQL Server 2008: Relational Data As XML - The FOR XML Modes (part 3) - AUTO Mode
- Programming with SQL Azure : WCF Data Services (part 1)
- Using XML in SQL Server 2008: Relational Data As XML - The FOR XML Modes (part 2) - Working with Binary Columns
- Using XML in SQL Server 2008: Relational Data As XML - The FOR XML Modes (part 1) - RAW Mode
 
 
Most View
- Context and Interception : Custom Component Services (part 1) - Building a Custom Context Attribute & Installing a Custom Message Sink
- Programming Windows Phone 7: XNA Orientation
- SQL Server 2008 R2 : Replication - The Publisher, Distributor, and Subscriber Magazine Metaphor
- BizTalk Server 2009 : Service-oriented schema patterns (part 1) - Designing schemas based on service type
- jQuery 1.3 : AJAX - Loading data on demand (part 3) - Loading an XML document
- Installing Windows Server 2012 and Server Core : Installing a Clean Version of Windows Server 2012 Operating System (part 2)
- BizTalk Server 2006 Operations : Disaster Recovery
- Windows Server 2003 Security Configuration (part 1) - Windows Server 2003 Security Settings
- Windows Server 2008: Installing a Read-Only Domain Controller (part 1)
- Windows Server Backup Volume Recovery (part 1) - Windows Server 2008 R2 Data Volume Recovery
Top 10
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 3) - Configuring Recipient Filtering
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 2)
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 1)
- Implementing Edge Services for an Exchange Server 2007 Environment : Installing and Configuring the Edge Transport Server Components
- What's New in SharePoint 2013 (part 7) - BCS
- What's New in SharePoint 2013 (part 6) - SEARCH
- What's New in SharePoint 2013 (part 6) - WEB CONTENT MANAGEMENT
- What's New in SharePoint 2013 (part 5) - ENTERPRISE CONTENT MANAGEMENT
- What's New in SharePoint 2013 (part 4) - WORKFLOWS
- What's New in SharePoint 2013 (part 3) - REMOTE EVENTS