Hopefully everyone caught the news that the November PASSMN meeting was cancelled.  We had some scheduling conflicts and things didn’t come together as expected.  But to make up for that, we’ve managed to snag a speaker and room for the beginning of December.

Don’t Wait! Consolidate on 2008!

December 8th
5:00 PM Networking & Social
5:15 PM – 5:30 PM Meet the new PASSMN Board
5:30 PM – 7:00 PM Presentation

8300 Norman Center Drive
9th Floor
Bloomington, MN  55437

Don’t Wait! Consolidate on 2008!

Speaker: Rick Heiges

SQL Server 2008 introduces features such as Resource Governor, Compression, Virtualization, and Policy-Based Management that help organizations consolidate to keep TCO down. A discussion of features since 2000 that enable consolidation along with the limitations of each will be presented. Real-world stories will enhance this discussion. The session will also encompass SQL Server on Virtual Machines.

Rick Heiges  is a SQL Server MVP and a Sr. Solutions Consultant with Scalability Experts working with customers on solutions within the SQL Server environment and educating users on SQL Server by speaking at user groups, conferences, labs, and road shows across the USA, Europe, and Africa. He holds an MBA and a M.S. in MIS as well as a B.S. in Computer Science. In addition, he also has attained MCTS:SQL2005 and MCITP:DBA certifications. His career has dealt with a wide array of job responsibilities including professor, developer, trainer, database administrator, and project manager/leader. Rick has been involved with PASS since the spring of 2001 and started up an Official PASS SQL Server user group in North Carolina as well as authoring several articles on for the PASS technical journal, the SQL Server Standard. Rick has served on the PASS Board of Directors since January 2003. You can read his blog at www.sqlblog.com and contact him at rheiges@scalabilityexperts.com.

Show Up And Network

I’d like to encourage everyone to come down and meet someone new at the user group meeting this month.  Walk up to someone you’ve not met before and introduce yourself.  Share a business card.  Make a contact.  Who knows!  The next person you meet just might be a new client or have the answer to solve your next production issue.

Also, with the regular monthly swag, we’ll be giving away CDs from PASS Summit 2008.  This is a library of over 160 sessions covering database administration, development, business development and professional development.  A huge resource of materials that I personally get a lot of benefit out of.

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.

A few times this past week I heard the quote, “Hi, Eric Stratton, Rush chairman, damn glad to meet you”.  That line kind of summarizes what the PASS Summit was like for me this year.  It’s been like coming to a week long rush event for the best fraternity in the world.  This fraternity isn’t the Delta Tau Chi’s though, it’s the PASS organization.

The PASS Summit was a chance to look at upcoming features in SQL Server, share learning in nearly 170 presentations, and networking with peers from across the street and the other side of the world.  It wasn’t always serious and one night at the karaoke bar a fellow DBA Rick Rolled us.

A Great Change

This year was drastically different though (for me at least) – and I believe it has everything to do with social networking.  It started a bit last year, but now with the flood of SQL Server professionals on twitter it seems to have matured quite a bit.  From the first day I was at the conference, I was recognizing people that I’ve shared conversations with on Twitter.  And I didn’t have deal with the nervousness of meeting them for the first time, since Twitter and Social Networking had taken care of that.

Last year at the PASS Summit, I think I met about a dozen new people.  Most of the people I talked to were just people I had met through out that year.  They were a good group of people, but I mostly just continued to develop those relationships.

This year though, I think I met about a hundred people that I hadn’t met before.  True, I had talked with a number of them prior to PASS through Twitter.  But I hadn’t actually met them.  And let’s be honest, I’m just this guy from Minneapolis.  I’m not someone who’s written books or worked on the DBCC commands.

What I am getting at here is that anyone can be a part of the community.  It’s a matter of joining in and becoming a part of the conversation.  And with Twitter anyone can join the conversation at any time.  Or you can just listen, but you’ll eventually join in… it’s too tempting.

Doing It Wrong

I’m going to say it.  I’ve said it to others and I’m going to say it here.  If you are a SQL Server professional and you are not on Twitter yet, you are doing it wrong.

If you are at a company and you are the lone DBA. *POOF*  No more.  Hundreds of others in your same boat, dealing with the same issues are there for you.  If you’re at a large organization dealing with hundreds of servers and run across a new issue that no one at your company has seen.  *POOF* I bet someone out on Twitter has and they might be able to help you through the answer.

I can’t stress the benefits of social networking enough.  It really gives you a door into the office of hundreds of other SQL Server professional who can help you solve problems and give you a sense of community with what you are doing.  And when it comes to the PASS Summit, it’s a way to find out where people are going, what they are doing, and how to get around town to hot spots that others are checking out.

I hope this little unwinding from PASS helps you decide that you need to get on Twitter if you are not already.  And also helps you reconsider the PASS Summit if you haven’t gone to it yet.  I have more thoughts on PASS that I’ll be posting later.

You can register already for next year…