July 17 Meeting At Denver SQL Server User’s Group

This Thursday is is the July meeting for the Denver SQL Server User’s Group at the Microsoft office in the Tech Center.   Janis Griffin is giving a presentation on Wait-Time Based SQL Server Performance Management, which should be very interesting.  I will be there, and I think that Kevin Cox will also be in the area.

I use the queries below every day to keep an eye on what my top wait types on my various SQL Server instances.

 

    -- Clear Wait Stats
    DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR);


    -- Isolate top waits
    WITH Waits AS
    (
      SELECT
        wait_type,
        wait_time_ms / 1000. AS wait_time_s,
        100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,
        ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn
      FROM sys.dm_os_wait_stats
      WHERE wait_type NOT LIKE '%SLEEP%'
      -- filter out additional irrelevant waits
    )
    SELECT
      W1.wait_type, 
      CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,
      CAST(W1.pct AS DECIMAL(12, 2)) AS pct,
      CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct
    FROM Waits AS W1
    INNER JOIN Waits AS W2
    ON W2.rn <= W1.rn
    GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct
    HAVING SUM(W2.pct) - W1.pct < 90 -- percentage threshold
    ORDER BY W1.rn;

This entry was posted in SQL Server 2008. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s