![]() ![]() Our third stored procedure has no recompile hint in it at all. But as we’re going to see, these are different in some important ways. Now this seems really similar, right? There’s just one real query in the stored procedure. Instead with recompile is in the header of the stored procedure. ![]() In this case, there’s no query hint at the end of the query. The second stored procedure has the same name but it’s named RecompilerInHeader, and let’s go ahead and create that one. There’s a set nocount on and then one query in it, and the query has an OPTION query hint at the end of the query. The first stored procedure is called, MostPopularYearByNameRecompileHint. They’re the same code, but two of them have recompile hints in them. What we’re going to do is we’re going to compile three different stored procedures… So, having a private test environment to learn your SQL Server on, very valuable, for lots of different reasons. This is a really simple, easy way to make out demo rerunnable ‘cause we’re going to be looking at how many times did SQL Server see some stored procedures run.īut maybe be very careful about doing this if you’re using an instance that other people are using to test as well because this could skew their results to suddenly start compiling new execution plans if they’re in the middle of running something. Now when you run this the way I have this in this demo with no limitations on it at all, it clears out the entire procedure cache on your instance. The very first thing that we’re going to do in this demo is something that can be a little problematic to do sometimes, we’re clearing out the execution plan cache by running DBCC FREEPROCCACHE. We are going to watch some recompile hints. You get query plan and aggregate information in Query Store, even if you have recompile in the header of a procedure. Good news: recompile hints don’t obscure past performance in Query Store (available in SQL Server 2016+) like they do in the plan cache. Gail Shaw has an article that goes deeper into this topic: hit and miss. Since most applications that use procedures specify the command type, I used a PowerShell script to reproduce that behavior. If you execute a stored procedure in SQL Server Management Studio (or if you don’t specify the command type as a procedure), SQL Server first considers your execution statement itself as a potential adhoc plan because it appears as just text. That makes the compilation-related counters look different. I used a PowerShell script in this demo because it allows me to call a stored procedure like an application does, with a specific command type that indicates it’s a stored procedure right off. You’ll learn more granular ways to clear out parts of your execution plan cache in the upcoming module on “How to ‘take out’ an execution plan” Why a PowerShell script? ![]() Then compare their performance, both from the client application and in the SQL Server plan cache… if you can! Being more gentle to the plan cache See different kinds of recompile hints in action ![]()
0 Comments
Leave a Reply. |