Tuesday, January 23, 2007

Combining SQL Server Profiler and Windows Performance Logs and Alerts to monitor performance issues

SQL Server Profiler can give you a great hand when troubleshooting performance in a SQL Server database. If you combine Profiler with the Performance Logs and Alerts snap in, you can put your sql statements next to the performance counter to make it easy to find the weak points.

In the following example, I'll show you how you can find a deadlock by combining both tools:

Set up of the performance counter:

  • Open the Performance Logs and Alerts snap-in (Administrative Tools)

  • Expand Performance Logs and Alerts, right-click Counter Logs and click New Log Settings

  • Choose a name for the log (I've used DeadlockLogging)

  • In the General tab click Add Counters

  • Set the properties as below:

    • Performance object: SQLServer:Locks
    • Select counters from list: Number of Deadlocks/sec
    • Select instances from list: _Total

  • Click Close

  • In the General tab, change the Interval to 1 second

  • In the Log Files tab, select Text File (Comma delimited) for the Log File Type

  • In the Schedule tab, select both Start and Stop methods to Manually
  • Click OK (if a warning message occurs to create the directory, click Yes)

  • Select the counter you just created and start it

Set up of the SQL Server Profiler:

  • Open SQL Server Profiler (All Programs\Microsoft SQL Server 2005\Performance Tools\SQL Server Profiler)

  • Create a new trace (File\New Trace ...) and connect to the server

  • In the Use the template box, select TSQL_Replay

  • Check the Save to file and specify a location for the file

  • Click Run

Create a deadlock situation:

  • Open SQL Server Management Studio

  • Run the following query:
    USE tempdb;

    CREATE TABLE Product (
    ProdID INT,
    ProdName VARCHAR(50)

    INSERT INTO Product VALUES (1, 'Book')

    CREATE TABLE ProdOrder (
    ProdOrderID INT,
    ProdID INT,
    OrderCode VARCHAR(10)

    INSERT INTO ProdOrder VALUES (1, 1, 'BKO1')

  • Enter the following query but do not run it yet:
    USE tempdb;

    SET ProdName = 'DVD'
    WHERE ProdID = 1
    WAITFOR DELAY '00:00:10'

    UPDATE ProdOrder
    SET OrderCode = 'DVD01'
    WHERE ProdOrderID = 1

  • Open a new query window and type the following statement:
    USE tempdb;

    SET OrderCode = 'BK01'
    WHERE ProdOrderID = 1
    WAITFOR DELAY '00:00:10'

    UPDATE Product
    SET ProdName = 'BOOK'
    WHERE ProdID = 1

  • Start both queries

  • You will see that after a few seconds, the following error will be shown: "Msg 1205, Level 13, State 45, Line 8 Transaction (Process ID 53) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction."

Look up the deadlock in Profiler:

  • Open the Performance Log and Alerts and stop the DeadlockLogging counter

  • Switch to SQL Server Profiler and stop the trace

  • Close SQL Server Profiler and open the trace file you just created

  • From the File menu, select Import Performance Data and select the log file created by Performance Log and Alerts

  • In the Performance Counter Limit Dialog window make sure that everything is selected and click OK

  • Now you can loop throught the timebar and see which select statement performed the deadlock

1 comment:

Tech Dreamer said...

Very Useful post..