SQL Server is using part of its memory for storing execution plans of the queries that can be used repeatedly without recompilation. Every compilation of execution plan has its cost including system resources mostly CPU. So is it saving time by caching execution plans for their reuse, this part of memory is called procedure cache. More info about this mechanism can be found in technet article Execution Plan Caching and Reuse.
Now lets consider situation when there are many queries running over SQL Server database. Most of these queries are totally different each other and running not too often. Anyway SQL Server need to store its compiled execution plan in cache having its running costs and use some piece of memory.
Running costs are not something that SQL Server can’t handle easily, but lets imagine that we have tons of queries, concurrency etc. Then we have a lot of running costs for storing execution plans that won’t be used again and using bytes in memory that could be used for something beneficial.
Check what plans we have in cache #
Run below query to check what type of queries are using our query execution plan cache. Query will gather aggregated data from dynamic management view sys.dm_exec_cached_plans and grouped by object type. Object types described in list below:
- Proc: Stored procedure
- Prepared: Prepared statement
- Adhoc: Ad hoc query. Refers to Transact-SQL submitted as language events by using osql or sqlcmd instead of as remote procedure calls.
- ReplProc: Replication-filter-procedure
- Trigger: Trigger
- View: View
- Default: Default
- UsrTab: User table
- SysTab: System table
- Check: CHECK constraint
- Rule: Rule
SELECT S.CacheType, S.Avg_Use, S.Avg_Multi_Use, S.Total_Plan_3orMore_Use, S.Total_Plan_2_Use, S.Total_Plan_1_Use, S.Total_Plan, CAST( (S.Total_Plan_1_Use * 1.0 / S.Total_Plan) as Decimal(18,2) ) AS [Pct_Plan_1_Use], S.Total_MB_1_Use, S.Total_MB, CAST( (S.Total_MB_1_Use * 1.0 / S.Total_MB ) as Decimal(18,2) ) AS [Pct_MB_1_Use] FROM ( SELECT CP.objtype AS [CacheType], COUNT(*) AS [Total_Plan], SUM(CASE WHEN CP.usecounts > 2 THEN 1 ELSE 0 END) AS [Total_Plan_3orMore_Use], SUM(CASE WHEN CP.usecounts = 2 THEN 1 ELSE 0 END) AS [Total_Plan_2_Use], SUM(CASE WHEN CP.usecounts = 1 THEN 1 ELSE 0 END) AS [Total_Plan_1_Use], CAST((SUM(CP.size_in_bytes * 1.0) / 1024 / 1024) AS Decimal(12,2) )[Total_MB], CAST((SUM(CASE WHEN CP.usecounts = 1 THEN (CP.size_in_bytes * 1.0) ELSE 0 END) / 1024 / 1024) AS Decimal(18,2) ) AS [Total_MB_1_Use], CAST(AVG(CP.usecounts * 1.0) as Decimal(12,2)) AS [Avg_Use], CAST(AVG(CASE WHEN CP.usecounts > 1 THEN (CP.usecounts * 1.0) ELSE NULL END) as Decimal(12,2)) AS [Avg_Multi_Use] FROM sys.dm_exec_cached_plans AS CP GROUP BY CP.objtype ) AS S ORDER BY S.CacheType
Above query returns on my test instance running locally on my laptop following results. We can see what object types are cached and their usage. This is starting point of making decision if we can earn some plus points by using server setting Optimize for Ad-Hoc workloads set to true.
To see exact compiled execution plans for queries and its usage statistics can be checked using following query
SELECT usecounts, cacheobjtype, objtype, size_in_bytes, [text] FROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_sql_text(plan_handle)
Results containing count of uses and size in bytes used by this plan, and query text itself
Change server configuration #
Using SQL Server Management Studio:
You can check this setting in server properties using SQL Server Management Studio in tab Advanced in Miscellaneous section.
SP_CONFIGURE 'Show Advanced Options', 1 GO RECONFIGURE GO SP_CONFIGURE 'optimize for ad hoc workloads', 1 GO RECONFIGURE GO
Database scoped configuration #
Microsoft introduced Database Scoped Configuration since SQL Server 2016. This brings many advantages of setting configuration options for individual databases rather than affecting whole instance. One of them are MAXDOP, Cardinality Estimation, clean plan cache and more (maybe in separate post).
One interesting option unfortunately now available only for Azure SQL Database is OPTIMIZE_FOR_AD_HOC_WORKLOADS. As you guess you can enable this just for one database without affecting whole instance behavior. Note that turning this option on or off will clear the plan cache for the database.
You can enable it by running following query against the database you want to alter.
ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZE_FOR_AD_HOC_WORKLOADS = ON;
Hopefully we can get this option available also for on-premise SQL Server databases as well as others that are now available on for Azure SQL DB. You can check Database Scoped Configurations by querying sys.database_scoped_configurations system view.
SELECT * FROM sys.database_scoped_configurations
You can also find some available configurations in database properties using Management Studio under Options tab
Keep in mind that this configuration will definitely not solve all your performance issues. Modifying it require complex view of your workload and data. And of course test on non-production environment first. I wish you good luck on the way to better SQL Server performance.