I’ve noticed that there are several subjects where the act of studying a thing affects the thing being studied. I can think of an example in Physics: Measuring the speed of a particle affects what you can know about the location of that particle and vice versa. Or as another example, psychologists have to take care when setting up experiments so as not to influence subjects and inadvertently skew results.
Well, the same thing applies when monitoring the performance or health of SQL Server. The new Performance Data Collector in SQL 2008 helps (not surprisingly) collect performance data. But the data collector can consume about 5% of the machine’s CPU and it has to be taken into account when analyzing the results.
Another case is when executing scripts on SQL Server that view or analyze cached plans. Here’s such a script:
SELECT TOP 10 st.TEXT, qs.*
FROM sys.dm_exec_query_stats qs
CROSS apply sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY total_worker_time DESC
Queries like these can sometimes show up in the results themselves! To filter out these rows from the results, you can add a where clause that looks like this:
SELECT TOP 10 st.TEXT, qs.*
FROM sys.dm_exec_query_stats qs
CROSS apply sys.dm_exec_sql_text(qs.sql_handle) st
WHERE st.TEXT NOT LIKE '%sys.dm_%'
ORDER BY total_worker_time DESC
If you want to be more selective about what queries to filter out, and you have control over the queries, then include a GUID in every query that you want to eliminate. It can be any arbitrary GUID, but it should be the same GUID. This GUID can be used as a tag for SQL Server to use in filtering criteria:
SELECT TOP 10 st.TEXT, qs.*
FROM sys.dm_exec_query_stats qs
CROSS apply sys.dm_exec_sql_text(qs.sql_handle) st
WHERE st.TEXT NOT LIKE '%078D048A-DDD1-4092-9259-3CC175D644F9%'
ORDER BY total_worker_time DESC
The above query will never return itself. It’s a bit like searching Google for:
“database whisperer” -“smudgy didjeridoo”
You’ll find a number of web pages, (including many from this blog), but you’ll never find this particular page that you’re reading. That’s because there’s a recursive thing going on based on the use-mention distinction of a phrase.
