Tuesday, May 7, 2019

[Table Partitioning] Does WAIT_AT_LOW_PRIORITY work with Standard Edition?

(TL;DR - yes.  Yes, it does.)

As you hopefully know, back with SQL Server 2016 SP1, Microsoft opened the floodgates of formerly-only-enterprise-features (https://sqlperformance.com/2016/11/sql-server-2016/big-deal-sp1). 

But with caveats.

For instance, IMOLTP (in-memory online transactional processing, aka "in-memory") tables are limited in the amount of RAM used. 

But how about WAIT_AT_LOW_PRIORITY?  That was introduced in 2014 to make table partitioning deal better with That-Dude-From-Accounting-Who-Kicks-Off-A-Massive-Query-On-Friday-at-5pm, which causes partitioning to hang on Saturday when you're trying to add and remove partitions.

Specifically, the notes don't say anything about it.  The closest they get is:

And ONLINE is an Enterprise-only feature.

So, does it or doesn't it?

For a simple example I actually used Aaron Bertrand's answer to a StackOverflow question:

So, what do we get?
Well, as pointed out by Joe Sack back in 2013(!), that feature will log to the Errorlog when it works. 

Let's try it out.  In the first window create the objects.  Once created, start a second window and open the transaction.  Then back to the first and run the SWITCHes and see what happens!

...Yup! It works in Standard!

Here are the messages I got:

Date 5/7/2019 3:41:03 PM
Log SQL Server (Current - 3/23/2019 1:06:00 AM)

Source spid61

An ALTER TABLE SWITCH statement was executed on database 'eif_workspace', table 'partitionexample' by hostname 'CH004547', host process ID 30812 with target table 'staging_FactInternetSales' using the WAIT_AT_LOW_PRIORITY options with MAX_DURATION = 1 and ABORT_AFTER_WAIT = BLOCKERS. Blocking user sessions will be killed after the max duration of waiting time.

, followed a minute later by: 

Date 5/7/2019 3:42:03 PM
Log SQL Server (Current - 3/23/2019 1:06:00 AM)

Source spid61

An ABORT_AFTER_WAIT = BLOCKERS lock request was issued on database_id = 7, object_id = 1182627256. All blocking user sessions will be killed.


Date 5/7/2019 3:42:03 PM
Log SQL Server (Current - 3/23/2019 1:06:00 AM)

Source spid61

Process ID 63 was killed by an ABORT_AFTER_WAIT = BLOCKERS DDL statement on database_id = 7, object_id = 1182627256.

I was figuring/hoping it'd work, but that example using ONLINE = ON gave me pause.
Proof, and a blog post!

No comments: