Strate SQL

If you’ve got the time, stop in to socialize and learn a bit about SQL Server.  Kalen Delaney will be in town teaching a class over at Benchmark Learning and has offered to join the meeting to talk about Simple Recovery Model.   Also, Barbara Rokke has offered to share  some of her experiences with partitioning in SQL Server.

Here are the meeting details:

Please click here for more meeting details and to RSVP.

Case Study: A Partitioning Strategy for a VLDB

Barbara Rokke, 3M

With our database growing rapidly from MB to TB, maintenance tasks locking users out of data access and a 7×24 data access requirement by the clients, the DBA team needed to come up with a better way of managing the large database. Partitioning was discussed however the DBA team didn’t have time to develop a manual partition strategy. With the release of SQL Server 2005, partitioning became a viable option. After discussions with Microsoft experts and reading every bit of information available on the web (only 2 websites offering valuable information at the time), the DBA team took the leap into the Partitioning world. Partitioning was installed and a two year window processing script was developed to maintain a rolling window of data.

This presentation will provide a review of the thought process and the implementation of the partitioning strategy the DBA team developed. Also a walk-through of the two year window processing to show self-maintenance partitioning will be reviewed.

What is Minimal Logging?

Kalen Delaney

There is a common misconception that Simple Recovery Model means no logging, and this is a very dangerous myth to propagate. SQL Server does log database changes in Simple Recovery, but some (not all) operations are minimally logged. In this session, I’ll discuss what exactly minimal logging means and what the benefits and dangers of Simple Recovery model are.

A while back, I noticed that Paul Randal (on twitter as @paulrandal) had started a new blog meme on three events that brought their life to where it is today.  I’ve skipped a lot the past year’s memes due to time constraints but thought I’d chime in on this one and have a go at it.

First I Was A DJ

Back when I was an extremely shy version of current myself, I had gone to a wedding.  The DJ sucked.  The musical choices were poor.  Songs were cut off in the middle of play for something new.  Not a fade to something better, but an abrupt stop and reversal of the entire tone of the moment.  And the equipment had no sound quality what-so-ever.  In fact, the DJ sucked so bad that I was convinced I could have done a better job.

As I was "armchair quarterbacking" the DJ the following week, I said that I should do that.  If she could, then I definitely could.  And the I was approached by a co-worker who asked if I started a company if I would DJ her wedding.  After thinking about it a bit, I decided to take her up on the offer.

The biggest problem after getting her money and contract was a general lack of any of the required infrastructure.  I worked out a deal with my uncle to purchase the necessary speakers, mixers, and lights.  And then I started shopping for CDs.  Within a couple months, I was approaching nearly 1,000 CDs and had a new problem.

How do I make music  lists without a lot of work?  How should I catalog the music?  How will I find the next song to play when the current song is playing?

That led me to take a look at the programs on my computer and I found one called… Access 2.0. I wasn’t really sure what it was, but it seemed that it could do what I needed.  I sat down and after hundreds of hours of tinkering over the years, I built a fairly robust database that I could use to manage and organize music for my business.

And I was officially a database hack.  I will qualify, I was a wedding DJ.  I did not have two turntables.  But I did have a microphone.

Then I Sucked At Credit Analysis

When  I was a database hack, I hadn’t actually believed anyone could make money or have a career with computer-machines.  So instead, I pretended to go to college and worked as a server.  Somewhere in there I decided to pick up a temp job and started at a bank working on entering credit card applications.  Oh yeah!  Data entry.

I rocked at the data entry and was soon moved off of that to reviewing individual credit cards.  And then off to corporate credit using  the internet to try and research the background and news happening with the bank’s customers.  One of the greatest lessons I learned at this point was that almost everything was on the internet, it was just a matter of submitting the right query.

Doing good at your job can have it’s curses.  I was soon asked to move to another group to do credit analysis within the corporate credit department.  I didn’t have a background in finances but I was young and willing to do the work in front of me.  Long and the short of it, I sucked at credit analysis.  I was so clueless as to what I was doing and trying to fake it while I tried to learn what I needed to know.  I was in over my head and ready to quit.

One of my old managers recognized this and remembered when I was stellar and offered to bring me over to her group rather than my current manager letting me go.  My desire to quit had been a little bit slower than the current managers desire to fire me.  Fortunately, though, before either happened I was moved to a merchant fraud risk group.

The nice thing about this group is that I eventually got a chance to highlight my database hack skills.  They had an Access database that they used for uncovering merchant fraud.  More specifically, they had an Access database that 100% mimicked a mainframe application and was as user friendly.  They even managed to make it uglier than its predecessor.
One thing led to another and when I left this department, I was a SQL Server developer.  The Access database now had a SQL Server backend.  And I was heading to a data warehousing group to write DTS packages.

I Got Divorced

Throughout my time at the bank, I had a number of life changing events that occurred.  Some of the highlights were getting married and having four children.  And some of the lows were my daughter and my best friend from my college years passing away.  I worked a lot and didn’t pay attention to my spouse or children nearly as much as I should have.  And really wasn’t having a good time with life.  All of this culminated in getting divorced.

For many divorce is a low point, and I’ll admit to having some bad days after it started.  But as a whole it was one of the best decisions of my life.  And really lead to a lot of success, happiness, and a chance to be a better father to my children.

It gave me an opportunity to re-think my life and career.  This was when I decided that I should control my career and not just let it happen to me, which is when I started blogging and started a new job at Digineer as a consultant.  I also decided to stop putting off things that I wanted to do and live my life in a way that I was satisfied with every day and what I did with it.

Since then I’ve had a lot of fun with my kids.  Enjoyed life and where it is going.  And I can once again count my ex-wife as a very good friend.  Life is good.

Figured since I helped write the meeting announcement and newsletter for PASSMN this month that I’d just copy most of the details. Since last month we’ve moved from our old host to a new host, so the RSVP/registration process has changed a bit. Hopefully, there aren’t any hurdles but if so please contact me and I’ll help anyone that needs to get registered.

Location: 8300 Norman Center Drive, 9th Floor, Bloomington, MN  55437
Date: January 19, 2010
Time: 3:00-5:30

Please click here for meeting details and to RSVP.

SQL Azure

Sarita Garg, United Health Group

This session will answer some of the following questions about new state of the art technology  SQL Azure from Microsoft

• What is SQL Azure?
• Why to use it?
• How does it work?
• How the pricing and licensing will work?
• Role of DBAs, Developers with SQL Azure
• Future of SQL Azure

SQL Server 2008 R2

Jin Cho and Lara Rubbelke, Microsoft

Some of the details are still being flushed out on this presentations, but this will be covering the items below with some demos of these features.

• Overview/Highlight of SQL Server 2008 R2 (new features)
• PowerPivot – Excel Add-in, deployment to SharePoint, IT Management

Other News

Live Meeting

• Beginning with the January 2010 meeting, you will now be able to join monthly meetings virtually through Live Meeting. This is a great opportunity to stay connected that has been provided to us by PASS.

PASS Summit 2010

• Registration for PASS Summit 2010 is now open. Register for only $995 until Jan 15. Tap into your 2009 or 2010 training budgets to take advantage of the lowest rate for PASS Summit.

I’ve been selected to speak at the Tampa SQL Saturday coming up at the end of January. I’m a bit pumped to be going down there since I woke up this morning to below zero weather for yet another day. Not that I’m complaining, but my feet could definitely use a little bit of heat. I know it’s a bit chilly down there right now, I heard it was 37 degrees. But let me tell you… that would be awesome right about now.

Here is the topic that I’ll be speaking on:

ABCs of CTEs

Common Table Expressions (CTE) aren’t as common as their name implies. CTEs are often seen as a secret part of the dark art of recursion. This session will explore CTEs to show how they can be extremely useful in improving performance and legibility of T-SQL code. And, of course, we will look at their use in returning recursive data.

Prerequisites

• Understand of basic T-SQL coding

Goals

• Provide an understanding of the purpose and use of CTEs for recursion and derived tables
• Define the pro’s and con’s to using CTEs in recursion and for derived tables
• Demonstrate real-world examples detailing methods for using CTEs to solve coding challenges

Hopefully if you are in the area, you’ll stop by and listen. If you want to, feel free to make fun of my lack of sun from the long, cold Minnesota winter. And now that I’ve purchase flight, hotel, and the rental… I’ll be there for certain.

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

And then there is the wait types and categories.  Since even WordPress thinks this is too much to add to a post, here is a script with all of it together:

Categories: DBA, DBADiagnostics, Performance Tuning, SQL Server, SQL Server 2005, SQL Server 2008, SQLServerSyndication, Wait Stat, Wait Type  |  Tags: DBA, DBADiagnostics, Performance Tuning, SQL Server, SQL Server 2005, SQL Server 2008, SQLServerSyndication, Wait Stat, Wait Type  |  5 Comments