Why doesn’t this query stay in its own lane?

It’s a terrible driver, it’s going into all the partitions.

Want to get sp_WhoIsActive yourself?

sp_WhoIsActive is a free procedure by Adam Machanic. Download it at whoisactive.com.

Transcript

This problem query starts getting blocked unexpectedly after we partition the table.

This query is looking to count the number of babies who were born on the first day of the year 2000, and it should just be accessing one partition in the table right?

We have the data partitioned into years, so it’s only reading data in the 2000 year partition, but yet it gets blocked by a query who doesn’t seem like it should be the blocker.

Let’s take a look.

The first thing I’m going to do in our blocking simulation is start up the blocker

I’m going to take this code and copy it into its own session. This code is correcting some records that came in. I’m going to start a transaction and then run an update statement that is setting the StateCode to ZZ just for rows that are in the 2010 partition.

We should only be locking inside one of the partitions in the table.

I’m going to put on actual execution plans and start this guy up. Looking at the execution plan, we’ll look at that columnstore index update there and we can see that it is only accessing one partition, partition number 46 is what it’s doing, it’s locking in there.

To simulate things happening at the same time, I haven’t committed or rolled back the transaction.

Let’s take a look at our unexpectedly blocked query

This query starts running at the same time as our blocker, it just wants to look a rows in 2000, not 2010, I’m going to start executing this. My query seems really slow, it’s running and running and running.

Well, let’s look at it from another session, let’s say we’re the Database Administrator or DevOps person who gets paged that something’s up on the instance. I’m going to use the free sp_WhoIsActive stored procedure, written by Adam Machanic. You can download it for free at whoisactive.com to say: what’s happening on my SQL Server, who is active? I’ve asked it for execution plans for the active sessions.

My Select… Is blocked… by the session that’s doing the update, and I’m waiting on a lock.

Well, I shouldn’t be touching the same partition, what’s up with that?

I can go on over, and sp_WhoIsActive, because I said @get_plans = 1, it’s given me a view of the query plan it sees in the execution plan cache for this guy. This isn’t an actual plan, it won’t have any actual partitions accessed in it, but I can see some information about my query. It wants to do a columnstore index scan.

Let’s take a look at the properties on that columnstore index scan. I don’t see any information at the top, but since it’s not an actual plan, I wouldn’t expect to see actual partitions accessed, I’m just not going to get that.

I can look at the predicate on the query, down here, there’s no seek predicate, there’s just a predicate. And popping up the predicate on the columnstore index scan, I get some interesting information about what’s going on.

It’s looking at [FakeBirthDateStamp] >= CONVERT_IMPLICIT datetime2(7)

It’s saying, I had to do an implicit conversion on the parameter you passed in, which it has reassigned to parameter name @1.

I just used a literal value, and SQL Server did a little bit of auto-parameterization behind the scenes.

When you run just a simple query– and your business users might do this– against your partitioned index, when you run just a simple query, sometimes SQL Server can automatically auto-parameterize that query, and it will turn it into the data type that seems the most natural.

The type that seemed the most natural in this case was datetime2(7). My column FakeBirthdateStamp is datetime2(0).

It has a different precision, because that is a different precision and because the column is less precise than the value it’s comparing it to, that prevents it from being able to be efficient on the operation.

This is also true for rowstore indexes, indexes in general. Implicit conversions can cause a problem in SQL Server.

Let’s get back to our query, let’s go back to our blocked query here. We’re going to stop it. Let’s go back up and review it. Here is the literal value that SQL Server automatically parameterized into a datetime2(7).

I’ve still got my blocking query running, I haven’t stopped that guy, it’s still going.

To get around this, what we can do is be explicit about our data types

This is just a little different from my original query.

I am explicitly saying I want to take this literal value and I want you to turn it in to a datetime2(0).

That is going to match— that DOES match the data type for FakeBirthdateStamp. I’ve got my actual execution plans on, I run the query, it is not blocked. Looking at my plan, now, it’s still a columnstore index scan, but, I get a giant Tooltip if I hover the wrong way that’s impossible to read. If I look at the properties– I find the properties window to be much easier than the Tooltip window to deal with.

Because I now matched the data type of the column properly, I was able to get partition elimination.

Before, when I had that implicit conversion, it was like, oh this doesn’t match, I can’t do partition elimination. It tried to access all the partitions in the table, so it got blocked, because a modification was happening in one partition.

Now that I fixed my data types, it’s like: oh, your data that you want is just in one partition, it’s in partition number 36. Data types, when you’re querying partitioned tables, are even more important when it comes to partition elimination. They really, if you do not match the data type, exactly– and with the date types like datetime2, this is the trickiest– then you might not get partition elimination when it seems like you should.

I could also, of course, declare a variable with the type, as long as I’m explicit and I’m using the type that matches and then plug that in to a query, that also does not get blocked and is able to do the partition elimination.

When you switch to partitioned objects, make sure that your users are clear on the data types involved, because the very best work around, if you’re not getting partition elimination on a query, is to rewrite it so that you do.