Get execution plans that were used only once. SQL Server from its nature caches all execution plans, even for queries that are executed once. This behavior can be suppressed by configuration called Optimize For Ad-Hoc Workloads. You can use this query to check if there are some execution plans stored in your plan cache but not used repeatedly. If so many of them such situation can be on of causes for memory bloat problem.
SELECT query_hash, COUNT(*) as number_of_entries FROM sys.dm_exec_query_stats WHERE execution_count = 1 GROUP BY query_hash HAVING COUNT(*) > 1 ORDER BY number_of_entries DESC SELECT TOP 10 stext.text, splan.query_plan FROM sys.dm_exec_query_stats as stat CROSS APPLY sys.dm_exec_sql_text(stat.sql_handle) as stext CROSS APPLY sys.dm_exec_query_plan(stat.plan_handle) as splan WHERE stat.query_hash = -- put hash from previous query here
Results
Script results screen:
Script results detailed description:
| Column name | Data type | Description |
|---|---|---|
| query_hash | binary(8) | Binary hash value calculated on the query and used to identify queries with similar logic. You can use the query hash to determine the aggregate resource usage for queries that differ only by literal values. |
| number_of_entities | int | Count of cached plans for given query. |
| - | - | - |
| text | nvarchar(max) | Text of the SQL query. Is NULL for encrypted objects. |
| query_plan | xml | Contains the compile-time Showplan representation of the query execution plan that is specified with plan_handle. The Showplan is in XML format. One plan is generated for each batch that contains, for example ad hoc Transact-SQL statements, stored procedure calls, and user-defined function calls. Column is nullable. |