Databases Reference
In-Depth Information
LISTING 15-31:
Single-use ad-hoc queries
-- Find single-use, ad-hoc queries that are bloating the plan cache
SELECT TOP(20) [text] AS [QueryText], cp.size_in_bytes
FROM sys.dm_exec_cached_plans AS cp WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE cp.cacheobjtype = N'Compiled Plan'
AND cp.objtype = N'Adhoc'
AND cp.usecounts = 1
ORDER BY cp.size_in_bytes DESC OPTION (RECOMPILE);
-- Gives you the text and size of single-use ad-hoc queries that
-- waste space in the plan cache
-- Enabling 'optimize for ad hoc workloads' for the instance
-- can help (SQL Server 2008 and above only)
-- Enabling forced parameterization for the database can help, but test first!
This query returns the query text and size of your largest (in terms of memory usage) single-use, ad
hoc query plans wasting space in your plan cache. If you see a lot of single-use, ad hoc query plans
in your plan cache, you should consider enabling the instance-level optimize for ad hoc workloads
setting (also see Chapter 3). This setting enables SQL Server 2008 and later to store a much smaller
version of the ad hoc execution plan in the plan cache the i rst time that plan is executed. This can
reduce the amount of memory that is wasted on single-use, ad hoc query plans that are highly likely to
never be reused. Conversely, sometimes the result of enabling this setting is that more of these smaller,
ad hoc plans are stored in the plan cache (because more smaller plans can i t in the same amount of
memory as fewer, larger plans), so you may not see as much memory savings as you anticipated.
Even so, we don't see any good reason not to enable this setting on all SQL Server 2008 and later
instances. When I talked to one of the developers at Microsoft who worked on this feature a couple of
years ago, the only downside to this setting that she could see was a scenario in which you had several
identical ad hoc query plans that would be executed between two and ten times, in which case you
would take a small hit the second time the plan was executed. That seems like an edge case to me.
DATABASE - LE VE L QUE RIES
After running all the server- and instance-level queries, you should have a fairly good idea of which
database or databases are the most resource intensive on a particular instance of SQL Server. In
order to get more details about a particular database, you need to switch your database context to
that database and run a set of database-specii c queries. The code in Listing 15-32 shows how to
switch your database context using T-SQL. Be sure to change the name of the database to the name
of the database that you are interested in investigating further.
LISTING 15-32:
Switching to a user database
-- Database specific queries ******************************************************
-- **** Switch to a user database *****
USE YourDatabaseName;
GO
Search WWH ::
Custom Search