vWorkspace single use plans

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:

UPDATE DmSettings
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.

This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s