Toad World® Forums

Plan Pollution

Hi,

I have noticed that Spotlight is causing quite a plan pollution with his monitoring queries.

SELECT query_hash ,
COUNT(DISTINCT ( query_hash )) AS DistinctPlanCount ,
COUNT(query_hash) AS PlanCount
FROM sys.dm_exec_query_stats
GROUP BY query_hash
ORDER BY COUNT(query_hash) DESC;

If you look closely at some of the top ones these are either not parameterized queries or different white spaces.

SELECT q.PlanCount ,
q.DistinctPlanCount ,
st.text AS QueryText ,
qp.query_plan AS QueryPlan
FROM ( SELECT query_hash ,
COUNT(DISTINCT ( query_hash )) AS DistinctPlanCount ,
COUNT(query_hash) AS PlanCount
FROM sys.dm_exec_query_stats
GROUP BY query_hash
) AS q
JOIN sys.dm_exec_query_stats qs ON q.query_hash = qs.query_hash
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE PlanCount > 1
AND qs.query_hash = 0x8FD48F3C03B151EB
ORDER BY q.PlanCount DESC;

Hi,

I checked into your concern with our Development team and it seems that this is an unfortunate by-product of the way some DMVs work in combination with the limitations of the USE statement. A lot of the SQL Server DMVs we access have database scope; that is they only return data for the current database context. The USE statement in SQL allows you to set the database context but is restricted such that the database name must be a literal and not a variable. This means that to get data for all databases some scripts must have a loop over all databases that executes a piece of dynamic SQL once for each database. That dynamic SQL is effectively a separate query. The figure of 331 is probably roughly the number of database you have. In our testing we see 133 in the PlanCount column of your query and there are 133 databases on the instance we tested on.

If this doesn’t quite address your concern and you like to look further into this, you can open a formal ticket with the Spotlight Support Team from the link below:

https://support.quest.com/create-service-request

Jon

Visit our Support Portal
https://support.quest.com/spotlight-on-sql-server-enterprise/

Hi Jon,

Thank you for the investigation. We have only 5 databases (not counting system databases). Queries on my 3rd screenshot are repeated for the same databases multiple times.