Strate SQL

I’ve been meaning to join in on the T-SQL Tuesday event for the last couple months and let the time opportunity slip by.  This month’s topic is reporting and its seems like a good time to get in on the event.

The great thing about reporting is that for the past few months I’ve been building towards this very post.  A few months ago, I posted a blog on a process that I often use with clients and on my own machines to capture wait stats over time.  A while later, I followed-up with a blog on categorizing wait stat and providing definitions for them.

While that was all fine and good, I hadn’t provided any method to report this information in a manner that would make gathering this information useful at all.  Part of the delay was that shortly after the first post I realized that the reports I had intended to put up were useless.  They were pretty and had lots of data, but there wasn’t anything you could really accomplish with them.  What’s the point of useless content?

So, here I’ll walk through a level-100 report based on the information that’s been collected.  The point of this report will simply be to provide a snapshot over a number of days of all wait stats and their degree of activity on a SQL Server instance.

Wait Stat Snapshot Procedure

Before I can provide the report, we’ll first need a stored procedure to work off of.  It’s a fairly simple procedure based on the previous two posts on this topic.  Those ones I mentioned above.

USE [DBADiagnostics]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Report].[WaitStatSnapshot]') AND type in (N'P', N'PC'))
DROP PROCEDURE [Report].[WaitStatSnapshot]
GO

/*============================================================
Procedure:	Report.WaitStatSnapshot
Author:		Jason Strate
Date:		April 1, 2010

Synopsis:
This procedure aggregates wait stats for the selected date range.  Data is
joined with category information to provide description and action
guidance if available.

Parameters
@StartDate  : Begin date for aggregating wait stats.  Begins from the start
of the date.
@EndDate    : End date for aggregating wait stats.  Includes all data for the
end date.

============================================================
Revision History:
Date:		By			Description
------------------------------------------------------------

============================================================*/
CREATE PROCEDURE [Report].[WaitStatSnapshot]
(
@StartDate datetime
,@EndDate datetime
)
As

;WITH WaitStatCTE
AS (
SELECT COALESCE(wtc.Category, 'UNKNOWN') AS Category
,COALESCE(wtc.Resource, 'UNKNOWN') AS Resource
,wtc.Description
,wtc.Action
,wsh.WaitType
,SUM(wsh.WaitingTasksCount) AS WaitingTasksCount
,SUM(wsh.WaitTimeMs) AS WaitTimeMs
,SUM(wsh.SignalWaitTimeMs) AS SignalWaitTimeMs
FROM Monitor.WaitStatHistory wsh
INNER JOIN Resources.WaitTypeCategory wtc ON wsh.WaitType = wtc.WaitType
WHERE wsh.WaitingTasksCount <> 0
AND	wsh.CreateDate BETWEEN @StartDate AND DATEADD(ms, -3, DATEADD(d, 1, @EndDate))
GROUP BY COALESCE(wtc.Category, 'UNKNOWN')
,COALESCE(wtc.Resource, 'UNKNOWN')
,wsh.WaitType
,wtc.Description
,wtc.Action
)
SELECT Category
,Resource
,[Description]
,[Action]
,WaitType
,WaitTimeMs
,WaitingTasksCount
,SUM(WaitTimeMs) OVER() AS TotalWaitTimeMS
,SignalWaitTimeMs
,CAST((1.*WaitTimeMs)/SUM(WaitTimeMs) OVER() AS decimal(8,6)) AS PercentWaitTimeMS
FROM WaitStatCTE
ORDER BY WaitTimeMs DESC
GO

Wait Stat Snapshot Report

The stored procedures above returns all of the wait stats aggregated over the selected time period and displays them on the report shown below.  By default the report won’t display Description or Action information, but you can easily drill-down into that information.

The report can be downloaded here :

To deploy this report, you’ll have to update the data source information for the report.  Beyond that it should be easy to deploy and begin using.

Finding Some Benefit

I’ve been using this report to keep an eye on what’s happening on the SQL Servers that I’ve been monitoring for clients and it provides a good snapshot of what’s been happening since I last reviewed the server.  The analysis isn’t too deep, but then it wouldn’t be called a snapshot report.

A few weeks ago I posted a job that I use to track wait stats in the DBA database that I bring to a number of clients.  I had promised to follow-up with a couple or three reports that could be used to monitor the values that were being aggregated in those tables.

Before getting to that, though, a quick look at the output of sys.dm_os_wait_stats might have indicated that there is a small issue with looking at the wait stats as they currently are.  There are a lot of wait types.  Specifically, my instances of SQL Server report 202 wait types for SQL Server 2005 and 485 wait types for SQL Server 2008.  Combining the two lists yields nearly 500 distinct wait types between SQL Server 2005 and 2008.  Ever notice that the MISCELLANEOUS wait type appears twice for SQL Server 2008, I bet there is a good story behind that one.

With all of those wait types, there needs to be a method to group them together to assist in researching resource performance related issues.  Of course, you already knew this and it’d probably be nice if I stopped my stalling and go to the point.

Building A Wait Type Category List

Before I go there, first I’d like to point out how I built the list and my plans for keeping it updated.  What’s the value in a classification process, if you don’t know the value of the information included?

To start with there are people that know the operations and internals of SQL Server much better than I do.  There is even a whole department at Microsoft, the CSS SQL Server Engineers,  that spends a lot time up to their elbows working with wait types.  Let’s be real, I mostly know what these guys have published.  Recently, Bob Ward has started a SQL Server Wait Type Repository this is the basis for the wait type categories.  Why not just use this list and be done with it?  Well, the repository only has 22 wait types currently listed.  They are hitting the ones that provide the most value.  Their table does provide a structure and starting point for this list and I consider it the source for updates to categorizing wait types.

Next the list was augmented with the information provided in the SQL Server 2005 Waits and Queues white paper.  This is a detailed white paper the provides categories and troubleshooting information for different wait types for SQL Server 2005.  If you haven’t read this white paper you are definitely missing out and should bail on this post and go read it.

The third resource I used was the SQLServerPedia Wait Type list.  This list covers a number of the items that aren’t covered in the previous two sources and almost all of the remaining wait types.  And depending on when you read this, it may just look identical to the wait type category list since I am hoping to submit an update of one with the other.

Beyond these, there are still a few holes in the list but hopefully over time one of the three sources above or some other source will fill in the gaps and I’ll just post updates from time to time.

Providing the Wait Type Category List

Based on the above, here is the schema for a table that will hold all of the wait types that conforms to the structure I mentioned earlier:

USE [DBADiagnostics]GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Resources].[WaitTypeCategory]') AND type in (N'U'))    DROP TABLE [Resources].[WaitTypeCategory]GO

IF  EXISTS (SELECT * FROM sys.schemas WHERE name = N'Resources')    DROP SCHEMA [Resources]GO

IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'Resources')    EXEC sys.sp_executesql N'CREATE SCHEMA [Resources] AUTHORIZATION [dbo]'GO

CREATE TABLE [Resources].[WaitTypeCategory](    [WaitType] [nvarchar](60) NOT NULL,    [Category] [varchar](50) NULL,    [Resource] [varchar](50) NULL,    [Version] [varchar](50) NULL,    [Description] [varchar](255) NULL,    [Action] [varchar](max) NULL, CONSTRAINT [PK_WaitTypeCategory] PRIMARY KEY CLUSTERED     (    [WaitType] ASC    )) GO

If you don’t know where you’ve been, how can you expect to know where you are going.  Seems like such a simple little line and I’m sure many of us can look at history, politicians, and maybe that restaurant review that you didn’t heed when it comes to this line.  But this also applies to DBAs and more specifically to SQL Server.

In much the same way… if you don’t know what’s been happening in your SQL Server environment, you won’t know how to resolve performance problems that have occurred on the server.  One of the ways I track what’s happened on a SQL Server instance is through wait stats.  Now if you aren’t tracking wait stats and they aren’t one of the first few things that you check when monitoring performance then you need to stop reading this post and read this instead.

Wait Stat Snapshots

To facilitate wait stat monitoring, I typically take a snapshot of the current wait stats (sys.dm_os_wait_stats) on a server every 15 minutes.  This is enough time that I can roll get a good idea of what’s happened from a resource perspective in a short amount of time and I can also easily roll the value up to hour, two-hour, or full day totals.  It gives me options – we all like options.

As I’ve done in a number of posts in the past this new script will be setup to add to the DBADiagnostics database that I’ve blogged about before.

Building the Snapshot

There are two table included in the process for creating the snapshots of wait stat activity.  These are:

• Monitor.WaitStatSnapshot
• Monitor.WaitStatHistory

Here is the schema to create them:

USE [DBADiagnostics]
GO
Â
CREATE TABLE [Monitor].[WaitStatSnapshot](
    [CreateDate] [datetime] NOT NULL,
    [WaitType] [nvarchar](60) NOT NULL,
    [WaitingTasksCount] [bigint] NOT NULL,
    [WaitTimeMs] [bigint] NOT NULL,
    [MaxWaitTimeMs] [bigint] NOT NULL,
    [SignalWaitTimeMs] [bigint] NOT NULL,
    CONSTRAINT [PK_Monitor_WaitStatSnapshot_CreateDateWaitType]
        PRIMARY KEY CLUSTERED ([CreateDate] ASC, [WaitType] ASC)
) ON [Data]
GO
Â
CREATE TABLE [Monitor].[WaitStatHistory](
    [CreateDate] [datetime] NOT NULL,
    [WaitType] [nvarchar](60) NOT NULL,
    [WaitingTasksCount] [bigint] NOT NULL,
    [WaitTimeMs] [bigint] NOT NULL,
    [MaxWaitTimeMs] [bigint] NOT NULL,
    [SignalWaitTimeMs] [bigint] NOT NULL,
    CONSTRAINT [PK_Monitor_WaitStatHistory_CreateDateWaitType]
        PRIMARY KEY CLUSTERED ([CreateDate] ASC, [WaitType] ASC)
) ON [Data]

The general process is to insert the current values from the wait stat DMV into Monitor.WaitStatSnapshot.  After which the delta between the last two snapshots in Monitor.WaitStatSnapshot is inserted into Monitor.WaitStatHistory.

If the values in the last snapshot are smaller that those in the second to last snapshot, then the insert assumes that the wait stat DMV has been reset.  Resetting can occur either though a SQL Server service restart or a DBCC command.

Either way the procedure below will accumulate a snapshot history from the wait stat DMV.  Of courses there are a couple parameters that provide some automated cleanup.  Data in Monitor.WaitStatSnapshot is cleaned up after 1 day and the data in Monitor.WaitStatHistory is cleaned up after 90 days.  This helps prevent the data in the DBADiagnostic database from taking over when it’s neglected because everything is running perfectly.

USE [DBADiagnostics]
GO

/*============================================================
Procedure:    Monitor.TrackWaitStats
Author:        Jason Strate
Date:        October 26, 2009

Synopsis:
    This procedure takes snapshots of wait stats and compares them with previous
    snapshots to determine a delta of changes over time.  Raw snapshot information
    is deleted on a short time span, while the delta information in the history
    table is deleted over a longer time span.

============================================================
Revision History:
Date:        By            Description
------------------------------------------------------------

============================================================*/
CREATE PROCEDURE [Monitor].[TrackWaitStats]
    (
    @SnapshotDays tinyint = 1
    ,@HistoryDays smallint = 90
    )
AS

INSERT INTO Monitor.WaitStatSnapshot
SELECT GETDATE()
    , CASE wait_type WHEN 'MISCELLANEOUS' THEN 'MISCELLANEOUS' ELSE wait_type END
    , SUM(waiting_tasks_count)
    , SUM(wait_time_ms)
    , SUM(max_wait_time_ms)
    , SUM(signal_wait_time_ms)
FROM sys.dm_os_wait_stats
GROUP BY CASE wait_type WHEN 'MISCELLANEOUS' THEN 'MISCELLANEOUS' ELSE wait_type END

;WITH WaitStatCTE
AS (
    SELECT CreateDate
        , DENSE_RANK() OVER (ORDER BY CreateDate DESC) AS HistoryID
        , WaitType
        , WaitingTasksCount
        , WaitTimeMs
        , MaxWaitTimeMs
        , SignalWaitTimeMs
    FROM Monitor.WaitStatSnapshot
    )
INSERT INTO Monitor.WaitStatHistory
SELECT w1.CreateDate
    , w1.WaitType
    , w1.WaitingTasksCount - COALESCE(w2.WaitingTasksCount,0)
    , w1.WaitTimeMs - COALESCE(w2.WaitTimeMs,0)
    , w1.MaxWaitTimeMs - COALESCE(w2.MaxWaitTimeMs,0)
    , w1.SignalWaitTimeMs - COALESCE(w2.SignalWaitTimeMs,0)
FROM WaitStatCTE w1
    LEFT OUTER JOIN WaitStatCTE w2 ON w1.WaitType = w2.WaitType
        AND w1.WaitingTasksCount >= COALESCE(w2.WaitingTasksCount,0)
        AND w2.HistoryID = 2
WHERE w1.HistoryID = 1

DELETE FROM Monitor.WaitStatSnapshot
WHERE CreateDate < DATEADD(d, -@SnapshotDays, GETDATE())

DELETE FROM Monitor.WaitStatHistory
WHERE CreateDate < DATEADD(d, -@HistoryDays, GETDATE())
GO

There you have it.  While I didn’t go into how you can and will use this information, the basis for collecting this data for reporting has been laid out.

Later this week I’ll post a follow-up to show how to report on this information and include a couple Reporting Services reports for digging into the data.

The Minnesota SQL Server user group (PASSMN) meeting is tonight at the Microsoft offices.  Come check us out to network and mingle with your peers and also for the opportunity to learn some new things.

Tonight’s topics are:

• SQL Server and Partition Alignment
• Wait-Time Based Performance Management

If you have questions or need more information on the meeting, feel free to contact me on twitter at@StrateSQL or though email at jstrate@digineer.com.

Just one week until the next PASSMN meeting.  As I mentioned last week, this month we’ll be focusing a  bit on performance.    And yeah, guys with lights coming out of his body means performance… or it might be just a great party.  Come out and join the discussion with a snack and some great presentations.

SQL Server and Partition Alignment & Wait-Time Based Performance Management

September 15th
2:30 PM Networking & Social
3:00 PM Announcements
3:15 PM – 5:15 PM Presentations
8300 Norman Center Drive
9th Floor
Bloomington, MN  55437

If that wasn’t enough to make you want to want to show up, here are the presentation details…

SQL Server and Partition Alignment

Speaker: Jimmy May, Microsoft
Now that SQL Server wait stats are formally documented & DMVs are available, disk partition alignment may be the best kept secret related to database performance optimization. In combination with stripe unit size & file allocation unit size, learn how you can increase I/O throughput by 10%, 30%, & under some circumstances even more. Many customers are unaware of partition alignment. Even experienced disk administrators may be unfamiliar with it. Explanations are often initially met with disbelief. Engineers familiar with the topic may underestimate its importance. For example, some customers think it is useful only for Microsoft Exchange Server. In fact, partition alignment is important for all servers from which high performance is expected, especially SQL Server. Each month I encounter customers with unaligned storage. Until existing misaligned partitions created using Windows Server 2003 or Windows 2000 Server are rebuilt properly, disk partition alignment will remain a relevant technology.

Jimmy May  is a Principal Performance Engineer for Microsoft’s Assessment, Consulting, & Engineering (A.C.E.) Services & works for Team Lee. He is the Founder & Visionary-in-Chief of SQL Server Pros & was formerly the Senior Database Architect for one of the world’s largest OLTP DBs. He is a founder & on the executive committees of both the Indiana Windows User Group & Indianapolis Professional Association for SQL Server . Jimmy lives in Indianapolis, Indiana with his lovely bride, Phyllis, & Fannie May the Wonder Dog. Subscribe to & read his blog at msdn.blogs.com/jimmymay. Contact him at jimmymay@microsoft.com or aspiringgeek@live.com.

Wait-Time Based Performance Management

Speaker: Janis Griffin, Confio
Using Wait Time Analysis and Wait Types is a newer method for tuning SQL Server instances. As a result, there is often confusion on exactly what the data means. The issue typically centers around the fact the wait time data is analyzed at the wrong level or the collected wait time data is not detailed enough. This presentation will focus on these problems and review several real-life case studies of using SQL Server Wait Type data coupled with Wait-Time based performance analysis to solve the most difficult performance related issues.

Janis Griffin has over 20 years of DBA experience including design, development and implementation of many critical database applications. Before coming to Confio, Janis held DBA positions primarily in the Telecom Industry (15 yrs), working with both real-time network routing databases and OLTP business to business applications.

Just two weeks away is the next PASSMN meeting.  This month we’ll be focusing a  bit on performance and to emphasis this point I found a picture of someone moving so fast that light emanates from their body.

SQL Server and Partition Alignment & Wait-Time Based Performance Management

September 15th
2:30 PM Networking & Social
3:00 PM Announcements
3:15 PM – 5:15 PM Presentations
8300 Norman Center Drive
9th Floor
Bloomington, MN  55437

If that wasn’t enough to make you want to want to show up, here are the presentation details…

SQL Server and Partition Alignment

Speaker: Jimmy May, Microsoft
Now that SQL Server wait stats are formally documented & DMVs are available, disk partition alignment may be the best kept secret related to database performance optimization. In combination with stripe unit size & file allocation unit size, learn how you can increase I/O throughput by 10%, 30%, & under some circumstances even more. Many customers are unaware of partition alignment. Even experienced disk administrators may be unfamiliar with it. Explanations are often initially met with disbelief. Engineers familiar with the topic may underestimate its importance. For example, some customers think it is useful only for Microsoft Exchange Server. In fact, partition alignment is important for all servers from which high performance is expected, especially SQL Server. Each month I encounter customers with unaligned storage. Until existing misaligned partitions created using Windows Server 2003 or Windows 2000 Server are rebuilt properly, disk partition alignment will remain a relevant technology.

Jimmy May  is a Principal Performance Engineer for Microsoft’s Assessment, Consulting, & Engineering (A.C.E.) Services & works for Team Lee. He is the Founder & Visionary-in-Chief of SQL Server Pros & was formerly the Senior Database Architect for one of the world’s largest OLTP DBs. He is a founder & on the executive committees of both the Indiana Windows User Group & Indianapolis Professional Association for SQL Server . Jimmy lives in Indianapolis, Indiana with his lovely bride, Phyllis, & Fannie May the Wonder Dog. Subscribe to & read his blog at msdn.blogs.com/jimmymay. Contact him at jimmymay@microsoft.com or aspiringgeek@live.com.

Wait-Time Based Performance Management

Speaker: Janis Griffin, Confio
Using Wait Time Analysis and Wait Types is a newer method for tuning SQL Server instances. As a result, there is often confusion on exactly what the data means. The issue typically centers around the fact the wait time data is analyzed at the wrong level or the collected wait time data is not detailed enough. This presentation will focus on these problems and review several real-life case studies of using SQL Server Wait Type data coupled with Wait-Time based performance analysis to solve the most difficult performance related issues.

Janis Griffin has over 20 years of DBA experience including design, development and implementation of many critical database applications. Before coming to Confio, Janis held DBA positions primarily in the Telecom Industry (15 yrs), working with both real-time network routing databases and OLTP business to business applications.

When ever I go to a new client to assist with performance issues, I inevitably download the Troubleshooting Performance Problems in SLQ Server 2005 white paper.  In my opinion and many others, it is one of those documents that should be at the click of a button to open and I keep it handy on my desktop.

One of the most common wait types that I observe at clients is CXPACKET, known on the street as parallelism.  And since most of these are OLTP database systems, there is a good need for looking into the causes of said wait type.

When I’ve identified that parallelism is the reason for the server running hot and hopefully not catching on fire I start to look at what is running or has run on the server that is utilizing parallelism.  When I first starting to look for execution plans that might use parallelism, I used the query in the white paper (mentioned above) that would return back a list of all of the plans that are currently in the procedure cache that would utilize parallelism if they were selected.

That query is:

SELECT 

    p.*, 

    q.*,

    cp.plan_handle

FROM sys.dm_exec_cached_plans cp

    CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) p

    CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) q

WHERE cp.cacheobjtype = 'Compiled Plan' 

AND p.query_plan.value('declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/showplan";max(//p:RelOp/@Parallel)', 'float') > 0

But recently I got to thinking; and this can be a dangerous game. Do I care about a plan that is only used once? Also, is it better to first look at plans that utilize more CPU than other plans? How do I rank one plan against another plan?

To accomplish this, I turned to the DMV sys.dm_exec_query_stats. This DMV aggregates performance statistics for cached query plans. The trouble with this DMV is that it is the query level and not the procedure level so conditional statements and looping will affect how often some SQL statements are within a procedure. I’ve put together a couple queries that combine the query above with sys.dm_exec_query_stats.

First, I put together the top 50 statements by average CPU utilization that is part of an execution plan that utilizes parallelism. What I like about this query is that I get an exact SQL statement to look at. Unfortunately, this isn’t an exact science because the statement with the high average CPU may not be the statement that is utilizing parallelism. On the other hand, who is going to complain if a high CPU statement without parallelism is tuned?

SELECT TOP 50

    OBJECT_NAME(p.objectid, p.dbid) as [object_name]

    ,qs.execution_count

    ,qs.total_worker_time

    ,qs.total_logical_reads

    ,qs.total_elapsed_time

    ,CASE statement_end_offset WHEN -1 THEN q.text

        ELSE SUBSTRING(q.text, statement_start_offset/2, (statement_end_offset-statement_start_offset)/2) END as sql_statement

    ,p.query_plan

    ,q.text

    ,cp.plan_handle

FROM sys.dm_exec_query_stats qs

    INNER JOIN sys.dm_exec_cached_plans cp ON qs.plan_handle = cp.plan_handle

    CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) p

    CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) as q

WHERE cp.cacheobjtype = 'Compiled Plan' 

AND p.query_plan.value('declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/showplan";max(//p:RelOp/@Parallel)', 'float') > 0

ORDER BY qs.total_worker_time/qs.execution_count DESC

The second query I have here ignores the statement level detail of sys.dm_exec_query_stats and summarizes the views at the plan_handle level. This presents some problems though since execution counts amongst all query statements isn’t always the same within a plan and which number would be best to use? Minimum executions? Maximum executions? Average executions? It all depends which side you want to error on.  For the script, though, I went with maximum executions.

WITH cQueryStats

AS (

    SELECT qs.plan_handle

        ,MAX(qs.execution_count) as execution_count

        ,SUM(qs.total_worker_time) as total_worker_time

        ,SUM(qs.total_logical_reads) as total_logical_reads

        ,SUM(qs.total_elapsed_time) as total_elapsed_time

    FROM sys.dm_exec_query_stats qs

    GROUP BY qs.plan_handle

)

SELECT TOP 50

    OBJECT_NAME(p.objectid, p.dbid) as [object_name] ,qs.execution_count

    ,qs.total_worker_time

    ,qs.total_logical_reads

    ,qs.total_elapsed_time

    ,p.query_plan

    ,q.text

    ,cp.plan_handle

FROM cQueryStats qs

    INNER JOIN sys.dm_exec_cached_plans cp ON qs.plan_handle = cp.plan_handle

    CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) p

    CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) as q

WHERE cp.cacheobjtype = 'Compiled Plan' 

AND p.query_plan.value('declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/showplan";max(//p:RelOp/@Parallel)', 'float') > 0

ORDER BY qs.total_worker_time/qs.execution_count DESC

If you are already on SQL Server 2008, you do have the benefit of having the DMV sys.dm_exec_procedure_stats at your disposal.  Using this DMV will solve the problem of not having accurate execution information at the procedure level.  For those blesses with SQL Server 2008, you can use this query to identify execution statistics for stored plans that utilize parallelism.

SELECT TOP 50

    OBJECT_NAME(p.objectid, p.dbid) as [object_name] 

    ,ps.total_worker_time/ps.execution_count as avg_worker_time

    ,ps.execution_count

    ,ps.total_worker_time

    ,ps.total_logical_reads

    ,ps.total_elapsed_time

    ,p.query_plan

    ,q.text

    ,cp.plan_handle

FROM sys.dm_exec_procedure_stats ps

    INNER JOIN sys.dm_exec_cached_plans cp ON ps.plan_handle = cp.plan_handle

    CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) p

    CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) as q

WHERE cp.cacheobjtype = 'Compiled Plan' 

AND p.query_plan.value('declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/showplan";max(//p:RelOp/@Parallel)', 'float') > 0

ORDER BY ps.total_worker_time/ps.execution_count DESC

If anyone has any queries that they use to address and research parallelism in their environments, I would be more than happy to see them.

Looking for something fun to do?  Trying to get your SQL Server to perform a little better?

There is a PASSMN (Minnesota SQL Server User Group) meeting tonight.  We are meeting a little later than usual because of a room conflict.  Instead of starting the meeting at 2:30 PM we are meeting at 4:30 PM.

Here are the sessions that we will have tonight:

Drive Your Way to the DMV
Speaker: Jason Strate, Digineer

Performance Troubleshooting with Wait Statistics
Speaker: Joe Sack, Microsoft

Register here if you are planning to come join us.

Drive Your Way to the DMV & Performance Troubleshooting with Wait Statistics

April 21, 2009
3:00 PM – 5:15 PM

Drive Your Way to the DMV
Speaker: Jason Strate, Digineer

Dynamic Management Views (DMVs) and Dynamic Management Functions (DMF) have been around since the release of SQL Server 2005. But for many people DMVs and DMFs are still a mystery. Step under the hood and review some of the main DMVs and DMFs in SQL Server. From sessions to caches a walk through of some of the more useful DMVs and a practical look at what they all really mean.

Jason Strate, Digineer Inc, has been a Database Architect for over ten years. His experiences include designing and implementing both OLAP and OLTP solutions involving clustering servers, database monitoring and tuning, analysis services, DTS/Integration Services, Reporting Services, and Notification Services. Jason is actively involved with the local PASS chapter (SQL Server User Group); he is currently serving on the Executive Board and has presented at monthly meetings. Jason contributed to Microsoft’s published white paper "Empowering Enterprise Solutions with SQL Server 2005 Enterprise Edition" and actively blogs about SQL Server and related technologies. Also this year, Jason participated in the development of Microsoft Certification exams for SQL Server 2008, presented at the SSWUG SQL Server Launch Event and is presenting in upcoming SSWUG SQL Server Conference.

Performance Troubleshooting with Wait Statistics
Speaker: Joe Sack, Microsoft

When encountering a performance issue on a SQL Server instance, where do you begin? DBAs sometimes use troubleshooting methods that are biased towards past experience, instead of using a disciplined and consistent methodology. The danger of using instinct-based methods is that you may overlook the true root cause, or spend time troubleshooting less significant bottlenecks. In this session, Joe Sack will walk you through how to troubleshoot performance issues by evaluating Wait Statistics, which represent waits encountered for executed threads on a SQL Server instance. Joe will also discuss common wait type patterns seen in the field, and describe recommended next steps and resolutions. Performance troubleshooting with Wait Statistics allows you to focus on the higher priority bottlenecks, saving you time, and allowing you to apply your efforts towards the most impactful changes.

Joe Sack is a dedicated support engineer with Microsoft Premier Field Engineering based in Minneapolis, Minnesota. Since 1997, he has been developing and supporting SQL Server environments for clients in financial services, IT consulting, manufacturing, retail and the real estate industry. He blogs about various SQL Server topics onhttp://blogs.msdn.com/joesack/ and is the author of the Apress books SQL Server 2000 Fast Answers for DBAs and Developers, SQL Server 2005 T-SQL Recipes, and most recently SQL Server 2008 Transact-SQL Recipes.

I’ve was reviewing wait stats the other day and kept finding this wait stat peaking out the other wait stats by a few hundred milliseconds.  Oh my!  Should I care or not… well long story short is that if all of the CLR assemblies are created in safe mode there is no problem.

Or at least according to SQL PSS who tend to know things…

If you have a SQL CLR application, you may notice that waits on CLR_MANUAL_EVENT and CLR_AUTO_EVENT from sys.dm_os_wait_stats are very high.

This is normal and can be safely ignored because these waits reflect internal CLR runtime waits under normal conditions.  Since SQL as a CLR host implements synchronization mechanism, it is able to track these kinds of waits and exposes them via DMV.

The only exception when you need to pay attention to these two wait types is that you actually use unsafe assemblies and use CLR Event objects (ManualResetEvent or AutoResetEvent) yourself.  In other words, if you use ManualResetEvent and you code results in waiting on the object, the wait time will also be reflected in CLR_MANUAL_EVENT type.  The same is true for AutoResetEvent which will be reflected in CLR_AUTO_EVENT type.  But using these two objects will require explicitly creating the assembly in unsafe mode. 

To summarize, if all your assemblies are created in safe mode and you see high waits on CLR_MANUAL_EVENT and CLR_AUTO_EVENT, you can safely ignore them.

Jack Li
Escalation Engineer