21

Preparing a SQL batch separately from executing the prepared SQL batch is a construct that is effectively** useless for SQL Server given how execution plans are cached. Separating out the steps of preparation (parsing, binding any parameters, and compiling) and execution only makes sense when there is no caching. The purpose is to save the time spent on ...


17

OPTIMIZE FOR UNKNOWN doesn't use a value - instead, it uses the density vector. If you run DBCC SHOWSTATISTICS, it's the value listed in the "All density" column of the second result set: In this example, I'm using the StackOverflow demo database. The density vector for the Reputation column is 5.962674E-05. If you take that value, times the number of ...


15

The behavior is no different between restarting the service alone or restarting the service due to rebooting the underlying operating system. Which information is wiped on such a restart? Query plans? Yes. Table/index data? Yes. Statistics? No. (Query plans, which use statistics, will have to be recompiled, but statistics won't have to get re-created ...


13

According to Infinite recompile message in the errorlog on the SQL Programmability & API Development Team Blog, this message is triggered when a statement in a batch recompiles 100 times in a row. This message does not necessarily mean there is a problem; it exists to help troubleshoot statements that might legitimately be recompiling that often (for ...


12

Explanation: You declare user_info as record. The manual: Record variables are similar to row-type variables, but they have no predefined structure. They take on the actual row structure of the row they are assigned during a SELECT or FOR command. The substructure of a record variable can change each time it is assigned to. Bold emphasis mine. ...


12

The reason that the size_in_bytes field of the sys.dm_exec_cached_plans DMV, at least in terms of "Compiled Plans", is larger than the CachedPlanSize attribute of the QueryPlan node in the XML plan is because a Compiled Plan is not the same thing as a Query Plan. A Compiled Plan is comprised of multiple Memory Objects, the combined size of which equates to ...


12

Think about what "actual" means. It's what actually happens for the execution of that plan. Another common name for the actual execution plan is the "post execution plan". As a real world example to correlate this scenario, say you plan to go on a cross country trip, so you plot out the roads you're going to take and how long you think it'll take. But ...


12

Per Microsoft documentation changing the max server memory will clear the plan cache, as will changing: access check cache bucket count access check cache quota clr enabled cost threshold for parallelism cross db ownership chaining index create memory max degree of parallelism max server memory max text repl size max worker threads min memory per query ...


12

Yes it does. Since SQL Server is in charge of maintaining those caches, when you shut down SQL server's services, it releases the memory back to the operating system.


11

If a user executes a statement that is one of the statements in the multi-statement query can it use that relevant part of the query plan already in the cache for the multi-statement query? No. The basic unit of plan reuse in SQL Server is the batch. Would it be better to hash each statement in a multi-statement query so they could be used by users ...


9

Cost is an overloaded term, as is trivial. When talking about execution plans, the estimated cost is computed by the query optimizer, as a way to choose one plan, or one small part of a plan, over another. The final plan has an associated cost computed by summing all the subtree estimated operator costs. A trivial plan is one obtained without going through ...


9

At first glance, this sounds like a classic parameter sniffing problem. SQL Server builds an execution plan for the first set of parameters that get called when the plan needs to be compiled, and then reuses that plan over and over through the day. You can see what parameters they are - when you're viewing the serial plan, right-click on the select ...


9

What you are looking for is the column query_hash, which was introduced in SQL Server 2008. You can find this in sys.dm_exec_query_stats. Here's a sample query to look at top 20 most common patterns: WITH agg AS ( SELECT TOP 20 COUNT(*) AS similar_query_count, query_hash FROM sys.dm_exec_query_stats qs GROUP BY qs.query_hash ORDER BY similar_query_count ...


9

The length of @P1 is different in all of those. You're getting different plans because you're not explicitly setting parameter lengths in your code. Any difference in strings, no matter how minor, will generate a new plan. Data type sizes, spaces, comments, all of them will cause new entries in the plan cache. Here's a demo video illustrating the problem. ...


9

There's an XEvent for that: query_cache_removal_statistics Occurs when a query plan is removed from the plan cache and the historical statistics for the object are about to be destroyed So something like: CREATE EVENT SESSION [PlanCacheEvictions] ON SERVER ADD EVENT sqlserver.query_cache_removal_statistics( ACTION(sqlserver.sql_text)) Also ...


8

My question is when could the Age of a plan reach zero? The algorithm that SQL Server uses to determine when and how plans should be removed from cache is called the eviction policy. The cost of plan is analyzed to determine which plans gets evicted. Upon detecting memory pressure, zero cost plans are removed from the cache and the cost of all other plans ...


8

You've got a few different questions in here: Does the "Lock Pages in Memory" setting preserve the plan cache? Only indirectly. LPIM means that SQL Server won't page out to disk if it comes under memory pressure, but SQL Server will still give up memory when the OS is under pressure. Jonathan Kehayias covers this in detail. The short answer is that when the ...


7

This is called parameter sniffing, and it's covered extensively in Erland Sommarskog's epic post, Slow in the App, Fast in SSMS. I can't even begin to do justice to it here, but sample solutions include: OPTION (RECOMPILE) - which causes increased CPU use for the plan compilation, plus loses historical metrics of the query execution, but can build a unique ...


7

Question 1: "What's with CREATE PROCEDURE?!?" When you execute a stored procedure, SQL Server stores the entire text of the stored procedure as the thing you called. You weren't CREATING the stored procedure - you were only executing it - but this can be a little confusing for folks who are just getting started analyzing the plan cache. So hey, you're now ...


7

So you just want to clear the Ad-hoc query plans and still dont want to clear the whole procedure cache. What you are asking is there in Clearing Your Ad-hoc SQL Plans While Keeping Your SP Plans Intact The blog asks you to run DBCC FREESYSTEMCACHE('SQL Plans') As per the blog The procedure cache actually consists of 4 distinct cache stores that hold ...


7

Cached Plans are typically only removed from the plan cache under memory pressure. SQL Server primarily considers the cost of the plan when deciding which plans to remove. Lost-cost plans are removed before high-cost plans. The "cost" here is not directly the same as the "plan cost" you see when looking at execution plans - it's a costing mechanism ...


7

There are a bunch of different reasons why a statement might be recompiled, including both performance and correctness-related reasons. For instance, an index used by the query may have been dropped, in which case the plan must be recompiled because it genuinely can't be run in that state. Or statistics may have been updated enough that an automatic ...


7

I "prime" the stored procedure with optimal values You can do better than that. Instead turn on the Query Store and force the plan you want. Or use a query hint like OPTION (RECOMPILE) or OPTIMIZE FOR.


6

Kris, •Do we really think that we will see a measurable performance gain here? If so, how can we quantify it aside from looking at the space used in the query above? That depends, but my gut instinct with the data you've given is - no. Sure, you'll potentially save some of that space as a plan stub will still take memory just not all that much (...


6

According to this, the second run of an ad hoc batch removes the stub (which was used only once) and creates and caches the plan (using it for the first time). I also haven't seen many references to refcounts other than it being a count of references by cache objects. Adhoc Compiled Plan objects can still have a refcount of 1, so it's not exclusively caused ...


6

That data in the query plan only indicates whether the query plan requires on an ordered scan, or whether an allocation-order scan would work too. Even if the query plan does not require an ordered scan, you normally get one anyway, as allocation-order scans are only allowed under specific circumstances, as Paul White explains all here. So since this data ...


5

Benjamin Nevarez has a good article about OPTIMIZE FOR UNKNOWN at his blog Essentially, SQL Server is using statistics about the table along with math to determine which value to use. From his post: Density is defined as 1 / number of distinct values. The SalesOrderDetail table has 266 distinct values for ProductID, so the density is calculated as 1 / ...


5

What you want to do is to use nodes() Method (xml Data Type) in a cross apply to shred on RelOp nodes so you then can use value() Method (xml Data Type) to get the property values you are looking for. You are specifically looking for the RelOp nodes that have a Hash/ProbeResidual node so you should use that in a predicate in the XQuery parameter to the ...


5

If it's a case of different options causing different plans to be used, just check the options using sys.dm_exec_plan_attributes. If the options are not the same for a good and a bad plan, that can then be the reason. A list of plan cache keys can be found in the following answer by Martin Smith: What would cause parameter sniffing one one computer and not ...


5

Regarding this I wonder if the prepare() method is kind of useless or obsolete or if I am missing something here? I would say that the Prepare method has limited value SqlCommand world, not that it's entirely useless. One benefit is that Prepare is part of the IDbCommand interface that SqlCommand implements. This allows the same code to run against ...


Only top voted, non community-wiki answers of a minimum length are eligible