Michael J. Swart

April 27, 2011

Woo Hoo… Microsoft Certification Exam 70-451

Filed under: SQLServerPedia Syndication,Technical Articles — Tags: , , , — Michael J. Swart @ 12:00 pm

So I passed the Microsoft Certification Exam 70-451. I know, I know. This blog post seems like a rerun. Wasn’t it only two weeks ago that I passed this exam? What gives?

Actually no. Two weeks ago, I passed the 70-433. That was a prerequisite for this one.  To recap:

  • Pass the 70-433 exam and you’re a MCTS (Microsoft Certified Technology Specialist) in SQL Server 2008 Database Development.
  • Pass the 70-451 exam and you’re a MCITP (Microsoft Certified IT Professional) Database Developer 2008.

(As long as those titles seem, it’s not longer than the name of my bachelors degree written out. Ask me about it some time).

My Experience

The motivation behind getting the certification was simple. I was asked to get it for work:

I was asked “Can you get the cert in a month?”

“Umm… let’s see. Yep, the testing center has seats available, I’ll give it a try.”

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.

Two champagne glasses captioned: "I have my moments... I recognize them when I can"

 

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).

Designing a Database Strategy (13 percent)

  • Create SQL Server agent jobs, steps & schedules (through ui and sp_)
  • Set up DB Mail and use. Notify self when a job is done.
  • Create a linked server (through ui and sp_)
  • Review normalization articles
  • Create an application that uses the Entity Framework
  • Security Strategies. Put the following terms somewhere into security triples: (prinicpal, object, permission)
    • Application Roles
    • Schema Ownership
    • Execution context
    • Window vs SQL Authentication
    • Permissions and DB Roles
  • Security Strategies.
    • Create and implement a demonstration of the ideas listed above, both with the UI and with t-sql.
  • Service Broker
    • Go through tutorial, review rusanu’s asynchronous job service broker.
    • Look through following ideas for ideas not yet covered
      • Services,
      • Contracts
      • Activation
      • Routes
      • Message types
      • Queues
      • Remote service binding
      • Priorities
    • For ideas not yet covered. Create and implement a demonstration of the ideas listed. Both with the UI and with t-sql

Designing Database Tables (16 percent)

  • Implement and use data types from C# application
    • Geography: Implement an app that tells you how close the nearest x is.
    • Geometry: Review “Birth of Venus”
    • HierarchyId: Review
      • Can you get value of current node?
    • Date, time, datetime2, datetimeoffset
      • Watch Kendra Little’s 24HOP session on dates.
    • Varbinary (max) (Wasn’t this around in 2005?)
    • Filestream ughh…
  • Size the data types. Review each.
  • In row data blobs, Create a table that stores data in row by default, and out of row by default. (What is the unspecified default?)
  • Create some persisted computed columns.  Review Determinism, precision etc…
  • Data integrity ,Enforce some constraints the following way:
    • Dml triggers,
    • Pk
    • Fk
    • Check
    • Unique
    • Null/not null
    • Default (???)

Designing Programming Objects (17 percent)

  • Stored Procedures
    • Create procedure exercising different execution contexts (EXECUTE AS)
    • Review TVP example, Also create sproc to pass tables between tsql and sproc
    • Write sproc that returns data (using RETURN?), using OUTPUT param, using result sets. Are there others?
    • Look at interaction of RECOMPILE (table level vs. sproc level)
    • Look for Error handling examples, start with Rusanu’s asynch script. Look for TRY CATCH examples. Update sp_blockedprocess* with error handling
  • Views
    • Create views again, CTEs
    • What’s a partitioned view.
    • Review with check option again (it means updating views shouldn’t violate the view criteria)
    • Read BOL on WITH SCHEMABINDING
  • Functions
    • What permissions are there on udfs? flavors of security here: GRANT, DENY, REVOKE versus EXECUTE AS
    • What is schemabinding on udf?
    • what are the three (four?) kinds of functions
    • Review  inline table-valued functions vs. views, multi-statement table-valued functions, determinism \
  • CLR Stuff (This was my favorite part of studying if not the most useful)
    • Implement a regex “match” in CLR
    • Implement “Concatenate” aggregate in CLR
    • Implement “first” aggregate in CLR (if possible)
    • Impelement “times table” sproc in CLR (gives times table as resultset from 1 to 12)
    • Explore PERMISSION_SET.
    • Implement Regex as a clr UDType
  • Look out for Bobby Tables:
    • QUOTENAME,
    • PARSENAME,
    • too bad we can’t use sys.fn_quotefourpartname
    • Especially look out for EXEC or sp_executesql… distrust any parameter values!

Designing a Transaction and Concurrency Strategy (14 percent)

  • Locking granularity levels (Something I’ve always avoided in the past.)
    • Review the different locking granularity hints.
    • Understand memory consumption of each.
  • Implicit transactions vs explicit transactions
    • 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?
    • Save points, (review sudoku solution)
    • Use SET IMPLICIT_TRANSACTION ON setting
  • Concurrency.
    • Read Kendra’s poster
    • Review my own series.
    • How do are these affected by rowversion datatype?

Designing an XML Strategy (8 percent)

  • Watch MCM video on xml by Bob Beauchemin

Designing Queries for Performance (17 percent)

  • Optimize and Tune
    • Review what’s sargable
    • Look at temp storage (find a definitive blog post on difference between temp table and table variable.
    • Find Itzik’s posts on sqlmag.com for GROUP BY  (grouping sets, cube, rollup)
  • Execution plans
    • Review my post on table access operators
    • Look at join operators,
    • Review those fuzzy ones (table spool, parallelism, others)
  • Row based vs Set based operations
    • Enumerate as many non-set based things (cursors, WHILE),
    • Do ctes count? or subqueries in SELECT, WHERE clauses count as non-set based?
    • Look at batching methods, splitting implicit transactions (this is Rowcount(), top(x), while)

Designing Queries for Optimal Performance (15 percent)

It was at this point in creating my study guide (the last section!) that I discovered Eric Wisdahl (t|b) had created his own study guide earlier this year.

I have no better advice in this section than to point you to his posts http://ericwisdahl.wordpress.com/tag/70-451/.

I found those posts very valuable and I want to end this post by saying thank you Eric!

April 20, 2011

A New Way to Examine Blocked Process Reports

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

sp_blocked_process_report_viewer [@Trace = ] 'TraceFileOrTable'
    [ , [ @Type = ] 'TraceType' ]

Arguments
[@Trace = ] ‘TraceFileOrTable’

    Is the name of the trace table or trace file that holds the blocked process reports

[@Trace = ] ‘TraceType’

    Is the type of file referenced by TraceFileOrTable. Values can be TABLE, FILE or XMLFILE. The default is FILE

Download it Now!

Go to the http://sqlblockedprocesses.codeplex.com site and download it. Once you’re there, click on the big green download button (as shown to the right) and you’ll have the stored procedure!

Here’s a sample output. It shows clearly who the lead blocker is:

A screenshot showing output for this sproc

Nicely organized, at least better than usual

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 codeplex 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

April 13, 2011

Whew… Microsoft Certification Exam 70-433

Filed under: SQLServerPedia Syndication,Technical Articles,Tongue In Cheek — Tags: , , , — Michael J. Swart @ 12:00 pm

Yesterday was April 12, 2011, and as far as I know mainstream support for SQL Server 2005 ended. See Aaron Bertrand’s A Quick Note About SQL Server 2005 Mainstream Support.

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.

Bad News: So my MCTS (Microsoft Certified Technology Specialist) certification for SQL Server 2005 expired yesterday (don’t worry I’ll get over it).
Good News: My MCTS certification for SQL Server 2008 takes effect last Friday when I passed the 70-433 exam (yaay!!) It’s my first step towards a new certification for myself, the MCITP (IT Professional).

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.

A picture of a monkey with holding a dart.

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
  • What is schemabinding on udf?
  • what are the three (four?) kinds of functions
  • Explore flavours of triggers.
  • Create trigger that uses INSERTED/DELETED
  • Explore security on triggers, (EXECUTE AS)
  • Create DDL trigger.
  • Create DDL trigger that returns data.
  • Create CLR assembly.
  • Explore clr: SAFE/UNSAFE/EXTERNAL_ACCESS
  • Explore CLR: set trustworthy
  • Implement TRY CATCH RAISERROR
  • error handling, look at Rusanu’s service broker scripts, he’s an awesome error handler.

Query Fundamentals

  • Explore different DELETE syntax (i.e. DELETE A FROM A…)
  • Without checking syntax, use the OUTPUT clause
  • Without checking syntax, use the MERGE statement
  • Write a query that uses grouping sets.
  • What’s the difference between LEN and DATALENGTH
  • without checking syntax, use patindex and charindex

Additional Query techniques

  • write a subqueries (correlated)
  • write a subqueries (simple)
  • write a subqueries (scalar)
  • write a subqueries (list)
  • write a subqueries (table valued)
  • Without checking syntax, Use rank
  • Without checking syntax, Use dense rank
  • Without checking syntax, Use row_number
  • Without checking syntax, Use ntile
  • Without checking syntax, Use row_number, and partition by
  • look through table hints to understand any unused ones
  • look through query hints to understand any unused ones
  • manage collations? how can they be managed.
  • what’s the diff between column/db/server collations
  • (international) define custom errors

Other SQL Components

  • Set up db mail
  • implement full text search
  • full text: Use CONTAINS
  • full text: Use CONTAINSTABLE
  • full text: Use FREETEXT
  • full text: Use FREETEXTTABLE
  • full text: Use STOPLIST
  • Use powershell and smo to create .net cmdlet demonstrates smo
  • Take service broker tutorial, compare sample syntax to rusanu’s syntax
  • Implement change tracking
  • Create db audit spec
  • Use CHANGETABLE

XML Data

  • use each FOR XML type
  • without checking syntax, use OPENXML, and sp_XML_docs
  • without checking syntax, shred xml.
  • review xpath syntax
  • what’s the difference between xquery and xpath?
  • Read through books online topic on xml

Gathering Perf Info

  • Use SHOWPLAN to (showplanxml?) to look through query
  • look at how paul (@sql_kiwi) displays query plan
  • Run through DTA, Prepare a workload <– remember options in DTA here!!!
  • Review catalog views. (Is this INFORMATION_SCHEMA?)

April 6, 2011

Data Modelling: My Favourite Example

Filed under: SQLServerPedia Syndication,Technical Articles — Michael J. Swart @ 12:00 pm

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:

Scoring results for a judged sport

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.

A Visual explanation of a db design pattern.

I see this pattern a lot.


And so in this case I would get:

Simple Schema for this example.

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.

Schema A

CREATE SCHEMA A;
GO
 
CREATE TABLE A.JUDGES
(
	JudgeId INT IDENTITY NOT NULL PRIMARY KEY,
	Name NVARCHAR(100) NOT NULL UNIQUE,
	Country NVARCHAR(100) NOT NULL
);
 
CREATE TABLE A.[EVENTS]
(
	EventId INT IDENTITY NOT NULL PRIMARY KEY,
	[Year] INT NOT NULL,
	Location NVARCHAR(100) NOT NULL,
	UNIQUE ([Year], Location)
);
 
CREATE TABLE A.ATHLETES
(
	AthleteId INT IDENTITY NOT NULL PRIMARY KEY,
	Name NVARCHAR(100) NOT NULL UNIQUE,
	Country NVARCHAR(100) NOT NULL
);
 
CREATE TABLE A.EVENT_JUDGES
(
	EventId INT NOT NULL
		REFERENCES A.[EVENTS](EventId),
	JudgeId INT NOT NULL
		REFERENCES A.JUDGES(JudgeId),
	PRIMARY KEY (EventId, JudgeId)
)
 
CREATE TABLE A.EVENT_ATHLETES
(
	EventId INT NOT NULL
		REFERENCES A.[EVENTS](EventId),
	AthleteId INT NOT NULL
		REFERENCES A.ATHLETES(AthleteId),
	PRIMARY KEY (EventId, AthleteId)
)
 
CREATE TABLE A.SCORES
(
	EventId INT NOT NULL,
	AthleteId INT NOT NULL,
	JudgeId INT NOT NULL,
	Score NUMERIC(3,2) NOT NULL,
	PRIMARY KEY (EventId, AthleteId, JudgeId), 
	FOREIGN KEY (EventId, AthleteId)
		REFERENCES A.EVENT_ATHLETES(EventId, AthleteId),
	FOREIGN KEY (EventId, JudgeId)
		REFERENCES A.EVENT_JUDGES(EventId, JudgeId)
)

Schema B

CREATE SCHEMA B;
GO
 
CREATE TABLE B.JUDGES
(
	JudgeId INT IDENTITY NOT NULL PRIMARY KEY,
	Name NVARCHAR(100) NOT NULL,
	Country NVARCHAR(100) NOT NULL
);
 
CREATE TABLE B.[EVENTS]
(
	EventId INT IDENTITY NOT NULL PRIMARY KEY,
	[Year] INT NOT NULL,
	Location NVARCHAR(100) NOT NULL
);
 
CREATE TABLE B.ATHLETES
(
	AthleteId INT IDENTITY NOT NULL PRIMARY KEY,
	Name NVARCHAR(100) NOT NULL,
	Country NVARCHAR(100) NOT NULL
);
 
CREATE TABLE B.EVENT_JUDGES
(
	EventJudgeId INT IDENTITY NOT NULL PRIMARY KEY,
	EventId INT NOT NULL
		REFERENCES B.[EVENTS](EventId),
	JudgeId INT NOT NULL
		REFERENCES B.JUDGES(JudgeId)
)
 
CREATE TABLE B.EVENT_ATHLETES
(
	EventAthleteId INT IDENTITY NOT NULL PRIMARY KEY,
	EventId INT NOT NULL
		REFERENCES B.[EVENTS](EventId),
	AthleteId INT NOT NULL
		REFERENCES B.ATHLETES(AthleteId)
)
 
CREATE TABLE B.SCORES
(
	ScoreId INT IDENTITY NOT NULL PRIMARY KEY,
	EventAthleteId INT NOT NULL REFERENCES B.EVENT_ATHLETES(EventAthleteId),
	EventJudgeId INT NOT NULL REFERENCES B.EVENT_JUDGES(EventJudgeId),
	Score NUMERIC(3,2) NOT NULL
)

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.

2. Queries turn out easier. e.g. when deleting …

/* 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)

3. Queries turn out easier. e.g. when copying …

/* Copy whole event */
DECLARE @EventIdToCopy INT = 43;
DECLARE @NewEventId INT;
 
INSERT A.[EVENTS](Location, [Year])
SELECT Location, [Year]
FROM A.[EVENTS]
WHERE EventId = @EventIdToCopy;
 
SET @NewEventId = SCOPE_IDENTITY();
 
INSERT A.EVENT_ATHLETES (EventId, AthleteId)
SELECT @NewEventId, AthleteId
FROM A.EVENT_ATHLETES
WHERE EventId = @EventIdToCopy;
 
INSERT A.EVENT_JUDGES (EventId, JudgeId)
SELECT @NewEventId, JudgeId
FROM A.EVENT_JUDGES
WHERE EventId = @EventIdToCopy;
 
INSERT A.SCORES(EventId, AthleteId, JudgeId, Score)
SELECT @NewEventId, AthleteId, JudgeId, Score
FROM A.SCORES
WHERE EventId = @EventIdToCopy;
 
/* For schema B, this is difficult and hairy. I could write a sample solution but it's not instructive.
Solutions usually include temp tables, output clauses and a mess of joins. Or you could instantiate
them as objects in an application and persist them again. But it's not as simple as Schema A */

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).

Powered by WordPress