Creating Uncacheable Stored Procedures In SQL Server

Posted on by Erik Darling

Creating Uncacheable Stored Procedures In SQL Server


Here’s the demo script from today’s video:

/*Create a table if you need to*/
CREATE TABLE
    dbo.DinnerPlans
(
    id bigint IDENTITY,
    name nvarchar(40) NOT NULL,
    seat_number tinyint NULL,
    is_free bit NOT NULL,
);
GO

/*First example, with an object that doesn't exist*/
CREATE OR ALTER PROCEDURE
    dbo.i_live
(
    @decider bit = NULL
)
AS
BEGIN
    SET NOCOUNT, XACT_ABORT ON;
   
    IF @decider = 'true'
    BEGIN
        SELECT
            dp.*
        FROM dbo.DinnerPlans AS dp;
    END;
   
    IF @decider = 'false'
    BEGIN
        SELECT
            whatever.*
        FROM dbo.AnObjectThatDoesntEvenPretendToExist AS whatever;

        --SELECT result = 'false!';
    END;
   
    IF @decider IS NULL
    BEGIN
        SELECT
            result =
                'please make a decision.'
    END;
END;
GO

/*Say goodbye!*/
DBCC FREEPROCCACHE;

/*This runs without an error*/
EXEC dbo.i_live
    @decider = 'false';

/*But there's no query plan!*/
SELECT
    object_name =
       OBJECT_NAME(deps.object_id, deps.database_id),  
    deps.type_desc,
    deps.last_execution_time,
    deps.execution_count,
    dest.text,
    query_plan =
        TRY_CAST(detqp.query_plan AS xml)
FROM sys.dm_exec_procedure_stats AS deps
OUTER APPLY sys.dm_exec_sql_text(deps.plan_handle) AS dest
OUTER APPLY sys.dm_exec_text_query_plan(deps.plan_handle, 0, -1) AS detqp;
GO

Thanks for watching!

Video Summary

In this video, I delve into a fascinating and somewhat unconventional topic: creating uncachable stored procedures in SQL Server. The goal is to demonstrate how to craft a store procedure that will never cache any query plans, effectively leaving no trace of its execution in the plan cache. This can be particularly useful for consultants or developers who need to run potentially suspicious queries without leaving evidence behind. I walk through the process step-by-step, creating a simple stored procedure with conditional logic that references non-existent tables and null values, ensuring SQL Server cannot cache any plans for it. Along the way, we explore why this happens and how it relates to deferred compilation and sensitive operations like decrypting asymmetric keys. Whether you find value in this niche technique or simply enjoy learning about SQL Server’s inner workings, I hope you found this video informative and engaging.

Full Transcript

Erik Darling here with Darling Data. And I know what you’re thinking. It’s two days and two YouTube videos in a row. And I’ve decided that perhaps I’ll endeavor to record a YouTube video every day as long as I have something reasonably recordable today. So, this one, this one is on the very border of reasonably recordable. And I actually find it patently ridiculous. And there are parts of me that wish this didn’t, this wasn’t a demo. Now, let’s start with what it actually is. And the idea here is to create uncashable store procedures. And by uncashable, I mean no query plan will ever be cached for this store procedure.

Right? And now, who would this really appeal to? Aside from like, you know, consultants, people who make monitoring tools, things like that, who don’t want to leave any evidence at how bad the DMV queries and stuff that they have to run are. So, here we go. Alright. So, what we have is, so, I already created this table. We don’t have to worry about this. This is already done. If I try to execute this, I’ll already, I’ll get an error message saying, already there, dum-dum. And I’ll say, yeah, I knew that. Thank you for the emotional support.

So, what we’re going to do is create a store procedure. And I’m going to just make sure that I’ve done things correctly because if I don’t do things correctly, then nothing works, right? Unlike government, I have to do things right for anything to happen. So, here we’re going to create a store procedure. And this is relatively simple, right? Make sure that the problem is, rather, the point is as simple and easily surmised as possible for those out there with short attention span.

And this store procedure right now just has one parameter called decider. It’s a bit. And we have three potential things that could happen when this store procedure runs. If the bit is true, then we’re going to select everything from this dinner plans table.

If the bit is false, then we’re going to select from this table that does not exist. Not anywhere in my database or any database ever. It does not exist anywhere. If I try to run this query on its own, I’m going to get an error that says invalid object name, blah, blah, blah, blah, blah.

You see what I mean? And then if decider is null, we’ll say, please, please make a decision. Kindly support yourselves making a decision.

All right. So, we have that. I’m just going to make sure that I actually hit, I actually executed that the way it should be. And what I’m going to show you is when I clear up the plan cache and I run this for decider equals true, we get results back from my dinner plans table.

But then when I go look at the plan cache, well, there’s a bunch of other stuff in there. I should do that from clear up the plan cache. Now run this. Now run this.

And hopefully we don’t catch anything else stupid in there. But this is our store procedure right here that we just executed. And it has no query plan.

All right. No query plan in there. I can’t click on that. It’s not just a null query plan. It’s no query plan for that. Now let’s contrast that.

There is no comparison. We don’t contrast something like that. And let’s just flip this. So let’s say result equals false. All right. Let’s unquote that.

Or rather let’s quote that out, not unquote. Whatever. It’s going to be that kind of day. So let’s recreate this now with just this select result equals false in there. All right.

But we’re going to clear the plan cache and then run this. And we’re still going to get the same results back. But now when we go look in the plan cache, we have an execution plan where it says we selected from the dinner plans table. All right.

Now I’m going to show you two more things before I talk about why this thing was uncacheable in the first place. So let’s change it back to how it originally was. All right.

And let’s do this. And now let’s try two things. The first thing that I’m going to try to do is get an estimated execution plan for this store procedure. I’m going to hit control and L.

And what happens? When SQL Server tries to estimate a plan for this, you get an error saying there’s an invalid object name. When SQL Server tried to build an estimated plan for this, it has to go and explore every single branch.

It has to figure out what can we estimate might happen here. But since that table doesn’t exist, you get an error. It’s crazy.

The other thing, I mean, it’s not crazy. The table doesn’t exist. How could you possibly come up with an execution plan for a table that doesn’t exist? The other thing is if we change this to false and we actually try to execute it, we’ll get the same error. All right.

So something is clearly very funny about the plan caching behavior. Ooh, that stuff isn’t right. Something is clearly very funny about the plan caching behavior of store procedures that have objects in them that do not exist. And part of this is going to be what SQL Server dorks will refer to as deferred compilation.

So, or deferred resolution or something like that. Where, like, you’re allowed to create modules that depend on things in some cases, even if those things aren’t there yet. So, like, you can create a store procedure that executes another store procedure, but you’ll get a warning in there and say, hey, just heads up.

FYI, this store procedure depends on this store procedure, but this store procedure isn’t there yet. Right? So you have to, like, you can do that, and SQL Server will let you do that as long as you don’t actually try to execute that other store procedure.

So that’s really what’s happening here, but it’s really interesting to me that what that results in is that there’s no cacheable plan for the store procedure. Now, this has one other sort of, well, I’m sorry, rather, there’s sort of like one other circumstance where you might see stuff like this happen. And it’s something that Paul White blogged about a little while back, and I thought I had scrolled to where it was in there.

Oh, there we go. So the uncacheable batch, right? So in this case, so when Paul’s blog post, what he did was he used, like, security stuff, like, you know, sensitive password information.

So something like this, where opening asymmetric key and decrypting it, or something like this, where you’re creating an application role with a password, right? So SQL Server won’t cache stuff like that either, right? Rather, it won’t, like, cache, won’t go cache a query plan for that, because it’s just like, whoa, whoa, whoa.

Sensitive stuff in there. Don’t want that to happen, right? But this is really interesting, because you can do that without having to do any of that stuff, right? Without having to fiddle with weird logic and creating, maybe trying to create roles, or without having to, like, submit any, like, symmetric key stuff.

All you have to do is put in a branch that will never be explored, like this. You could make this any sort of weird thing, right? You could be like, if one plus one is less than two billion or something like that, or, I don’t know, whatever, you pick something, right?

Whatever you find to be fundamentally and provably untrue in your world, in your mind, you can put that condition into your stored procedure, in your stored procedure. And you can have that stored procedure explore, like, or rather, never explore a branch where an object that will never exist lives, like, as a queryable object. And guess what?

You will never cache a plan for that stored procedure. So, if you ever want to hide what you’re doing from the plan cache, this is a great way to do it. All right.

So, with that out of the way, thank you for watching. I hope you enjoyed yourselves. I hope you learned something. I mean, I don’t know how much value you could have learned from this, but if you’re out there writing stored procedures you don’t want anyone to know about, on top of, like, you know, adding recompile hints and stuff, it’s a pretty keen way of doing things.

If you enjoy this sort of hard-hitting, useful SQL Server content, feel free to give the video a like and pretty please subscribe to my channel so that you can get little ding, ding, ding notifications every time I teach you about something hopefully more useful than this. All right. I wonder if anyone wants to buy Beer Gut magazine.

Getting desperate here. Anyway, thank you for watching and I will see you in the next video whenever that may be. Maybe later today, maybe tomorrow.

See how the day finds me. Anyway, thank you for watching.

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.

Related Posts