At D2L, we created a new free monthly meetup called D2L Night School that helps us give back to the local tech community. This is how we describe it on the site:

Since 1999, D2L has been growing in Kitchener while transforming the way the world learns. We don’t know it all, but along the way we’ve learned some things about technology and management that we want to share with the wider community. We’re excited to launch D2L Night School to share what we’ve learned, network with other innovators and collectively build a stronger tech community in KW.

On December 12, I’ll be giving my Drawing Conclusions talk, a fun and sometimes irreverent look at the SQL Server and Data world through illustrations.

Register here. Then come drink beer, socialize and talk tech. It should be fun.

Earlier this year, the local .Net User Group CTTNUG asked members what topics they’d like to learn more about. It turns out that SQL Server was high on that list and so Ken Cenerelli reached out to me. And so I’ll be talking about SQL Server concurrency, a topic I care a lot about.

Concurrency is a critical part to scalability. Come by and learn how to tackle common enemies of concurrency. Get this right and you’ll have one less problem to worry about. And one less problem lets you get back to more important things.

November 22, 2016

When I was younger, I put posters on my bedroom wall. My favorite was of the Beatles. I don’t decorate my bedroom with posters any more, but I do decorate my office. And I suspect a lot of you do too.

I can help you with that! I’ve opened an Etsy store where I’m selling art prints.

I’ve started doing drawings (in my own style) at a resolution that is print-worthy. These prints use card stock and are worthy of framing. But if you would rather tack them to a wall with pushpins, that’s fine too 🙂 .

This is Carrie Fisher as Princess Leia of course. It’s the end of Empire Strikes Back. She’s thinking about Han and Luke. Leia isn’t in a panic. She’s worried and concerned but not anxious. I drew this as if she’s on cloud city at sundown.

Fun fact. This scene doesn’t actually exist, because our heroes don’t get to catch their breath until they’re away from the planet, but I like the red.

I’m a relatively new fan of westerns and of John Wayne in particular. But I know many people love him because they grew up watching him with their family.

This is the Duke as Rooster Cogburn in True Grit (1969). In the story, Rooster is a real bad ass which is perfect for John Wayne. This picture is from a scene where Rooster opens up about his past. He’s sitting at a campfire talking with Maddie but instead of being tough on the outside and soft in the middle, we see that Rooster is tough as nails right through “She said, ‘Goodbye, Reuben, a love for decency does not abide in you.'”

Are you missing any of these check constraints? Run this query to check.
This query looks for any columns in the same table that begin with “Start” and “End”. It then looks for check constraints that reference both these columns. If it doesn’t find them, it suggests a check constraint.

Caveats

Don’t blindly run scripts that you got from some random guy’s blog. Even if that someone is me. That’s terribly irresponsible.

But this query may be useful if you do want to look for a very specific, simple kind of constraint that may match your business specs. These constraints are just suggestions and may not match your business rules. For example, when I run this query on Adventureworks, I get one “missing” check constraint for HumanResources.Shift(StartTime, EndTime) and when I look at the contents of the Shift table, I get this data:
Notice that I can’t create a constraint on this table because of the night shift. The constraint doesn’t make sense here.

Creating constraints on existing tables may take time if the table is huge. Locks may be held on that table for an uncomfortably long time.

Of course if your table has data that would violate the constraint, you can’t create it. But now you have to make some other choices. You can correct or delete the offending data or you can add the constraint with NOCHECK.

November 8, 2016

Thank you Andy Yun for hosting this month’s T-SQL Tuesday. Andy asks us to write about speaking which is a bit apropos because like Andy I got to speak the PASS Summit this year.

If you have visited any post on this site, you’ll realize two things very quickly. (1) I love writing about SQL Server and (2) I like drawing. When doing either of those things, I rely heavily on the undo button. Whether it’s Ctrl+Z or Backspace. It helps me be fearless. Fearless when drawing, fearless when writing. It helps me be perfect at the cost of efficiency – or at least as close to perfect as I want to be.

But there’s no undo button when speaking. Speaking is a kind of performance and I think I get hung up on that which makes me nervous. So this post then is kind of a narrative essay of my experience speaking at the PASS Summit.

Applying to Speak

Back in January, I set a goal for myself, write once a week and speak once a month. This meant looking for places to speak. So when the call PASS Summit call for speakers came around I decided to apply. I read two or three blog posts on what makes a compelling abstract and followed most of the advice. The piece of advice I followed most closely was “talk about what you know”. So I signed up with one abstract (only one) on a topic near and dear to my heart, SQL Server concurrency.

The thing I knew I had going for me was that I live this topic. At work we’ve successfully refactored a legacy system that has handled a peak load of over 50,000 transactions per second (And that’s without Hekaton). Most of the talk includes lessons we’ve learned and things I wish I knew earlier.

Getting Accepted

So I got accepted. I have to describe the experience of getting accepted, because the notification comes via email and is nothing like a college letter. College acceptance letters usually start with words like “Congratulations”, or “I am pleased to inform…”. While College rejection letters start with “We regret” or “Unfortunately”.

Well, the letter from PASS is nothing like that. It’s a form letter where they introduce definitions of Accepted/Alternate/Not Accepted. And then 250 words later (scroll scroll scroll on my phone) I see a tiny word beside my session title: “accepted”. That was celebration time.

Do you know when a rookie manages to get a hit at their first at bat in the majors? For a few minutes at least, they’re batting a thousand. That’s me right now. I realize how fortunate that makes me.

The Big Room

So a week before the summit, I hopped onto the speaker orientation conference call where Allan White tells us about which rooms are being recorded. He mentions one room which is set up for PASS TV. Room 6E. I checked the schedule and found my name. Now to check the room, (scroll, scroll, scroll) 6E Gulp!

In the end, the room was a blessing and a curse. It was huge. I figure it could have held maybe 1000 people. There’s no way that was going to be full on the second last session right after lunch on a Friday afternoon! And although the audience was probably the largest audience I’ve ever had, it felt empty.

The Talk

The talk went well. It started out rocky but once I got into the SQL, I got more comfortable. I had friends in the audience and I really appreciated their presence. The questions after were great. One lesson I’m taking for next time is to end maybe five or ten minutes early so I’m not in such a rush to get off the stage. There were many people who were waiting to ask me a question that never got a chance to.

It was a good experience and I want to do more of it. That means more practice. I’m double lucky because this week, I get to give the talk again for work! D2L is hosting an internal conference and I’m excited to deliver it to the home team.

October 26, 2016

For most of 2016, I worked with a small team of three called the Samurai team. The team name was taken from the wandering Samurai who wanders from village to village doing good where ever injustice was found. Our job was to go around doing good wherever technical debt was found.

And that was the point. It was wherever we found the technical debt. It was an exercise in trust I suppose. They trusted us to do what was important. We had more autonomy then I’ve ever enjoyed before. There was still accountability and everything we did was data-driven and pragmatic. But I really enjoyed the experience.

We did a lot of work and on the database scalability side, we focused on these things:

Avoiding queries

Tuning queries

Stabilizing execution plans

Concurrency

Database Concurrency

In 2016, more than ever before, I got to exercise my database concurrency skills in a real practical sense. It was cool to find out what rules of thumb were true and what rules of thumb didn’t matter.

Developing Highly Concurrent Databases

That’s why I’m really excited to talk about database concurrency at the PASS Summit. Developing Highly Concurrent Databases I’m here in Seattle this week and I get to deliver my talk called talk on Friday at 2PM.

But if you’re not in Seattle, you can choose to watch me on PASS TV! They’re live streaming one session room throughout the conference. And I’m fortunate enough to be presenting in that room.

If you’re here at PASS, mark your schedule for room 6E on Friday at 2PM (Pacific).
And if you’re not, you can still catch me and other presenters on PASStv (Friday at 5PM Eastern)http://www.sqlpass.org/summit/2016/Live.aspx.

September 23, 2016

There are so many ways to look inside SQL Server. New extended events and dynamic management views are introduced every version. But if you want to collect something that’s unavailable, with a little bit of creativity, you can create your own tools.

The Problem To Tackle

I had a deadlock graph I wanted to tackle, but I was having trouble reproducing it. I needed to know more about the queries involved. But the query plans were no longer in cache. So here’s the problem

Can I collect the execution plans that were used for the queries involved in a deadlock graph?

I want to use that information to reproduce – and ultimately fix – the deadlock.

The Right Tool For the Job

If you don’t have enough data to get to the root cause of an issue, put something in place for next time.

Can I Use Out-Of-The-Box Extended Events?
I’m getting used to extended events and so my first thought was “Is there a query plan field I can collect with the deadlock extended event? There is not. Which isn’t too surprising. Deadlock detection is independent of any single query.

So How Do I Get To The Execution Plans?
So when I look at a deadlock graph, I can see there are sql_handles. Given that, I can grab the plan_handle and then the query plan from the cache, but I’m going to need to collect it automatically at the time the deadlock is generated. So I’m going to need

XML shredding skills

Ability to navigate DMVs to get at the cached query plans

A way to programatically respond to deadlock graph events (like an XE handler or a trigger)

Responding Automatically to Extended Events
This is when I turned to #sqlhelp. And sure enough, six minutes later, Dave Mason helped me out:

I had never heard of Event Notifications, so after some googling, I discovered two things. The first thing is that I can only use Event Notifications with DDL or SQLTrace events rather than the larger set of extended events. Luckily deadlock graphs are available in both. The second thing is that Event Notifications aren’t quite notifications the way alerts are. They’re a way to push event info into a Service Broker queue. If I want automatic actions taken on Service Broker messages, I have to define and configure an activation procedure to process each message. In pictures, here’s my plan so far:

Will It Work?
I think so, I’ve had a lot of success creating my own tools in the past such as

July 27, 2016

We don’t use CLR assemblies in SQL Server. For us, programming in the database means that maybe “you’re doing it wrong”. But there have been rare circumstances where I’ve wondered about what the feature can do for us.

For example, creating a CLR assembly to do string processing for a one-time data migration might be preferable to writing regular SQL using SQL Server’s severely limited built-in functions that do string processing.

Deployment Issues

I’ve always dismissed CLR as part of any solution because the deployment story was too cumbersome. We enjoy some really nice automated deployment tools. To create an assembly, SQL Server needs to be able to access the dll. And all of a sudden our deployment tools need more than just a connection string, the tools now need to be able to place a file where SQL Server can see it… or so I thought.

Deploy Assemblies Using Bits

CREATE ASSEMBLY supports specifying a CLR assembly using bits, a bit stream that can be specified using regular T-SQL. The full method is described in Deploying CLR Database Objects. In practice, the CREATE ASSEMBLY statement looks something like:

This completely gets around the need for deployments to use the file system. I was unaware of this option until today.

Your Experience

So what’s your experience? My mistaken assumptions kept me from evaluating CLR properly. I wonder if anyone is in the same position I was in and I wonder if this accounts for the low adoption in general of CLR in SQL Server. Answer this survey, Which option best describes you?

July 20, 2016

I recently learned that when combining multiple operators in a SQL expression, AND has a higher precedence than OR but & has the same precedence as |. I expected the precedence rules for the logical operators to be consistent with the bitwise operators.

Practical Subset

I have a book on my shelf called Practical C Programming published by O’Reilly (the cow book) by Steve Oualline. I still love it today because although I don’t code in C any longer, the book remains a great example of good technical writing.

That book has some relevance to SQL today. Instead of memorizing the full list of operators and their precedence, Steve gives a practical subset:

You’ve heard the advice “write what you know”. I’ve applied that advice to my choice of topic. For the past nine years, I’ve worked for D2L, a fast growing company facing a higher volume of database activity year after year.

We built a system that has handled a peak load of over 50,000 transactions per second. And that’s without using In-Memory OLTP yet. Much of the talk will include lessons we’ve learned and things I wish we knew earlier.

July 7, 2016

Earlier this week I asked people to help me out prioritizing a list of issues. I was surprised by the number of people who participated. I think I missed an opportunity to crowd-source a large part of my job by including my real issues.

Results

Thanks for participating. After the results started coming in, I realized that my question was a bit ambiguous. Does first priority mean tackle an issue first? Or does a higher numbered issue mean a higher priority? I clarified the question and took that into account for entries that picked sproc naming conventions as top priority.

The results were cool. I expected a variety of answers but I found that most people’s priorities lined up pretty nicely.

For example, even though I wrote a list of issues all with different severity, there were three issues that stood out as most critical: Corrupted databases, a SQL injection vulnerability and No automated backups. Keeping statistics up to date seemed to be the most important non-critical issue.

But there is one issue that I thought had a lot of variety, index fragmentation. I personally placed this one second last. I got a friend to take the survey and I got to hear him explain his choices. He wanted to tackle index fragmentation early because it’s so easily fixable. “It’s low hanging fruit right? Just fix it and move on.”

My friend also pointed out that this technique would work well as an interview technique. Putting priorities in an order is important but even better is that it invites so much discussion about the reasons behind the order.

Speaking of which, go check out Chuck Rummel’s submission. He wins the prize for most thorough comment on my blog.

My Priorities

Here they are:

Corrupted database – serving data is what databases are supposed to do