So I did. Other people have different motivations for certification. I’m not sure if I would have gotten these without some prompting. But on the other side, now that I’ve gotten it, I’m glad I did.

But the preparation for these exams meant a ton of studying. I’m pretty confident in my skills, but I realize that the happy ending was not guaranteed. So that meant work. If I was going to fail, it wasn’t going to be because I didn’t prepare enough. (Remember that in the Hare and the Tortoise the real moral of the story is not slow and steady… but actually don’t get cocky).

So I was nervous. I went into the test and started answering questions. About 15 questions in I realized that I was going to be okay and I relaxed a bit. I finished with time to spare and reviewed the questions I was fuzzy on. I clicked submit and BAM, I passed with a score of 914. They tell you your mark immediately.

Celebrating

In an article of his, Brent Ozar reminded me recently about Scott Adams’ champagne moments. In that article he talks about how important an event has to be before it’s worth popping open a bottle of champagne. Like Brent, I have a low threshold for these “champagne-worthy” moments and Monday was one of those days.

I was surprised at how glad I was to get the certification. And that’s worth recognizing.

The Study Guide I Used

So I created a study guide for myself earlier and what’s shown here is a straight dump of that; I include it as-is. That is, I don’t provide links and some of the resources you might not have access to.

I started out making the study guide a list of things to do, and not a list of things to learn. (Just like homework or an assignment).

Nested transactions, using XACT ABORT, Nesting levels, (damn, where’d I put my totem). Dive deep into these. Which severity of error is enough to kick you up a level? I know how TRY CATCH behaves, is this affected by aborted transactions?

Solving concurrency problems are a large part of troubleshooting. Often solutions include tuning the blockers to minimize the blocked time or tweaking locks and isolation levels to make processes play nicely with each other. But to dig into the problem, you have to understand the blocking chain.

If you’re troubleshooting a concurrency problem that’s happening on your server right now then you can get information from the DMVs or even better, by using Adam Machanic’s Who Is Active stored procedure.

But what if the excessive blocking behavior is intermittent? Then the best strategy is to monitor the server and try to capture a SQL trace that includes the “blocked process report” event. I’ve had a lot of luck with that event, it can really tell you a story about excessive blocking. But I find that interpreting the trace can be tricky, especially when there’s a large blocking chain. Sorting through hundreds of events to find the lead blocker is not fun.

New and Free: sp_blocked_process_report_viewer

So I wrote a script! And I stuffed it in a stored procedure! Here’s the syntax (BOL-Style):Syntax

I’m Promoting This Script to a Project

Although, you still have to know how to read a blocked process report, this utility makes the structure of the blocking chain clear. I find this script useful for my own purposes. In fact I like it enough that I’m going to maintain it on github as: SQL Server Blocked Process Report Viewer

Let Me Know How It Goes

Run the script! Use it! Tell your friends. Tell me what you think of it (for once in my life, I’m seeking out criticism).

Going forward, I do have some plans for the script. There’s a number of things I eventually want to do with it:

Add error handling

Really make the sproc perform well (it’s already decent).

Develop a test suite (sample traces that exercise the procedure)

There’s an opportunity to look up object names, index names and sql text based on object ids if the traces belong to the local server.

A SQL Server Management Studio plugin. A treeview control would really be useful here. (This might be easier after Denali comes out)

I plan to do the work, but if you’re really keen and you want to pitch in, you’re welcome to. If you see any errors you can

And if you care, this also means that the Microsoft Certifications that are tied to that product also expire. At least that’s what I gather from this Microsoft FAQ which says:

Q. How long will my certification be valid? A. Today, most of our Microsoft Certified Technology Specialist (MCTS), Microsoft Certified IT Professional (MCITP), and Microsoft Certified Professional Developer (MCPD) exams retire when Microsoft discontinues mainstream support for the related technology…

But that’s just the exam, I’m not too sure about the certification. In any case it really doesn’t matter at all. I mean, the skills a person has with SQL Server does not diminish or become obsolete with the expiration of SQL Server 2005. SQL Server 2008 and other versions are still alive and kicking.

It was a decent exam, I thought it was fair. There were a couple of things I would have changed:

I found at least four typos in the questions and at least once I had to answer the question they meant to ask not the question they actually asked.

Two thirds of the way through the exam, there was a power outage. No one could have foreseen it, but I was glad to discover that my questions weren’t lost and the time without electricity was not on-the-clock.

Section 5, My Nemesis

I did well in every section except one, Working With Additional SQL Server Components. In that section I got two questions correct out of six. I guess I should have given more focus to it while studying. To be honest, I didn’t actually work the plan I wrote out for myself for that section. I had heard scary things about some of the other sections and focused on them. But even so, two out of six is pretty rotten for a multiple choice test. Random guesses might have even fared better. A lucky monkey has a good chance (46%) of doing at least as well or better than I did simply by throwing darts randomly as a strategy for choosing answers.

My 70-433 Study Plan

Of course I can’t give details about the content of the exam for obvious reasons, but I do want to share the study plan I followed when studying for this exam. I wrote the plan based on Skills Measured as described by Microsoft. Everything I write here can be found online:

Tables and Views

What does with CHECK OPTION mean when creating views

What does with ENCRYPTION mean when creating views

what’s the syntax for fill factor?

write (without checking syntax) a nci with fill factor

rebuild an index (without checking syntax) with a fillfactor

create statistics without checking syntax

add a pk (without checking syntax)

add a fk with cascading deletes

disable/enable pk

disable/enable check

disable/enable fk

disable/enable unique

Create table with column that has filestream

create a spatial column (without checking)

What’s a structured vs semi-structured column?

name collation varieties (case sensitive etc…)

Create partitioned table (without checking syntax)

Split merged table into two, merge back together

Create empty table and switch into partition

Programming objects

Without checking syntax, pass in a tvp

without checking syntax, write sprocs with all varieties of “Execute as”

Without checking syntax, create sproc that specifies RECOMPILE

Without checking syntax, create sproc that specifies WITH ENCRYPTION

What permissions are there on sprocs? 2 flavors of security here: GRANT, DENY, REVOKE versus EXECUTE AS

Build Function without checking syntax

What permissions are there on udfs? flavors of security here: GRANT, DENY, REVOKE versus EXECUTE AS

April 6, 2011

Intro

In this article, I want to explain a pattern that I like to use whenever I model something, it’s fairly straightforward. It involves using identity columns primary keys for all object tables and compound keys for all relationship tables. Surprisingly, even with these restrictions, there’s still a lot of flexibility possible in the data model.

In fact, I conducted an exercise at work where colleagues designed a database model based on this rule of thumb. (The exercise was like pair programming but for db design, ask me about it some time). Attendees were given the instructions: “Identity columns for objects, compound keys for relationships” and I found that there was still a large variety of designs.

For example, ask yourself how you would model a SHIFT table (as in scheduled work)? Is it an object, or is it a relationship? It sounds like it’s own object, but it can also be thought of as a relationship between employee and time slot.

Example

Imagine you want to track the new sport Synchronized Bulk Loading (the sport will make its premier at the 2012 SQLympics in London).

It’s a judged sport and we want to track the marks given by judges to various performances. Here’s what an application might display:

A mockup of a report, or a sample screen shot

For this example I want to capture (at a minimum) the following information

Events (Sport, Location, Date)

Judges (Name, Country)

Atheletes (Name, Country)

Scores (Score)

Every time I see a two dimensional chart like this, I look to see whether it’s appropriate to use this pattern.

I see this pattern a lot.

And so in this case I would get:

Simple diagram for this example's schema.

I want to compare two different schemas, The first one is my preferred one. It uses identity columns only for object tables (not relationship tables). The second one uses identity columns for everything.

Why I like A over B

Schema A uses identity columns for only JUDGES, EVENTS and ATHLETES. It uses natural keys for every other table. By contrast Schema B uses identity columns for every table, even the relationship tables. I like Schema A over Schema B for a number of reasons. Here are a couple of them:

1. Business Constraints seem easier to enforce.
We get some of this data integrity almost for free! For example, we get to enforce the business rule that scores apply to judges and athletes who are participating in the same event. It is impossible for some rogue application to insert a score that applies to a judge and an athlete who never participated in the same event.

/* Due to unprecedented use of steroids and bribes, delete all marks for an event */
DECLARE @EventIdToDelete INT = 42;
DELETE FROM A.SCORES WHERE EventId = @EventIdToDelete;
DELETE FROM B.SCORES
WHERE EventAthleteId IN (
SELECT EventAthleteId
FROM B.EVENT_ATHLETES
WHERE EventId = @EventIdToDelete)
OR EventJudgeId IN (
SELECT EventJudgeId
FROM B.EVENT_JUDGES
WHERE EventId = @EventIdToDelete)

4. Ready for partitioning
By using natural keys, In Schema A we have the option of using a column such as EventId as a partitioning column on all tables. You can’t say the same for Schema B.

But It Seems I’m Arguing Against Myself

It’s true. It does seem like I’m setting up a straw man just to knock it down again. But I see designs like Schema B often so the example is not far fetched. And if something like Schema B makes it into production systems, it gives the that schema an enormous amount of inertia because at that point “refactoring” the schema will cost a lot (in time, money, you name it).