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.

I’ve blogged about certifications a number of times in the past.  One of the certifications that I really want to obtain in the future is the Microsoft Certified Master (MCM) for SQL Server 2008.  This is a great certification that really dives in deep and requires classroom training and labs as part of the certification process.  That classroom training and the labs equates to one great thing and that is knowledge.

Since I haven’t managed to convince anyone to send me to the MCM training yet, I was pleased today to find a list of pre-reading material for the MCM program.  It’s a 4-pages of reading goodness that I encourage everyone else to read.  I really think that this is a gem of a find and with this being so close to the New Year, I think I may have found my first resolution for next year.

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.

In case you missed the last reminder or hadn’t heard.  We are having a PASSMN meeting in December this year.  Check out the event details below and take a chance to learn new things with some of your fellow SQL Server professionals.

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.

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=0x03000a0089b9100d0e527800669c00000100000000000000CREATE PROCEDURE dbo.FooGetTableA    (    @Parameter varchar(4)    )AS

SELECT Column1 FROM dbo.TableAWHERE Column2 = @Parameter

GRANT EXEC ON dbo.FooGetTableA TO ApplicationRole    inputbufProc [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=0x03000a00043f146882564201a09b00000100000000000000CREATE PROCEDURE dbo.FooGetTableB    (    @Parameter varchar(4)    )AS

SELECT Column1 FROM dbo.TableBWHERE Column2 = @Parameter

GRANT EXEC ON dbo.FooGetTableB TO ApplicationRole    inputbufProc [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.

I got to work with deadlocks quite a bit recently.  There were quite a few interesting ones that came up that I had the chance to research.  Since I like easy, I’ll start with the one that I forgot to grab the deadlock details for.

Well, maybe not all of the details… in this case as the title states I was looking at deadlocks with the events exchangeEvent and threadpool.  I managed to come across a post from Bart Duncan that went through and deciphered this deadlock.  The long and the short of it… parallelism deadlocks.

Bart does a better job explaining this than I can do here, especially since I didn’t take the time to grab the deadlock details for review.  Maybe I’ll have that one the next one…

Fortunately, a large part of the issue that I was reviewing for the client had to do with parallelism and so solving this issue actually occurred as a side effect of dealing with parallelism issues.  But I will share my little secret that I used to resolve this and most of the parallelism…

Indexes!!

There I said it.  True, you can have too many indexes.  But no indexes is too few.  No clustered indexes can lead to too many scans.  I could pulpit here on indexes and making sure that you have them, but I’ll save that for another time.

Overall, I used Bart’s Workaround #1.  Hopefully this helps… direct you to a post that is more prescriptive.