SQL Server 2014 – How to Query Plan Cache for All Queries Using Table Variable

plan-cachesql serversql server 2014

Is there a way to query the sql plan cache for all query's using a table variable?

Best Answer

One way to do it is you could use the system DMVs to do a wildcard contains search on the cached query plans' text like so:

SELECT databases.[name] AS DatabaseName, Plans.objtype AS ObjectType, PlanText.text AS PlanText
FROM sys.dm_exec_cached_plans AS Plans -- Cached Query Plans
CROSS APPLY sys.dm_exec_sql_text(Plans.plan_handle) AS PlanText -- Query Plan Text
INNER JOIN sys.types -- Data Types (inclusive of User Defined)
    ON PlanText.[text] LIKE '%' + types.[name] + '%'
INNER JOIN master.sys.databases -- Databases
    ON PlanText.[dbid] = databases.database_id
WHERE types.is_user_defined = 1
    AND types.is_table_type = 1 -- Filter on only the User Defined Table Types

This will return the database of the cached query plan, the object type (View, Procedure, Ad-Hoc etc), and the query plan's text itself. One potential downside to this is if you have a user-defined table type that has a very common name used for other things in other queries, and therefor you might get extra results back due to false positives on the contains search.

Starters

Setting the SQL Server option optimize for ad hoc workloads is not really a solution to fix high recompilation values in the query plan cache.

It is however a good solution when your application is performing lots of ad hoc (hence the name) queries that run only once and which would otherwise pollute (waste) the query plan cache space. This can be, for example, an application that allows the users to select the columns of tables dynamically that they wish to see the results of.

Main Course

When SQL Server Database Engine (DBE) executes a query and this query has never been executed before, then the Database Engine has to determine how it will access the data. As soon as the DBE has determined the best way to access the data it will store this information in the Query Plan Cache (QPC), so that the users will benefit the next time the application performs the same query again (albeit, probably with slightly different values).

The SQL Server DBE will search the QPC each time the application requires a statement be run. If the DBE finds an adequate Query Plan in the QPC then it will select that Query Plan to retrieve the data. If however, the DBE is unable to determine an adequate Query Plan in the QPC (not found or timeout value reached for querying the QPC), then the DBE will create a new Query Plan. This is the resulting compiles/s you are observing.

Dessert

The root cause however can vary.

SQL Server may be under memory pressure and is unable to store enough compiled Query Plans in the QPC. The DBE will kick out old plans and insert the new ones. (Solution: Add more memory to the SQL Server instance)

The application is indeed generating a large amount of queries that have never before been executed and/or have slightly different values than the Query Plans stored in the QPC. (Solution: Remove complexity in application)

Answers to your questions

You can't. The query plans belong in the query plan cache. You can either clear the QPC or leave the DBE to do its best. (Selective deletion of query plans can be achieved with DBCC FREEPROCCACHE(, but I wouldn't recommend this.)
Determine what query plans are stored in the QPC and optimise the application and/or the memory settings for the SQL Server instance. (See script in Cigar Lounge and join with sys.dm_exec_sql_text and/or other DMV according to description for plan_handle column in sys.dm_exec_cached_plans documentation)

Cigar Lounge

The following query will list all cached plans stored in the QPC, and can be linked to other relevant DMVs to retrieve additional information:

SELECT plan_handle, ecp.memory_object_address AS CompiledPlan_MemoryObject,   
    omo.memory_object_address, pages_allocated_count, type, page_size_in_bytes   
FROM sys.dm_exec_cached_plans AS ecp   
JOIN sys.dm_os_memory_objects AS omo   
    ON ecp.memory_object_address = omo.memory_object_address   
    OR ecp.memory_object_address = omo.parent_address  
WHERE cacheobjtype = 'Compiled Plan';  
GO

Reference: sys.dm_exec_cached_plans (Transact-SQL) (Microsoft Docs)

Returns a row for each query plan that is cached by SQL Server for faster query execution. You can use this dynamic management view to find cached query plans, cached query text, the amount of memory taken by cached plans, and the reuse count of the cached plans.

Reference Material

Why would I NOT use the SQL Server option “optimize for ad hoc workloads”? (DBA Stackexchange)
sys.dm_exec_query_plan (Transact-SQL) (Microsoft Docs)
sys.dm_exec_cached_plans (Transact-SQL) (Microsoft Docs)
Troubleshooting Plan Cache Issues (Microsoft MSDN)
sp_BlitzFirst® Result: High Compilations per Second (Brent Ozar)
Forcing Query Plans (Microsoft Technet)

SQL Server 2016 – Queries Recompiling Due to Plan Cache Corruption

You should enable Lock Pages in Memory (LPIM).

On SQL Server 2016+, I've encountered a similar scenario that I believe to be a bug. When the server experiences memory pressure, and LPIM is not be enabled, SQL Server will clear the plan cache as part of it's process to release memory back to the OS. This much in normal. SQL Server should only do this when a low memory condition exists.

In some cases, SQL Server will continue to clear the plan cache, even after the low memory condition is resolved. This results in the behavior you are seeing. On a busy server, you will see some plans entering the plan cache, only to get purged seconds later. This is not the proper behavior.

Enabling LPIM will resolve the issue. Enabling LPIM is a best practice for all SQL Server installations, so I would recommend enabling this for every SQL Server instance, regardless of version or whether you've experienced this problem.

To enable the lock pages in memory option

On the Start menu, click Run. In the Open box, type gpedit.msc.
On the Local Group Policy Editor console, expand Computer Configuration, and then expand Windows Settings.
Expand Security Settings, and then expand Local Policies.
Select the User Rights Assignment folder.
The policies will be displayed in the details pane.
In the pane, double-click Lock pages in memory.
In the Local Security Setting – Lock pages in memory dialog box, click Add User or Group.
In the Select Users, Service Accounts, or Groups dialog box, select the SQL Server Service account.
Restart the SQL Server Service for this setting to take effect.