I found that vWorkspace uses a lot of single use plans. However, SQL Server is good at flushing them out when needed. vWorkspace is sharing instances with our client databases, so I’d rather it not take up many resources.
On one of our instances, vWorkspace is using 2 GB of single use plans. 99.6% of the cached plans have been used only once. This instance has no memory pressure, so the plans just stayed in there.
The query with all the single plans is the following:
SET DateTimeValue = GETUTCDATE()
WHERE SectionKey = ‘BrokerTiming’
AND ValueKey = ‘VdiLastTimeMonitored’
AND (CONVERT(VARCHAR(36), DateTimeValue, 109) = ‘May 26 2016 6:06:57:750PM’ OR DateTimeValue IS NULL)
(So vWorkspace, if you’re listening, please parameterize the date in future versions)
On a more active instance, vWorkspace has few single use plans in cache. That is because the memory is needed, and the single use plans are flushed.
We’re planning to enable ‘optimize for ad hoc’ on our servers here, and that should hopefully reduce the amount of space that vWorkspace needs in the plan cache.