Performance

SQL Server: Plan Cache and Adhoc Workloads

by

Applications generating many dynamic queries (such as ORM frameworks) can lead to a query plan cache bloated by single use plans. Caching something that is only used once is obviously a waste of memory that could otherwise be used to store data pages.

If you have a predominately adhoc workload, turning on ‘optimize for adhoc workloads’ can help reduce the memory footprint of single use plans (it won’t solve the problem entirely though). A system I’ve recently worked on was able to regain 9GB of memory for data pages by turning this setting on.

sp_configure 'show advanced options', 1
GO
reconfigure
GO
 
sp_configure 'optimize for ad hoc workloads', 1
GO
reconfigure
GO

I’ve recently been using a slightly modified version of Kimberly Tripp’s query from her post, Plan cache and optimizing for adhoc workloads to categorise a workload:

SELECT
    CacheType = objtype,
    TotalPlans = COUNT_BIG(*),
    TotalMBs = CAST(SUM(CAST(size_in_bytes AS DECIMAL(18, 2))) / 1024 / 1024 AS decimal(9,2)),
    AverageUseCount = AVG(usecounts),
    TotalMBs1USE = CAST(SUM(CAST((CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END) AS DECIMAL(18, 2))) / 1024 / 1024 AS decimal(9,2)),
    TotalPlans1USE = SUM(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END),
    [%TotalPlans] = CAST(100. * SUM(1) / (SELECT COUNT_BIG(*) FROM sys.dm_exec_cached_plans) AS decimal(9,2)),
    [%TotalMB] = CAST(100. * SUM(CAST(size_in_bytes AS DECIMAL(18,2))) / (SELECT SUM(CAST(size_in_bytes AS DECIMAL(18,2))) FROM sys.dm_exec_cached_plans) AS decimal(9,2))
FROM
    sys.dm_exec_cached_plans
GROUP BY
    objtype

Faster I/O Subsystem Causing More WRITELOG Waits?

by

Paul Randal explains how WRITELOG waits can increase with a faster I/O subsystem:

I was contacted last week by someone who was asking how are faster disks causing more WRITELOG waits. They were seeing lots of these waits, with an average wait time of 18ms. The log was stored on a Raid-1 array, using locally-attached spinning disks in the server. They figured that by moving the log to Raid-1 array of SSDs, they’d reduce the WRITELOG wait time and get better workload throughput.

They did so and got better performance, but were very surprised to now see WRITELOG as the most frequent wait type on the server, even though the average wait time was less than 1ms, and asked me to explain…