This is a bit of a rabbit hole, and not 100% reliable, but first follow my initial line of thought:
- Queries that run often will be captured by sys.dm_exec_query_stats (though this will vary depending on your frequency of service restarts and plan cache recycling).
- When you have the query text, parsing it to find involved tables is hard. Ask my co-workers who wrote tokenization / anonymization in Plan Explorer. :-)
- If SQL Server is modern enough, sys.dm_exec_describe_first_result_set can help, sometimes. It all depends on the text of the batch, the location of the tables involved, and a host of other variables.
- I'm going to loosen your requirement a bit, if that's ok, to state that what you want to audit is any query that involves more than one table (so in addition to joins, you'd also have queries that concatenate unions, use cross apply, etc).
Let's start with something we all have: msdb. If we run the following two queries:
SELECT backup_set_id FROM dbo.backupset;
SELECT b.backup_set_id, m.media_set_id
FROM dbo.backupset AS b
INNER JOIN dbo.backupmediaset AS m
ON b.media_set_id = m.media_set_id;
These are two very simple tables, one is from a single query, the other has a join. If we feed these into the metadata functions:
SELECT * FROM sys.dm_exec_describe_first_result_set
(N'SELECT backup_set_id FROM dbo.backupset;', N'', 1);
SELECT * FROM sys.dm_exec_describe_first_result_set
(N'SELECT b.backup_set_id, m.media_set_id
FROM dbo.backupset AS b
INNER JOIN dbo.backupmediaset AS m
ON b.media_set_id = m.media_set_id;', N'', 1);
We can see that, when we feed query text to this function, we can determine the columns and tables that are at least output by the query. (You should start to see that this adds some limitations - if you join two tables and only output columns from one of them, or use something like EXISTS, the function will only tell you about one of them.)
So let's take this one step further, and extract the query text for our two queries from the query_stats DMV, instead of hard-coding them (we'll hard-code a filter, though, to keep out noise).
SELECT qs.plan_handle, qs.execution_count,
q = SUBSTRING(st.[text],(qs.statement_start_offset + 2) / 2,
(CASE
WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(max), st.text)) * 2
ELSE qs.statement_end_offset + 2
END - qs.statement_start_offset) / 2)
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) AS st
WHERE st.[text] LIKE N'%backup[_]' + N'set[_]id%'
AND st.[text] NOT LIKE N'%[_]describe[_]%';
This should return two rows with three columns: the plan_handle (a big 0x0... value), the execution_count (probably 1), and the query text. Now, next step, let's connect this query to the function:
;WITH src AS
(
SELECT qs.plan_handle, qs.execution_count,
q = SUBSTRING(st.[text],(qs.statement_start_offset + 2) / 2,
(CASE
WHEN qs.statement_end_offset = -1 THEN
LEN(CONVERT(nvarchar(max), st.text)) * 2
ELSE qs.statement_end_offset + 2
END - qs.statement_start_offset) / 2)
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) AS st
WHERE st.[text] LIKE N'%backup[_]' + N'set[_]id%'
AND st.[text] NOT LIKE N'%[_]describe[_]%'
)
SELECT * FROM src
CROSS APPLY sys.dm_exec_describe_first_result_set(src.q,N'',1) AS f;
Now we see three rows, one from the non-join query, and two from the join query. Since we are looking for queries that involve more than one table, and setting aside edge cases like identical table names in multiple schemas or databases, synonyms, etc. we can use grouping to only show those queries that have more than one distinct table name in the function output:
;WITH src AS
(
SELECT qs.plan_handle, qs.statement_start_offset, qs.execution_count,
q = SUBSTRING(st.[text],(qs.statement_start_offset + 2) / 2,
(CASE
WHEN qs.statement_end_offset = -1 THEN
LEN(CONVERT(nvarchar(max), st.text)) * 2
ELSE qs.statement_end_offset + 2
END - qs.statement_start_offset) / 2)
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) AS st
WHERE st.[text] LIKE N'%backup[_]' + N'set[_]id%'
AND st.[text] NOT LIKE N'%[_]describe[_]%'
),
agg AS
(
SELECT src.plan_handle, src.statement_start_offset, f.source_schema, f.source_table,
tablecount = COUNT(*) OVER (PARTITION BY f.source_schema, f.source_table)
FROM src
CROSS APPLY sys.dm_exec_describe_first_result_set(src.q,N'',1) AS f
GROUP BY src.plan_handle, src.statement_start_offset, f.source_schema, f.source_table
)
SELECT src.q, src.execution_count, agg.source_schema, agg.source_table
FROM src INNER JOIN agg ON src.plan_handle = agg.plan_handle
AND src.statement_start_offset = agg.statement_start_offset
WHERE agg.tablecount > 1
ORDER BY src.execution_count DESC, agg.source_schema, agg.source_table;
You'd have to run this often enough to be meaningful (the DMVs used get cleared out on service restarts, for example).
So while this was a fun exercise, I think Jonathan is more on-track - you should be worried about which tables and indexes are busiest, unused, written more than read, etc. Joins in and of themselves aren't something you'll typically need to audit. IMHO.
