How to Create Un-cachable Stored Procedures in SQL Server

Creating uncashable stored procedures in SQL Server is like hiding your secrets in plain sight. By making sure that certain branches of your code will never be explored, you can ensure that no query plan will ever be cached for your stored procedure. It’s a sneaky way to keep your actions off the radar. 😉🔒👀 So, if you want to keep your SQL Server activities under wraps, this is a pretty neat trick to have up your sleeve. Just be careful not to get caught! Cheers! 🍻

Introduction

In this video, we will discuss the concept of creating uncashable store procedures in SQL Server, and how it can be useful for certain scenarios.

Key Takeaways

Here’s a quick summary of the key points we will cover in this video:

| Points |

|———————————————|
| Definition of uncashable store procedures |
| Use cases for uncashable store procedures |
| Exploring the plan caching behavior of SQL Server |
| How to hide queries from the plan cache |

The Problem of Uncashable Stored Procedures 😖

The idea behind creating uncashable store procedures is to ensure that no query plan will ever be cashed for these procedures. This could be useful for consultants, developers, or monitoring tool creators who do not want evidence of their queries in the plan cache.

Example of Uncashable Stored Procedure

Let’s say we create a stored procedure with a parameter called ‘decider’, and based on its value, different queries are executed. If the bit is true, one query is executed; if it’s false, another query is executed for a table that does not exist. And if the ‘decider’ is null, a message is displayed asking for a decision.

The Plan Caching Behavior 📊

When we clear out the plan cache and run the stored procedure with different parameters, we observe the behavior of plan caching. If the query results in an error due to a non-existent table, the plan doesn’t get cached, as SQL Server cannot create an execution plan for an invalid object.

The Deferred Compilation Behavior

The concept of deferred compilation or deferred resolution comes into play when we create modules dependent on non-existent objects. In some cases, SQL Server allows this but issues a warning that the dependency does not exist. This is an interesting observation that leads to the inability to cache a plan for such stored procedures.

Making Queries Invisible to the Plan Cache 🕵️‍♂️

By utilizing a branch in the stored procedure that will never be explored, we can effectively ensure that the store procedure will never have a cached query plan. This can be a valuable tactic for those wanting to hide their queries from the plan cache.

Conclusion

In this video, we explored the concept of creating uncashable stored procedures in SQL Server, which can be a useful approach in certain scenarios. If you found this content interesting, please consider subscribing to our channel for more valuable SQL Server insights and updates.

FAQ

Q: Can uncashable stored procedures impact performance?
A: Uncashable stored procedures may result in repeated compilation of query plans, impacting performance in some cases.

Thank you for watching, and stay tuned for more insightful content!