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:
"WAIT_AT_LOW_PRIORITY used with ONLINE=ON only."
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:
https://dba.stackexchange.com/questions/155495/table-partitioning-in-sp1-for-sql-server-2016
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.
https://sqlperformance.com/2013/09/sql-indexes/lock-priority-sql2014
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
Message
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
Message
An ABORT_AFTER_WAIT = BLOCKERS lock request was issued on database_id = 7, object_id = 1182627256. All blocking user sessions will be killed.
and
Log SQL Server (Current - 3/23/2019 1:06:00 AM)
Source spid61
Message
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:
Post a Comment