Blogs

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.

I’d like to thank Pam Shaw (twitter as @pamshaw) and Jorge Nunez (twitter as @sqlchicken) for letting me come down to Tampa this past weekend to present at SQL Saturday #32.  Jeez, that picture doesn’t make me look very happy.  For someone like me that tends to forget to take time off, this is a great way to get out and travel and still feel like I’m being a workaholic.

The session I presented was ABCs of CTEs.  Here’s the description of the session:

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.

I put this session together a couple years ago when I realized I was behind the ball in leveraging CTEs into the T-SQL that I was writing.  And after asking around found there was a lot of confusion surrounding their use.  Hopefully, people that sat through the session go something out of it.

I’ve uploaded the slide deck to the SQL Saturday site but for those that just want the information now… here’s the presentation material: 

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.

With the next release of SQL Server, data analysis is going to go through a major change with the PowerPivot add-in for Excel and SharePoint.  If you haven’t been keeping up with these changes to Business Intelligence, SSWUG.org has an opportunity for you to get up to speed.  On January 22, 2010 there will be an online conference on PowerPivot with the following sessions:

• Getting started with PowerPivot
• Insight and oversight: the IT perspective on PowerPivot
• Power up your Pivots! Building sophisticated applications with PowerPivot
• Beyond the desktop: PowerPivot in your business infrastructure

Not only is the this a great opportunity to learn about some new features, but it is also a free event.  No excuses not to learn on this day…  hope to see you online.

We had our final meeting of the year earlier this month and while there was a snow storm, we still had over 20 people show up.  Not the greatest turnout but more than last year’s December meeting.  During which it also snowed… maybe someone is telling us something about December meetings.

New Board

I’m pleased that I will be serving on the PASSMN Board of Directors for the next year. <sarcasm> It wasn’t a very difficult election this year, there were five people running for the five open slots on the board. So I finished in both first and last place. </sarcasm>

To build on the momentum from last year, the board members that continued from last year are filling the same roles for 2010 that they did for 2009. Those board members are:

• Lara Rubbelke as our Microsoft Liaison.  Lara writes on SQL Server topics on her blog and tweets as SQLGal.
• Dan English is continuing as Chair. Dan writes on Business Intelligence topics on his blog and tweets as denglishbi.
• Eric Strom is continuing as Director of Technology.
• Jason Strate (that’s me) is continuing as Director of Program Development.  You can read my blog on SQL Server and related technologies through my feedburner feed or come straight over to my website.  Also, I tweet as StrateSQL and am the more frequent Board of Directors tweeter for PASSMN.  Feel free to contact me through e-mail if you need anything.

New to the board this upcoming year is:

• Gordon D’Souza as the Director of Membership/Treasurer.
• Mark Rocco as Director of Corporate Development. 

Hopefully this upcoming year, we’ll have more opportunity to bring education and events to our members.  As always, the PASSMN Board of Directors wants to hear from it’s members, potential speakers, and potential sponsors.  You can get a hold of us through any of the above, comments on this post, or just e-mail the board.

New Site

As one of the last things for wrapping up this year’s business, the Board of Directors completed our move from our existing user group site to a new web site.  The new site is hosted by PASS and allows us some great opportunities to coordinate our activities with the larger organization.  The direct URL to the new site is http://minnesota.sqlpass.org.  But to keep things easy, the old link re-directs to the new site as well.

If you haven’t been out there yet, take a look…

Some things that might be worth checking out on the new website:

• Meeting announcements can be found right under the Home menu.
• Upcoming meetings are listed on the Meetings page.
• The About menu has a Contact PASSMN option with an area to leave comments.
• The Discussion area has some generic forums along with an area for recruiters and candidates to discuss open positions
• Underneath Resources, logged in users are able to find content for the last couple years worth of meetings.  Going forward we’ll include session descriptions with each of the items to make materials easier to search for.

And as I mentioned above, if there is anything that should be added to the site.  Let us know we’ll work on getting it out there.

It’s a very good question. One that might not seem to insidious. Nothing that should be able to bring down the system and cause failures. Or will it?

I’ve been to a number of clients and done it myself before where I start to check out a stored procedure with some performance issues and sitting all pretty at the bottom is a GRANT EXEC statement. When I script out the stored procedure I get something similar to the following:

CREATE PROCEDURE dbo.FooGetTableA

    (

    @Parameter varchar(4)

    )

AS

SELECT Column1 

FROM dbo.TableA

WHERE Column2 = @Parameter

GRANT EXEC ON dbo.FooGetTableA TO ApplicationRole

GO

But if you look carefully, there is something missing, or one could say included that shouldn’t be.  Look again if you don’t see it. It’s hidden in plain sight.  The permissions for the procedure are included in the body of the stored procedure.  When the procedure was written, someone thought ahead to add permissions to the script but forgot the GO statement between the stored procedure

In a better world this script would have looked like this:

CREATE PROCEDURE dbo.FooGetTableA

    (

    @Parameter varchar(4)

    )

AS

SELECT Column1 

FROM dbo.TableA

WHERE Column2 = @Parameter

GO

GRANT EXEC ON dbo.FooGetTableA TO ApplicationRole

GO

It’s Just a Permission Statement

Who cares, right?  So you are assigning some permissions every time that procedure executes.  What harm could possibly come of it.  I’ve seen this so many times and usually it’s one of things I’ll point out and say, “oops, you should take care of that”.  When I should be saying, “yeah, fellas.  You’ve got a time bomb there waiting for your business to take off.”

And the time bomb is deadlocks.  Completely preventable deadlocks.

If you have procedures that grant themselves permissions, then as the volume of activity in your database increases you may start to see deadlock graphs similar to the following:

deadlock-list
 deadlock victim=process30108bac8
  process-list
   process id=processec55dd68 taskpriority=0 logused=0 waitresource=METADATA: database_id = 10 PERMISSIONS(class = 1, major_id = 219199881) waittime=15000 ownerId=746424569 transactionname=Load Permission Object Cache lasttranstarted=2009-10-22T23:06:59.287 XDES=0x3712a8e98 lockMode=Sch-S schedulerid=1 kpid=5832 status=suspended spid=157 sbid=2 ecid=0 priority=0 transcount=1 lastbatchstarted=2009-10-22T23:06:59.287 lastbatchcompleted=2009-10-22T23:06:59.280 clientapp=.Net SqlClient Data Provider hostname=PRDWB0111 hostpid=5640 loginname=portaluser isolationlevel=serializable (4) xactid=746424394 currentdb=10 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
    executionStack
     frame procname=AdventureWorks2008.dbo.FooGetTableA line=1 sqlhandle=0x03000a0089b9100d0e527800669c00000100000000000000
CREATE PROCEDURE dbo.FooGetTableA
    (
    @Parameter varchar(4)
    )
AS

SELECT Column1 
FROM dbo.TableA
WHERE Column2 = @Parameter

GRANT EXEC ON dbo.FooGetTableA TO ApplicationRole
    inputbuf
Proc [Database Id = 10 Object Id = 219199881]    
   process id=process30108bac8 taskpriority=0 logused=0 waitresource=METADATA: database_id = 10 PERMISSIONS(class = 1, major_id = 1746157316) waittime=2125 ownerId=746479249 transactionname=Load Permission Object Cache lasttranstarted=2009-10-22T23:07:12.180 XDES=0x3786c61c8 lockMode=Sch-S schedulerid=3 kpid=4048 status=suspended spid=69 sbid=2 ecid=0 priority=0 transcount=1 lastbatchstarted=2009-10-22T23:07:12.180 lastbatchcompleted=2009-10-22T23:07:12.167 clientapp=.Net SqlClient Data Provider hostname=AMBER hostpid=568 loginname=portaluser isolationlevel=serializable (4) xactid=746372404 currentdb=10 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
    executionStack
     frame procname=AdventureWorks2008.dbo.FooGetTableB line=1 sqlhandle=0x03000a00043f146882564201a09b00000100000000000000
CREATE PROCEDURE dbo.FooGetTableB
    (
    @Parameter varchar(4)
    )
AS

SELECT Column1 
FROM dbo.TableB
WHERE Column2 = @Parameter

GRANT EXEC ON dbo.FooGetTableB TO ApplicationRole
    inputbuf
Proc [Database Id = 10 Object Id = 1746157316]    
  resource-list
   metadatalock subresource=PERMISSIONS classid=class = 1, major_id = 219199881 dbid=10 id=lock4153ec880 mode=Sch-M
    owner-list
     owner id=process30108bac8 mode=Sch-M
    waiter-list
     waiter id=processec55dd68 mode=Sch-S requestType=wait
   metadatalock subresource=PERMISSIONS classid=class = 1, major_id = 1746157316 dbid=10 id=lock415451780 mode=Sch-M
    owner-list
     owner id=processec55dd68 mode=Sch-M
    waiter-list
     waiter id=process30108bac8 mode=Sch-S requestType=wait

Breaking It Down

When I first started looking at these there are a few things I noted right away:

1. The procedures were access completely different tables with no common objects between them.  In the sample above there is TableA and TableB and no relationship.
2. Looking at each of the processes in the deadlock both of them have the following attributes
   1. waitresource=METADATA: database_id = 10 PERMISSIONS
   2. transactionname=Load Permission Object Cache

So nothing in common and a deadlock on a metadata resource for permissions.  This made me start to re-think how the two procedures were related.  With a metadata resource wait, there seems to be an issue above the data in the table.  Since both procedures point to the Load Permission Object Cache, maybe there is an issue there.

If you take a look, each of the procedures has a GRANT EXEC permission statement in it.  This is the area of commonality and where the two executions deadlocked.  Removing the GRANT EXEC permissions statements stop this deadlock from occurring.

After going through and removing these permission statements from a number of procedures that had this issue, all of the deadlocks with these types of issues disappeared.  And it is smooth sailing once again.

Cautionary Tale

Hopefully this is a scenario that only I’ve run into.  But if it’s not then this should serve as a reminder that little details that seem like a little non-issue, could be the crack that breaks the damn when there’s enough water behind it.  The thing that gets you on this issue is that it isn’t until execution start to really grow before it pops out and it will only hit when you’re the busiest.  This is something I’ll be keeping an eye out for in the future and I’d recommend the same for others as well.

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.

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…