Michael J. Swart

November 22, 2016

I’m Sharing Some Cool Art Prints

Filed under: SQLServerPedia Syndication — Michael J. Swart @ 8:00 am

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

Michael J Swart @ Etsy

I’m starting with these two prints:

Her Highnessness

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.


The Duke

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.'”


So order some prints to spruce up your office!

November 21, 2016

Do You Have Check Constraints On Your Start and End Dates?

Filed under: Miscelleaneous SQL,SQL Scripts,SQLServerPedia Syndication,Technical Articles — Michael J. Swart @ 10:13 am

Adam Machanic tweeted this advice last week:

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.

WITH StartColumnNames AS
    SELECT object_id,
           name AS column_name
      FROM sys.columns
     WHERE name like 'start%'
EndColumnNames AS
    SELECT object_id,
           name AS column_name
      FROM sys.columns
     WHERE name like 'end%'
SELECT t.object_id,            
       OBJECT_SCHEMA_NAME(t.object_id) AS [schema_name], 
       t.[name] AS table_name,
       s.column_name AS start_column,
       e.column_name AS end_column,
       N'ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(t.object_id)) + N'.' + QUOTENAME(t.name) + 
           N' ADD CONSTRAINT ' + 
           QUOTENAME(N'CK_' + t.name + N'_' + s.column_name + N'_' + e.column_name) + 
           N' CHECK (' + QUOTENAME(s.column_name) + N' <= ' + QUOTENAME(e.column_name) + N');' as check_suggestion
  FROM StartColumnNames s
  JOIN EndColumnNames e
       ON s.object_id = e.object_id
       AND s.column_id <> e.column_id
       AND REPLACE(s.column_name, 'start', 'end') = e.column_name        
  JOIN sys.tables t
       ON t.object_id = s.object_id
           SELECT *
             FROM sys.check_constraints c
             JOIN sys.sql_expression_dependencies start_dependency
                  ON start_dependency.referencing_id = c.object_id
                  AND start_dependency.referenced_id = t.object_id
                  AND start_dependency.referenced_minor_id = s.column_id
             JOIN sys.sql_expression_dependencies end_dependency
                  ON end_dependency.referencing_id = c.object_id
                  AND end_dependency.referenced_id = t.object_id
                  AND end_dependency.referenced_minor_id = e.column_id
            WHERE c.parent_object_id = t.object_id


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

I’m Trying To Kick My “Undo Button” Habit

Filed under: SQLServerPedia Syndication — Michael J. Swart @ 8:15 am

T-SQL Tuesday Logo
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.

Look for more talks in the near future.

October 26, 2016

My 2016

Filed under: SQLServerPedia Syndication — Michael J. Swart @ 11:59 am

2016 was a blast at work.

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)

September 23, 2016

Build Your Own Tools

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.

Inspired by Richie Rump’s latest project, I’m going to describe a tool I wrote and the process I took to get there.

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:

My Plan

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

Also, looking at the different pieces, they’re probably robust enough to do what I need them to do. One surprise involved security concerns for activated procedures that need to access system views

The Deadlock Logging Script

Here’s the tool!

Create the Database

USE master;
IF (DB_ID('DeadlockLogging') IS NOT NULL)
    DROP DATABASE DeadlockLogging;

Create the Service Broker Objects
I’ve never used Service Broker before, so a lot of this comes from examples found in Books Online.

use DeadlockLogging;
CREATE QUEUE dbo.LogDeadlocksQueue;
CREATE SERVICE LogDeadlocksService
    ON QUEUE dbo.LogDeadlocksQueue 
CREATE ROUTE LogDeadlocksRoute
    WITH SERVICE_NAME = 'LogDeadlocksService',
-- add server level notification
IF EXISTS (SELECT * FROM sys.server_event_notifications WHERE [name] = 'LogDeadlocks')
    FOR deadlock_graph -- name of SQLTrace event type
    TO SERVICE ''LogDeadlocksService'', ''' + CAST(service_broker_guid as nvarchar(max))+ ''';'
FROM sys.databases 
WHERE [name] = DB_NAME();
EXEC sp_executesql @SQL;

The dynamic SQL is used to fetch the database guid of the newly created database.

a Place to Store Deadlocks

-- Create a place to store the deadlock graphs along with query plan information
CREATE SEQUENCE dbo.DeadlockIdentity START WITH 1;
CREATE TABLE dbo.ExtendedDeadlocks 
    DeadlockId bigint not null,
    DeadlockTime datetime not null,
    SqlHandle varbinary(64),
    StatementStart int,
    [Statement] nvarchar(max) null,
    Deadlock XML not null,
    FirstQueryPlan XML
    ON dbo.ExtendedDeadlocks(DeadlockTime, DeadlockId);

The Procedure That Processes Queue Messages

CREATE PROCEDURE dbo.ProcessDeadlockMessage
    WAITFOR ( 
        RECEIVE TOP(1)
            @RecvMsgTime = message_enqueue_time,
            @RecvMsg = message_body
        FROM dbo.LogDeadlocksQueue
    ), TIMEOUT 5000;
    IF (@@ROWCOUNT = 0)
    DECLARE @DeadlockId BIGINT = NEXT VALUE FOR dbo.DeadlockIdentity;
    DECLARE @RecsvMsgXML XML = CAST(@RecvMsg AS XML);
    DECLARE @DeadlockGraph XML = @RecsvMsgXML.query('/EVENT_INSTANCE/TextData/deadlock-list/deadlock');
    WITH DistinctSqlHandles AS
        SELECT DISTINCT node.value('@sqlhandle', 'varchar(max)') as SqlHandle
        FROM @RecsvMsgXML.nodes('//frame') AS frames(node)            
    INSERT ExtendedDeadlocks (DeadlockId, DeadlockTime, SqlHandle, StatementStart, [Statement], Deadlock, FirstQueryPlan)
    SELECT @DeadlockId,
    FROM DistinctSqlHandles s
    LEFT JOIN sys.dm_exec_query_stats qs 
        on qs.sql_handle = CONVERT(VARBINARY(64), SqlHandle, 1) 
    OUTER APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
    OUTER APPLY sys.dm_exec_sql_text (CONVERT(VARBINARY(64), SqlHandle, 1)) st
      SELECT SUBSTRING(st.[text],(qs.statement_start_offset + 2) / 2,
             WHEN qs.statement_end_offset = -1  THEN LEN(CONVERT(NVARCHAR(MAX), st.text)) * 2
             ELSE qs.statement_end_offset + 2
             END - qs.statement_start_offset) / 2)) as sqlStatement([statement]);
    -- clean up old deadlocks
    SELECT DISTINCT TOP (500) @limit = DeadlockId 
    FROM ExtendedDeadlocks 
    ORDER BY DeadlockId DESC;
    DELETE ExtendedDeadlocks 
    WHERE DeadlockId < @limit;

Activating the Procedure

ALTER QUEUE dbo.LogDeadlocksQueue
    ( STATUS = ON,
      PROCEDURE_NAME = dbo.ProcessDeadlockMessage,

Clean Up
And when you’re all done, this code will clean up this whole experiment.

use master;
if (db_id('DeadlockLogging') is not null)
    alter database DeadlockLogging set single_user with rollback immediate 
    drop database DeadlockLogging
if exists (select * from sys.server_event_notifications where name = 'DeadlockLogging')

July 27, 2016

Do you Use CLR in SQL Server?

Filed under: Miscelleaneous SQL,SQLServerPedia Syndication,Technical Articles — Michael J. Swart @ 11:11 am

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:

FROM 0x4D5A900003000000040000... -- truncated binary literal

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?

External Link to Survey Monkey survey.

Update July 29 Here are the answers so far:

July 20, 2016

Simplified Order Of Operations

Filed under: Miscelleaneous SQL,SQLServerPedia Syndication,Technical Articles — Michael J. Swart @ 8:00 am

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.

Even Stephen Sondheim seemed to struggle with this.

AND is Always Evaluated Before OR

WHERE (1 = 1) OR (1 = 1) AND (1 = 0)
-- returns TRUE
WHERE (1 = 0) AND (1 = 1) OR (1 = 1) 
-- returns TRUE

& and | are Evaluated Left To Right

SELECT 1 | 1 & 0
-- returns 0
SELECT 0 & 1 | 1
-- returns 1

Here Are The Official Docs

Here what Microsoft says about SQL Server’s Operator Precedence.

  1. ~ (Bitwise NOT)
  2. * (Multiply), / (Division), % (Modulo)
  3. + (Positive), – (Negative), + (Add), (+ Concatenate), – (Subtract), & (Bitwise AND), ^ (Bitwise Exclusive OR), | (Bitwise OR)
  4. =, >, <, >=, <=, <>, !=, !>, !< (Comparison operators)
  5. NOT
  6. AND
  8. = (Assignment)

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:

  1. * (Multiply), / (Division)
  2. + (Add), – (Subtract)
  3. Put parentheses around everything else.

July 19, 2016

PASS Summit 2016: Developing High Concurrency Databases

Filed under: SQLServerPedia Syndication — Michael J. Swart @ 10:26 am

My session has been accepted!

Double Double excited

I’m excited that my talk Developing Highly Concurrent Databases got accepted as a session in this year’s PASS Summit in Seattle. This October.

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.

Maybe I’ll see you there!

July 7, 2016

Prioritize This List of Issues (Results)

Filed under: Miscelleaneous SQL,SQLServerPedia Syndication,Technical Articles — Michael J. Swart @ 8:00 am

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.


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
  • No automated backups – protect that data from disasters
  • A SQL injection vulnerability – protect the data from unauthorized users
  • Stale statistics – serve data efficiently
  • Cursors – a common cause of performance issues, but I’d want to be reactive
  • GUID identifiers – meh
  • NOLOCK hints – meh
  • Developers use a mix of parameterized SQL and stored procedures – It’s not a performance concern for me
  • Fragmented indexes – supposedly better performance?
  • Sprocs prefixed with “sp-” – aesthetics?

July 5, 2016

Prioritize This List of Issues

Filed under: Miscelleaneous SQL,SQLServerPedia Syndication,Technical Articles — Michael J. Swart @ 9:14 am

“One thing at a time / And that done well / Is a very good thing / As any can tell”

But life isn’t always that easy is it? I spend a lot of my workday juggling priorities. And I want to compare what I think to others. So I wrote a survey which explores the importance people place on different SQL Server issues. It’s easy to say avoid redundant indexes. But does it follow that it’s more important to clean up redundant indexes before rewriting cursors?

The List

Prioritize this list from greatest concern to least. So if an item appears above another item, then you would typically tackle that issue first.

  • Corrupted database
  • A SQL injection vulnerability
  • Stale statistics
  • Fragmented indexes
  • Developers use a mix of parameterized SQL and stored procedures
  • Sprocs prefixed with “sp_”
  • Cursors
  • GUID identifiers
  • NOLOCK hints
  • No automated backups

I want to hear what you think. Submit your prioritized list in the comments, or by using this survey: https://www.surveymonkey.com/r/MV9F9YT

I’ll be posting my own answers on Thursday, July 5, 2016.

Update: I’ve shared the results. Prioritize This List Of Issues (Results)

« Newer PostsOlder Posts »

Powered by WordPress