![\"Perfmon](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2021\/07\/word-image-65.png\")
<\/p>\nThe standard guideline that SQL Compilations\/sec should be less than 10% of Batch Requests\/sec is misleading for ad hoc workloads. When a SQL Server instance has \u201coptimize for ad hoc workloads\u201d enabled, ad hoc queries generate a \u201ccompiled plan stub\u201d on first execution, and a full compiled plan only on second execution. This means the Compilations\/sec counter fires on the first execution (for the stub) but does not fire on the second execution when the full plan is actually compiled and cached.<\/p>\n
The result: the counter underreports actual compilation work while the Cache Hit Ratio appears lower than it should be, because the stub counts as a cache miss on the second execution. Understanding this behavior changes how you interpret these performance counters for ad hoc-heavy environments.<\/p>\n
I was recently working in a SQL Server health check assessment, and the scenario I was investigating made me re-evaluate what I knew about performance counters SQL Compilations\/sec, Cache Hit Ratio and the relation of those to Batch Requests\/sec.<\/p>\n
Consider the following scenario:<\/p>\n
<\/p>\n
As you can see, the number of SQL Compilations\/Sec is very high. It\u2019s important to step back and remember the general description and guideline for this counter and understand what I mean by \u201chigh\u201d:<\/p>\n
Official Description<\/a>: \u201cNumber of SQL compilations per second. Indicates the number of times the compile code path is entered.\u201d<\/p>\n A typical<\/a> description: \u201cThe number of times that Transact-SQL compilations occur, per second (including recompiles). The lower the value the better. Each compilation uses CPU and other resources, so SQL Server will attempt to reuse cached execution plans wherever possible. Not having to recompile the stored procedure reduces the overhead on the server and speeds up overall performance.\u201d<\/p>\n Guidelines:<\/p>\n Consider the \u201c10% of batch requests\/sec guideline\u201d. The counters show 21374 compilations\/sec and 39693 batch requests\/sec, (21374\/39693) * 100 = 54%. Considering the 10% guideline, 54% looks like a very high number.<\/p>\n Another thing that caught my attention is the low value (69%) of \u201cPlan Cache Hit ratio\u201d (Ratio between cache hits and lookups.). The general description for this counter is: \u201chow frequently a compiled plan is found in the plan cache (therefore avoiding the need to compile or a recompile)\u201d. A good guideline is: \u201cthe closer this number is to 100%, the better.\u201d<\/p>\n Note: There are other things that I could highlight like, plan cache counts is close to the default limit of 160,036<\/a> entries in a 64 bits system.<\/p>\n As always, it is a good practice to ask why<\/a>. Why those guidelines? Why is evaluating those counters important? Why is plan cache reuse important?<\/p>\n Query plan compilation and optimization are known to be CPU-intensive operations; therefore, SQL Server will attempt to reuse cached execution plans wherever possible. Not having to compile (or recompile) and optimize a statement reduces the overhead on the server and speeds up overall performance. This is the main reason to review the counters I mentioned before.<\/p>\n Notice that I mentioned \u201ccompilation\u201d and \u201coptimization.\u201d Those are two different things and are part of the execution plan creation process. Although the cost of a compilation is not low, the optimization phase usually uses more CPU.<\/p>\n In environments with ad hoc intensive workloads, the analysis of performance counter numbers and CPU overhead cost may get tricky (more on that in the \u201cplan cache for ad hoc queries session\u201d of this article). Because of that, it is important to identify the query execution workload. The query plan cache reuse and query execution workload may be one of the following:<\/p>\n One way to identify the workload is to look at the SQL compilations\/sec counter and its relation to the batch requests\/sec. In general, the higher the number of compilations\/sec in relation to the batch requests\/sec, the more likely the environment is experiencing an ad hoc workload.<\/p>\n Another quick option to check the workload is to look at Based on those numbers, the server received many ad hoc queries (an ad hoc workload).<\/p>\n Before I move on with the analysis, I\u2019ll recap how SQL Server manages the plan cache for ad hoc queries, as this is important to understand the scenario.<\/p>\n Read also: As I wrote before on simple-talk<\/a>, the chances that a query plan for an ad hoc query to be reused is very low because of the way SQL Server caches ad hoc plans.<\/p>\n In general, the cached plan for an ad hoc query will only be used only in the following conditions:<\/p>\n Take a look at a sample of all the mentioned conditions. To run the tests, I\u2019m using the following script to populate the sample tables:<\/p>\n For example, execute the following four queries in the database.<\/p>\n The first and second queries will use the same plan, but the third and fourth will need to generate a new plan. Notice that the only difference between second and third queries is the comment which is enough to invalidate plan reuse.<\/p>\n Following are the details from the cache DMVs:<\/p>\n As you can see, to take advantage of the reuse of ad hoc query plans, you need to make sure that the queries are identical, character for character. If one query has a new line or an extra space that another one doesn’t have, they will not be treated as the same. If one contains a comment that the other doesn’t have, they will not be identical. If one uses a different case for either identifiers or keywords, even in a DB with a case-insensitive collation, queries will not be considered the same.<\/p>\n Since SQL considered those queries to be different, each call triggered a query plan compilation and optimization.<\/p>\n SQL Server may use \u2018simple parameterization\u2019 to turn the literal values into parameters and increase the ability to match SQL statements for different literal values. When this happens, subsequent queries that follow the same basic parameterized query can use the same plan. For example:<\/p>\n Internally, SQL Server parameterizes the queries as follows:<\/p>\n You can also see the parameterized query in the actual execution plan; notice that the @1 variable is used instead of the literal value:<\/p>\n Following, you can see the details from the cache DMVs:<\/p>\n Notice that the three individual queries with their distinct constants do get cached as ad hoc queries. However, these are only considered shell queries and don\u2019t contain the complete execution plan, only a pointer to the full plan in the corresponding prepared plan.<\/p>\n Let me add more comments about those shell queries because they\u2019re the reason I\u2019m writing this article:<\/p>\n Take a look at the XML for the second row (the one with the ad hoc plan with OrderID = 3):<\/p>\n Notice that there is an attribute ParameterizedPlanHandle with value \u201c0x0600050024F5793700E98CD06C02000001000000000000000000000000000000000000000000000000000000\u201d which is the pointer to the prepared query that has the full execution plan.<\/p>\n By default, SQL Server simple-parameterizes a relatively small class of queries. The engine is very conservative about deciding when to use simple parameterization for a query. It will only parameterize queries if the prepared query plan is considered to be safe.<\/p>\n Read also:\u00a0 You can enable forced parameterization at the DB level to enable parameterization for all queries in the database, subject to certain limitations (for a list of limitations and exceptions, check the BOL<\/a>).<\/p>\n Once forced param is enabled, the result will be pretty much the same as simple-param, but for all queries.<\/p>\n For example, consider the following queries that would not be simple-parameterized but are parameterized under forced param:<\/p>\n Following, you can see the details from the cache DMVs:<\/p>\n It is a best practice to use stored procedures or rely on data access methods parameterization using param markers to increase the reuse of execution plans. For example, consider the same query used with forced param. Once the developer identifies all the parameters in the query, they can use parameter markers (question marks) to replace a constant in an SQL statement and are bound to a program variable. Using parameters at the application, you would see the following calls in the SQL Server:<\/p>\n Following, you can see the details from the cache DMVs:<\/p>\n When using Note: It is worthy of adding a note in favor of parameterization. Using parameters to hold values that end users type is more secure than concatenating the values into a string that is then executed by using either a data access API method, the EXECUTE statement, or the sp_executesql stored procedure. SQL injection, anyone?<\/p>\n Now that you understand how SQL manages plan cache for ad hoc queries, it\u2019s time to get back to the analysis.<\/p>\n Once I looked at the batch request\/sec and compilations\/sec numbers, I started to question myself: How can I have such a high number of compilations\/sec if most requests use forced parameterization? (notice that forced param\/sec counter is pretty much the same as compilations\/sec). If an ad hoc query execution is parameterized (via forced param), how could I still have a compilation? Shouldn\u2019t they use the \u201cprepared\u201d plan and count as a cache hit (found the plan in cache)?<\/p>\n Remember, on both simple-param and forced-param, SQL Server also adds the ad hoc shell query in the plan cache. When it happens, since the plan for the ad hoc query is not in cache, it counts as a cache miss (couldn\u2019t find query plan in cache). This will decrease the plan cache hit ratio and increases compilations\/sec. However, there is an important thing to consider: those compilations\/sec are not triggering an optimization, so the CPU cost of the compilation is not so bad. Since it is not triggering the optimization, it may use less CPU.<\/p>\n In other words, considering CPU usage, a high number of compilation\/sec per batch requests\/sec may not necessarily be a problem. This is still definitely something of concern if you consider the internal\/local memory pressure it may cause. As a result of having many ad hoc plans in cache, you may have the following problems:<\/p>\n Considering that, I decided to check the internal memory pressure condition. I started by looking at the wait CMEMTHREAD, and it is indeed there.<\/p>\n I\u2019ve also looked at DMV I also noticed that this was removing avg of 161k entries every 5 seconds.<\/p>\n Luckily, the number of CPUs (96) available in the server was good enough to keep up with the workload and avoid CPU pressure, but that only happened because it has forced parameterization enabled in all DBs in the instance. This was helping to avoid the full optimization of all the ad hoc queries, which I\u2019m sure would make the CPU go to 100% in just a few seconds if it wasn\u2019t the case. Forced parameterization does fix the issue of optimizing the query plan for each ad-hoc query execution, but it does not<\/em><\/strong> fix the issue of compiling the plan and bloating the cache with garbage plans which may cause internal memory pressure and consequently the plan cache to be cleaned and the spinlock contention.<\/p>\n The lesson I learned was, from now on, I\u2019ll look not only at batch requests\/sec, compilations\/sec and plan cache hit ratio, but also in another important counter which is the \u201cWorkload Group Stats: Query optimizations\/sec\u201d. When considering this counter, it would be easier to identify those cases where ad hoc queries are causing a high number of compilations but not necessarily triggering optimization. For instance, the following is the same image as before, but now with the info about the query optimizations\/sec:<\/p>\n Notice that out of 20905 compilations per second, there were only 247 query optimizations per second\u2014a much better picture of what is going on with the server. The general guideline of \u201cCompilations\/sec should be less than 10% of the Batch Request\/sec\u201d is still valid as a good practice, but, as you know, a general guideline doesn\u2019t apply for all scenarios.<\/p>\n Here are some tests to simulate the same scenario I presented before to see it\u2019s possible to identify the problems and analyze the alternatives and the benefits it may provide.<\/p>\n To run the tests, I created the following PowerShell script to run an ad hoc query 50000 times.<\/p>\n Then, I\u2019m using SQLQueryStress to call the script with 100 threads using Before I run the tests, I\u2019m also enabling optimize for ad hoc workloads to avoid memory pressure due to the size of the plan caches using the following code:<\/p>\n For the first execution, I tried the script with simple parameterization using 100 concurrent threads. Since the query is a bit complex, it won\u2019t be auto-parameterized, which means it will trigger a compilation and an optimization for each call. The script took 12 minutes and 16 seconds to run, and while it was running, I saw the following counter numbers:<\/p>\n As per the image above, I was able to run an average of 7597 batch requests per second. After a few seconds, I hit the limit of entries in the plan cache. Also, as expected, each ad hoc query execution is compiled and optimized. Since those operations are CPU intensive, with only 7597 batch requests\/sec of a very lightweight (query doesn\u2019t return anything and is very fast) query, it is using almost 100% of CPU in my 96 cores VM. That\u2019s a lot of CPU to do nothing other than creating execution plans.<\/p>\n For the second execution, I tried the script with forced parameterization and using the same 100 concurrent threads as before. This will trigger optimization only for the first call of the query, and all subsequent calls will reuse the prepared plan. The script took only 58 seconds to run, and while it was running, I saw the following counter numbers:<\/p>\n As per the image above, I was able to run an average of 94 thousand batch requests per second, and as expected, the compilations\/sec counter is showing the same 94 thousand values as each ad hoc query counts as a compilation, although there were no optimizations. Because there was no extra CPU cost related to the optimizations for each call, I was able to run a lot more requests per second.<\/p>\n For the third execution, I tried the script with a query parameterized from the application. Again, I used the same 100 concurrent threads as before.<\/p>\n To parameterize it from the PS script, I\u2019ve changed the following part of the code:<\/p>\n Now, the script took only 51 seconds to run, and while it was running, I saw the following counter numbers:<\/p>\n This is 106469 batch requests per second with CPU usage averaging at 90%. At this time, I thought, well, CPU at 90%, I think I can add a few more threads to see how it goes. Here is the result of the execution using 150 threads:<\/p>\n 125428 batch requests\/sec, that\u2019s a lot of requests \ud83d\ude0a.<\/p>\n This will work under simple or forced parameterization, so, it is a good way of guarantee a good parameterized behavior.<\/p>\n Following, you can see a table with the summarized results. Using parameterized queries from the application, I was able to run 125 thousand batch requests per second, which is impressive.<\/p>\n Scenario<\/strong><\/p>\n<\/td>\n Parameterization<\/strong><\/p>\n<\/td>\n Threads<\/strong><\/p>\n<\/td>\n Avg of batch requests\/sec<\/strong><\/p>\n<\/td>\n Time to run all requests<\/strong><\/p>\n<\/td>\n<\/tr>\n Test 1<\/p>\n<\/td>\n Simple without auto param<\/p>\n<\/td>\n 100<\/p>\n<\/td>\n 7597<\/p>\n<\/td>\n 12 minutes and 16 seconds<\/p>\n<\/td>\n<\/tr>\n Test 2<\/p>\n<\/td>\n Forced<\/p>\n<\/td>\n 100<\/p>\n<\/td>\n 94988<\/p>\n<\/td>\n 58 seconds<\/p>\n<\/td>\n<\/tr>\n Test 3<\/p>\n<\/td>\n sp_executesql<\/p>\n<\/td>\n 100<\/p>\n<\/td>\n 106469<\/p>\n<\/td>\n 51 seconds<\/p>\n<\/td>\n<\/tr>\n Test 3<\/p>\n<\/td>\n sp_executesql<\/p>\n<\/td>\n 150<\/p>\n<\/td>\n 125428<\/p>\n<\/td>\n 61 seconds<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n Parameterization plays a very important role in the performance of a system. Although simple parameterization, forced parameterization, and optimize for ad hoc workload features can help to minimize the lack of parameterization, it is a good practice to parameterize queries at the application side or use stored procedures.<\/p>\n Using stored procedures is also good because it usually avoids issues with implicit conversions due to wrong parameter data types (although even on SPs users can always use wrong datatypes if variable type is different than table column). This will reduce memory consumption, reduce memory pressure, and reduce CPU usage by saving the compilation and optimization time of each query.<\/p>\n If you liked this article, you might also like\u00a0SQL Server performance monitor data: Introduction and usage<\/a><\/p>\n\n\n\n
SQL Server plan cache \u2013 Compilation and optimization phases<\/h2>\n
\n
sp_executesql<\/code> or the prepare and execute method invoked through the data access API (ADO.NET, OLE DB, ODBC, JDBC and etc.)<\/li>\nsys.dm_exec_cached_plans<\/code> DMV. For instance, shown in the following image (script based on Erin\u2019s<\/a> script), the number of ad hoc plans with only one use is very high. The ratio of how many single-use count plans compared to all cached plans is 80%. This is a good indication that this is an ad hoc workload. Also, notice that SQL is using only 415MB to store 156883 ad hoc plans in cache; that is a good indication that the instance probably has the \u201coptimize for ad hoc workloads\u201d<\/a> server configuration option enabled.<\/p>\n![\"Results](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2021\/07\/word-image-66.png\")
<\/p>\n
<\/strong>The default trace for performance auditing<\/span><\/a>
Identifying and solving index scan problems<\/span><\/a>
<\/p>\nPlan cache for ad hoc queries<\/h2>\n
\n
PARAMETERIZATION FORCED<\/code> query hint.<\/li>\n<\/ul>\nUSE Northwind\nGO\n-- Run tests without ad hoc workloads\nEXEC sys.sp_configure N'show advanced options','1';\nGO\nRECONFIGURE WITH OVERRIDE;\nGO\nEXEC sys.sp_configure N'optimize for ad hoc workloads', N'0'\nGO\nRECONFIGURE WITH OVERRIDE\nGO\nIF OBJECT_ID('OrdersBig') IS NOT NULL\n DROP TABLE OrdersBig\nGO\nSELECT TOP 500000\n IDENTITY(Int, 1,1) AS OrderID,\n ABS(CheckSUM(NEWID()) \/ 10000000) AS CustomerID,\n CONVERT(Date, GETDATE() - (CheckSUM(NEWID()) \/ 1000000)) AS OrderDate,\n ISNULL(ABS(CONVERT(Numeric(18,2), (CheckSUM(NEWID()) \/ 1000000.5))),0) AS Value,\n CONVERT(VarChar(250), NEWID()) AS Col1\n INTO OrdersBig\n FROM master.dbo.spt_values A\n CROSS JOIN master.dbo.spt_values B CROSS JOIN master.dbo.spt_values C CROSS JOIN master.dbo.spt_values D\nGO\nALTER TABLE OrdersBig ADD CONSTRAINT xpk_OrdersBig PRIMARY KEY(OrderID)\nGO\nIF OBJECT_ID('CustomersBig') IS NOT NULL\n DROP TABLE CustomersBig\nGO\nSELECT TOP 500000\n IDENTITY(Int, 1,1) AS CustomerID,\n SubString(CONVERT(VarChar(250),NEWID()),1,20) AS CompanyName, \n SubString(CONVERT(VarChar(250),NEWID()),1,20) AS ContactName, \n CONVERT(VarChar(250), NEWID()) AS Col1, \n CONVERT(VarChar(250), NEWID()) AS Col2\n INTO CustomersBig\n FROM master.dbo.spt_values A\n CROSS JOIN master.dbo.spt_values B CROSS JOIN master.dbo.spt_values C CROSS JOIN master.dbo.spt_values D\nGO\nALTER TABLE CustomersBig ADD CONSTRAINT xpk_CustomersBig PRIMARY KEY(CustomerID)\nGO\nIF OBJECT_ID('ProductsBig') IS NOT NULL\n DROP TABLE ProductsBig\nGO\nSELECT TOP 500000 IDENTITY(Int, 1,1) AS ProductID, \n SubString(CONVERT(VarChar(250),NEWID()),1,20) AS ProductName, \n CONVERT(VarChar(250), NEWID()) AS Col1\n INTO ProductsBig\n FROM master.dbo.spt_values A\n CROSS JOIN master.dbo.spt_values B CROSS JOIN master.dbo.spt_values C CROSS JOIN master.dbo.spt_values D\nGO\nUPDATE ProductsBig SET ProductName = 'Produto qualquer'\nWHERE ProductID = 1\nGO\nALTER TABLE ProductsBig ADD CONSTRAINT xpk_ProductsBig PRIMARY KEY(ProductID)\nGO\nIF OBJECT_ID('Order_DetailsBig') IS NOT NULL\n DROP TABLE Order_DetailsBig\nGO\nSELECT OrdersBig.OrderID,\n ISNULL(CONVERT(Integer, CONVERT(Integer, ABS(CheckSUM(NEWID())) \/ 1000000)),0) AS ProductID,\n GetDate() - ABS(CheckSUM(NEWID())) \/ 1000000 AS Shipped_Date,\n CONVERT(Integer, ABS(CheckSUM(NEWID())) \/ 1000000) AS Quantity\n INTO Order_DetailsBig\n FROM OrdersBig\nGO\nALTER TABLE Order_DetailsBig ADD CONSTRAINT [xpk_Order_DetailsBig] PRIMARY KEY([OrderID], [ProductID])\nGO\nCREATE INDEX ixContactName ON CustomersBig(ContactName)\nCREATE INDEX ixProductName ON ProductsBig(ProductName)\nCREATE INDEX ixCustomerID ON OrdersBig(CustomerID) INCLUDE(Value)\nCREATE INDEX ixProductID ON Order_DetailsBig(ProductID) INCLUDE(Quantity)\nCREATE INDEX ixCol1OrderDate ON OrdersBig(Col1, OrderDate) INCLUDE(CustomerID, Value)\nGO<\/pre>\nStatement match<\/h2>\n
-- Statement match\n-- SET PARAMETERIZATION to SIMPLE \nALTER DATABASE Northwind SET PARAMETERIZATION SIMPLE;\nGO\nDBCC FREEPROCCACHE()\nGO\nSELECT * FROM CustomersBig WHERE ContactName = 'Fabiano' OR CompanyName = 'A company'\nGO\nSELECT * FROM CustomersBig WHERE ContactName = 'Fabiano' OR CompanyName = 'A company'\nGO\nSELECT * \/*important comment*\/ FROM CustomersBig WHERE ContactName = 'Fabiano' OR CompanyName = 'A company'\nGO\nSELECT * FROM CustomersBig WHERE ContactName = 'Amorim' OR CompanyName = 'A company'\nGO<\/pre>\n
SELECT a.plan_handle, \n a.usecounts,\n a.cacheobjtype,\n a.objtype,\n a.size_in_bytes,\n b.text,\n c.query_plan\n FROM sys.dm_exec_cached_plans a\n OUTER APPLY sys.dm_exec_sql_text (a.plan_handle) b\n OUTER APPLY sys.dm_exec_query_plan (a.plan_handle) c\n WHERE b.\"text\" NOT LIKE '%sys.%'\n AND b.\"text\" LIKE '%CustomersBig%'\n ORDER BY a.usecounts DESC\nGO<\/pre>\n
![\"Results](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2021\/07\/word-image-67.png\")
<\/p>\nSimple parameterization<\/h2>\n
-- Auto-param\n-- SET PARAMETERIZATION to SIMPLE \nALTER DATABASE Northwind SET PARAMETERIZATION SIMPLE;\nGO\nDBCC FREEPROCCACHE()\nGO\nSELECT * FROM OrdersBig WHERE OrderID = 1\nGO\nSELECT * FROM OrdersBig WHERE OrderID = 2\nGO\nSELECT * FROM OrdersBig WHERE OrderID = 3\nGO<\/pre>\n
SELECT * FROM [OrdersBig] WHERE [OrderID]=@1<\/pre>\n
![\"Execution](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2021\/07\/word-image-68.png\")
<\/p>\nSELECT a.plan_handle, \n a.usecounts,\n a.cacheobjtype,\n a.objtype,\n b.text,\n c.query_plan\n FROM sys.dm_exec_cached_plans a\n OUTER APPLY sys.dm_exec_sql_text (a.plan_handle) b\n OUTER APPLY sys.dm_exec_query_plan (a.plan_handle) c\n WHERE b.\"text\" NOT LIKE '%sys.%'\n AND b.\"text\" LIKE '%OrdersBig%'\n ORDER BY a.usecounts DESC\nGO<\/pre>\n
![\"Cached](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2021\/07\/word-image-69.png\")
<\/p>\n\n
<ShowPlanXML xmlns=\"http:\/\/schemas.microsoft.com\/sqlserver\/2004\/07\/showplan\" Version=\"1.539\" Build=\"15.0.4138.2\">\n <BatchSequence>\n <Batch>\n <Statements>\n <StmtSimple StatementText=\"SELECT * FROM OrdersBig WHERE OrderID = 3\" StatementId=\"1\" StatementCompId=\"1\" StatementType=\"SELECT\" RetrievedFromCache=\"true\" ParameterizedPlanHandle=\"0x0600050024F5793700E98CD06C02000001000000000000000000000000000000000000000000000000000000\" ParameterizedText=\"(@1 tinyint)SELECT * FROM [OrdersBig] WHERE [OrderID]=@1\" \/>\n <\/Statements>\n <\/Batch>\n <\/BatchSequence>\n<\/ShowPlanXML><\/pre>\n
<\/strong>Query Store for diagnosing parameter sniffing
Fixing cache bloat with guide plans and forced parameterization
<\/span><\/a><\/p>\nForced parameterization<\/h2>\n
-- Forced-param\n-- SET PARAMETERIZATION to FORCED \nALTER DATABASE Northwind SET PARAMETERIZATION FORCED;\nGO\nDBCC FREEPROCCACHE()\nGO\nSELECT * FROM OrdersBig WHERE OrderID = 1 OR Value < 0.2\nGO\nSELECT * FROM OrdersBig WHERE OrderID = 2 OR Value < 0.4\nGO\nSELECT * FROM OrdersBig WHERE OrderID = 3 OR Value < 0.7\nGO<\/pre>\n
![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2021\/07\/word-image-70.png\")
<\/p>\nStored procedures and sp_executesql<\/h2>\n
-- sp_executesql\n-- SET PARAMETERIZATION to SIMPLE\nALTER DATABASE Northwind SET PARAMETERIZATION SIMPLE;\nGO\nDBCC FREEPROCCACHE()\nGO\nEXEC sp_executesql N'SELECT * FROM OrdersBig WHERE OrderID = @OrderID OR Value < @Value', \n N'@OrderID Int, @Value NUMERIC(18,2)',\n @OrderID =1,\n @Value = 0.2\nGO\nEXEC sp_executesql N'SELECT * FROM OrdersBig WHERE OrderID = @OrderID OR Value < @Value', \n N'@OrderID Int, @Value NUMERIC(18,2)',\n @OrderID = 2,\n @Value = 0.4\nGO\nEXEC sp_executesql N'SELECT * FROM OrdersBig WHERE OrderID = @OrderID OR Value < @Value', \n N'@OrderID Int, @Value NUMERIC(18,2)',\n @OrderID = 3,\n @Value = 0.7\nGO<\/pre>\n
![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2021\/07\/word-image-71.png\")
<\/p>\nsp_executesql<\/code>, there are no entries for the ad hoc shell query (unparameterized) queries. Not having the ad hoc shell queries in the plan cache is a key factor for the analysis I was doing, as 80% of the plan cache was filled with those shell queries.<\/p>\nBack to the scenario analysis<\/h2>\n
\n
![\"Memory](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2021\/07\/word-image-72.png\")
<\/p>\nsys.dm_os_memory_cache_clock_hands<\/code>, This DMV has information about how many clock rounds have been made for each cache store. The query below returns information about cache stores with most entries removed in the last round. Notice that internal (internal and external clock hand distinguishes internal and external memory pressure respectively) clock hand for CACHESTORE_SQLCP<\/code> indicates the number of entries (plans) removed in the last round was 161098 (pretty much everything).<\/p>\nSELECT mcch.name,\n mcch.[type],\n mcch.clock_hand,\n mcch.clock_status,\n SUM(mcch.rounds_count) AS rounds_count,\n SUM(mcch.removed_all_rounds_count) AS cache_entries_removed_all_rounds,\n SUM(mcch.removed_last_round_count) AS cache_entries_removed_last_round,\n SUM(mcch.updated_last_round_count) AS cache_entries_updated_last_round,\n SUM(mcc.pages_kb) AS cache_pages_kb,\n SUM(mcc.pages_in_use_kb) AS cache_pages_in_use_kb,\n SUM(mcc.entries_count) AS cache_entries_count,\n SUM(mcc.entries_in_use_count) AS cache_entries_in_use_count,\n CASE\n WHEN mcch.last_tick_time\n BETWEEN -2147483648 AND 2147483647\n AND si.ms_ticks\n BETWEEN -2147483648 AND 2147483647 THEN\n DATEADD(ms, mcch.last_tick_time - si.ms_ticks, GETDATE())\n WHEN mcch.last_tick_time \/ 1000\n BETWEEN -2147483648 AND 2147483647\n AND si.ms_ticks \/ 1000\n BETWEEN -2147483648 AND 2147483647 THEN\n DATEADD(s, (mcch.last_tick_time \/ 1000) - (si.ms_ticks \/ 1000), GETDATE())\n ELSE\n NULL\n END AS last_clock_hand_move\nFROM sys.dm_os_memory_cache_counters mcc (NOLOCK)\n INNER JOIN sys.dm_os_memory_cache_clock_hands mcch (NOLOCK)\n ON mcc.cache_address = mcch.cache_address\n CROSS JOIN sys.dm_os_sys_info si (NOLOCK)\nWHERE mcch.rounds_count > 0\nGROUP BY mcch.name,\n mcch.[type],\n mcch.clock_hand,\n mcch.clock_status,\n mcc.pages_kb,\n mcc.pages_in_use_kb,\n mcch.last_tick_time,\n si.ms_ticks,\n mcc.entries_count,\n mcc.entries_in_use_count\nORDER BY SUM(mcch.removed_all_rounds_count) DESC,\n mcch.[type];<\/pre>\n
![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2021\/07\/word-image-73.png\")
<\/p>\n![\"Perfmon](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2021\/07\/word-image-74.png\")
<\/p>\nTime for some tests<\/h2>\n
Clear-Host\n$ScriptBlock = {\n $conn = New-Object System.Data.SqlClient.SqlConnection(\"Server=vm3;Database=Northwind;User ID=sa;Password=-------;\")\n $conn.Open()\n $i = 1\n while ($i -le 50000){\n try {\n $sqlCmd = New-Object System.Data.SqlClient.SqlCommand\n $sqlCmd.Connection = $conn\n [string]$Guid = New-Guid \n $query = \"SELECT *, (Select 1) AS Num FROM OrdersBig o \n WHERE o.Col1 = '$Guid'\"\n $sqlCmd.CommandText = $query\n $reader = $sqlCmd.ExecuteReader()\n $reader.Close()\n $i = $i + 1\n } \n catch {\n $ErrorMessage = $_.Exception.Message\n $vGetDate = Get-Date -Format G\n Write-Output \"$vGetDate : Connection Failed \"$ErrorMessage\"... retrying...\"\n }\n }\n $conn.Dispose() \n}\n$current = Get-Date\nInvoke-Command -ScriptBlock $ScriptBlock\n$end= Get-Date\n$diff= New-TimeSpan -Start $current -End $end\nWrite-Output \"Time to run the script: $diff\"\n<#\nexec xp_cmdshell 'powershell -NoLogo -NoProfile \"C:\\Temp\\Test.ps1\"'\n#> <\/pre>\nxp_cmdshell<\/code>. (I know there are easier ways to do it via PS, but that\u2019s good enough for me \ud83d\ude0a)<\/p>\n-- Run tests with ad hoc workloads\nEXEC sys.sp_configure N'optimize for ad hoc workloads', N'1'\nGO\nRECONFIGURE WITH OVERRIDE\nGO<\/pre>\n
Test 1: Ad hoc with simple param triggering compilation and optimization<\/h2>\n
![\"Perfmon](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2021\/07\/word-image-75.png\")
<\/p>\n![\"CPU](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2021\/07\/word-image-76.png\")
<\/p>\nTest 2: Ad hoc with forced param triggering compilation and only 1 optimization<\/h2>\n
![\"Results](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2021\/07\/word-image-77.png\")
<\/p>\nTest 3: Parameterized queries in the application<\/h2>\n
...\n[string]$Guid = New-Guid\n$Col1Param = New-Object System.Data.SqlClient.SqlParameter(\"@Col1\",[Data.SQLDBType]::varchar, 250)\n$Col1Param.Value = $Guid\n$sqlCmd.Parameters.Add($Col1Param) | Out-Null\n$query = \"SELECT * FROM OrdersBig o \n INNER JOIN CustomersBig c ON o.CustomerID = c.CustomerID \n INNER JOIN Order_DetailsBig od ON o.OrderID = od.OrderID \n INNER JOIN ProductsBig p ON od.ProductID = p.ProductID \n WHERE o.Col1 = @Col1\"\n$sqlCmd.CommandText = $query \n...<\/pre>\n
![\"Perfmon](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2021\/07\/word-image-78.png\")
<\/p>\n![\"Result](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2021\/07\/word-image-79.png\")
<\/p>\nTo summarize the results:<\/h2>\n
\n\n
\n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n SQL compilations\/sec<\/h2>\n