3

I'm trying to establish some metrics associated with which tables in our database get joined together the most often. How can I go about accomplishing this?

edit: I should explain my reasoning; we are moving to a microservices-based architecture, and I would like to have join data in order to best inform the domain-driven-design.

Seeing which tables need to be joined most often would allow me to design certain data layer microservices in a way that they will always have access to easy joins of the most frequently-joined tables.

So if Table1 and Table2 are ALWAYS being joined, I can assess this and have them controlled by the same service. If not, I'll have different services control them.

2
  • What kind of query workload do you have? Is it mostly SELECT queries?
    – Joe Obbish
    Feb 22 '17 at 0:46
  • @JoeObbish yes, a heavy heavy amount of Selects
    – Chris
    Feb 22 '17 at 1:02
1

I don't know if you can differentiate joins vs straight queries, but you can use index activity (what version of SQL Server, by the way?) to get an idea of your "hot" tables. This query should get you started, I use it to identify heavily fragmented or heavily used indexes, but it should still get you there. I don't think it will work with heaps that well, but you really shouldn't have many of those (or any) without a good reason.

I've written this mostly myself but of course cribbed and borrowed bits and pieces from others work. My thanks go out to everyone who has ever posted code to the internet.

/**
    Author: Jonathan Fite

    Index Analyzer

    Requires CONTROL permission in the database, VIEW DATABASE STATE and VIEW SERVER STATE

*/

/** Initialize Variables. 

    @DatabaseID - The database we want to examine, must be specified, but defaults to the current database...
    @IndexScanLevel - The level of detail you want to scan to.
        - LIMITED - This is the default or NULL value, only looks at the parent level of the index.  
        - SAMPLED - Detailed look at ~1% of the pages in the index. (Heaps and indexes smaller than 10K Pages use DETAILED)
        - DETAILED - full scan of all pages, heavy IO
    @MinFragmentation - If you want to see only indexes fragmented to a certain level.
    @MinPageCount - To limit to indexes that are over a certain page count.  Small indexes don't typically benefit
        from being defragmented.  A good number to start with is 100

*/

DECLARE @DatabaseID INT;
SET @DatabaseID = (SELECT DB_ID());

DECLARE @IndexScanLevel NVARCHAR(10);
SET @IndexScanLevel = 'SAMPLED';

DECLARE @MinFragmentation DECIMAL(4,2);
SET @MinFragmentation = 0.0;

DECLARE @MinPageCount INT;
SET @MinPageCount = 100;

/** Begin Work on the Query *********************************************************/

--Get Column List Information
DECLARE @ColumnList AS TABLE
    (
    object_id INT NOT NULL
    , index_id  INT NOT NULL
    , IndexedColumns VARCHAR(8000) NULL
    , IncludedColumns VARCHAR(8000) NULL
    );

WITH CTE_ColumnList AS
(
    SELECT IC.object_id
        , IC.index_id, C.name
        , IC.key_ordinal AS Position
        , DisplayName = C.name + CASE WHEN IC.is_descending_key = 1 THEN ' DESC' ELSE '' END
        , IC.is_included_column
    FROM sys.index_columns IC
        INNER JOIN sys.columns C ON C.object_id = IC.object_id AND IC.column_id = C.column_id
)
INSERT INTO @ColumnList
        ( object_id ,
          index_id ,
          IndexedColumns ,
          IncludedColumns
        )
SELECT DISTINCT 
    C.object_id
    , C.index_id
    , IndexedColumns = STUFF((SELECT ', ' + E.DisplayName FROM CTE_ColumnList E WHERE E.object_id = C.object_id AND E.index_id = C.index_id AND E.is_included_column = 0 ORDER BY E.Position FOR XML PATH('')), 1,1,'')
    , IncludedColumns  = STUFF((SELECT ', ' + E.DisplayName FROM CTE_ColumnList E WHERE E.object_id = C.object_id AND E.index_id = C.index_id AND E.is_included_column = 1 ORDER BY E.Position FOR XML PATH('')), 1,1,'')
FROM CTE_ColumnList C;

SELECT DB_NAME(IPS.database_id) AS DatabaseName
    , SchemaName = S.name
    , ObjectName = O.name
    , O.type_desc AS ObjectType
    , I.name AS IndexName
    , IPS.index_type_desc
    , IPS.alloc_unit_type_desc
    , IPS.avg_fragmentation_in_percent
    , IPS.avg_page_space_used_in_percent 
    , IPS.page_count
    , PageSize_MB = (IPS.page_count/128.0)
    , I.is_unique
    , I.fill_factor
    , I.is_disabled
    , I.has_filter
    , I.filter_definition
    , IUS.user_seeks
    , IUS.user_scans
    , IUS.user_lookups
    , IUS.user_updates
    , UsageActivityTotal = IUS.user_seeks + IUS.user_scans + IUS.user_lookups + IUS.user_updates
    , IOS.leaf_insert_count
    , IOS.leaf_update_count
    , IOS.leaf_delete_count
    , OperationalActivityTotal = IOS.leaf_insert_count + IOS.leaf_update_count + IOS.leaf_delete_count
    , LastAccessed = IUS_LastUsed.LastUsed
    , CL.IndexedColumns
    , CL.IncludedColumns
FROM sys.dm_db_index_physical_stats(@DatabaseID, NULL, NULL, NULL, @IndexScanLevel) AS IPS
    INNER JOIN sys.objects O ON O.object_id = IPS.object_id
    INNER JOIN sys.schemas S ON S.schema_id = O.schema_id
    INNER JOIN sys.indexes I ON I.object_id = IPS.object_id AND I.index_id = IPS.index_id
    LEFT OUTER JOIN @ColumnList CL ON CL.object_id = IPS.object_id AND CL.index_id = IPS.index_id
    LEFT OUTER JOIN sys.dm_db_index_usage_stats IUS ON IUS.database_id = IPS.database_id AND IUS.object_id = IPS.object_id AND IUS.index_id = IPS.index_id
    LEFT OUTER JOIN sys.dm_db_index_operational_stats(@DatabaseID, NULL, NULL, NULL) IOS ON IOS.object_id = IPS.object_id AND IOS.index_id = IPS.index_id
    LEFT OUTER JOIN (
                    SELECT S.database_id
                        , S.object_id
                        , S.index_id
                        , MAX(S.lastused) AS LastUsed
                    FROM sys.dm_db_index_usage_stats UNPIVOT (lastused FOR nlastused IN (last_user_seek, last_user_scan, last_user_lookup, last_user_update)) AS S
                    GROUP BY S.database_id
                        , S.object_id
                        , S.index_id
                    ) IUS_LastUsed ON IUS_LastUsed.database_id = IPS.database_id AND IUS_LastUsed.object_id = IPS.object_id AND IUS_LastUsed.index_id = IPS.index_id
WHERE ISNULL(O.is_ms_shipped, 0x0) = 0x0 --Exclude System Generated Objects
    AND ISNULL(I.is_disabled, 0x0) = 0x0 --Exclude Disabled Indexes
    AND ISNULL(IPS.page_count, 0) >= @MinPageCount --Only worry about indexes with worthwhile page counts
    AND ISNULL(IPS.avg_fragmentation_in_percent, 0.0) >= @MinFragmentation --Only display indexes that exceed fragmentation threshold
ORDER BY IPS.avg_fragmentation_in_percent DESC;
3
  • Wow thanks for the response! We are using SQL Server 2014 SP1
    – Chris
    Feb 21 '17 at 22:11
  • I figure some context will help: we are moving to a microservices-based architecture, and I would like to have join data in order to best inform the domain-driven-design. Seeing which tables need to be joined most often would allow me to design certain data layer microservices in a way that they will always have access to easy joins of the most frequently-joined tables. So if Table1 and Table2 are ALWAYS being joined, I can assess this and have them controlled by the same service. If not, I'll have different services control them.
    – Chris
    Feb 21 '17 at 22:55
  • Just read Aaron's approach, it's what I was going to suggest moving toward if you really wanted to slice this by what tables are used in conjunction of other tables. I have an idea of approaching it from another direction, if I have time I'll post it. Feb 22 '17 at 12:55
1

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.

1
  • Hey Aaron! I figured some context would help: We are moving to a microservices-based architecture, and I would like to have join data in order to best inform the domain-driven-design. Seeing which tables need to be joined most often would allow me to design certain data layer microservices in a way that they will always have access to easy joins of the most frequently-joined tables. So if Table1 and Table2 are ALWAYS being joined, I can assess this and have them controlled by the same service. If not, I'll have different services control them.
    – Chris
    Feb 21 '17 at 22:55
1

This is a bit hacky but it should get you some of the way there. We can look at the plan cache and piece out separate SQL statements, try to filter out everything that's not a SELECT, try to create a stored procedure for each query, use the sys.dm_sql_referenced_entities DMV to get dependencies, and insert summary data into a temp table. This won't tell you that table A joined to table B but it will give you a query level summary of which tables were used in each query.

Here I'm assuming that all of your tables belong to one database and that you want to filter out system objects. If either of those aren't true the code may need to be adjusted. I believe that all of the following code will work on SQL Server 2014 except for the DROP OBJECT IF EXISTS statements.

I'm going to use the code to piece out SQL statements from Aaron's answer. Thanks Aaron!

DECLARE
@plan_handle varbinary(64),
@execution_count BIGINT,
@query_text NVARCHAR(MAX),
@proc_create_script NVARCHAR(MAX),
@id INT = 0;

BEGIN

SET NOCOUNT ON;

DROP TABLE IF EXISTS #X_TABLE_AUDIT;

CREATE TABLE #X_TABLE_AUDIT (
ID INT NOT NULL,
TABLE_NAME SYSNAME NOT NULL,
execution_count BIGINT NOT NULL,
query_text NVARCHAR(MAX) NOT NULL,
plan_handle varbinary(64) NOT NULL
);

DECLARE cursor_cached_plans CURSOR LOCAL FAST_FORWARD
FOR
SELECT plan_handle, execution_count, query_text
FROM
(
    SELECT qs.plan_handle
    , qs.execution_count, 
      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) query_text
     FROM sys.dm_exec_query_stats AS qs
    CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) AS st
) t
WHERE LTRIM(t.query_text) LIKE 'SELECT%' OR LTRIM(t.query_text) LIKE 'WITH%';


OPEN cursor_cached_plans  

FETCH NEXT FROM cursor_cached_plans   
INTO @plan_handle, @execution_count, @query_text; 

WHILE @@FETCH_STATUS = 0  
BEGIN  
    DROP PROCEDURE IF EXISTS dbo.X_JRO_PROC;

    SET @proc_create_script = N'CREATE PROCEDURE dbo.X_JRO_PROC AS BEGIN ' + @query_text + ' END';

    SET @id = @id + 1;

    BEGIN TRY
        -- PRINT @proc_create_script;
        EXEC (@proc_create_script);

        INSERT INTO #X_TABLE_AUDIT (ID, TABLE_NAME, execution_count, query_text, plan_handle)
        SELECT DISTINCT @id, referenced_entity_name, @execution_count, @query_text, @plan_handle
        FROM sys.dm_sql_referenced_entities ('dbo.X_JRO_PROC', 'OBJECT')
        WHERE referenced_class_desc = 'OBJECT_OR_COLUMN'
        AND EXISTS
        (
            SELECT 1 
            FROM sys.tables st 
            WHERE st.[NAME] = referenced_entity_name
        );

    END TRY
    BEGIN CATCH
        WAITFOR DELAY '00:00:00'; -- do nothing, the above could error out for lots of reasons...
    END CATCH;

    FETCH NEXT FROM cursor_cached_plans   
    INTO @plan_handle, @execution_count, @query_text; 
END;

CLOSE cursor_cached_plans;  
DEALLOCATE cursor_cached_plans; 

SELECT ID, TABLE_NAME, execution_count, query_text, plan_handle
FROM #X_TABLE_AUDIT
ORDER BY execution_count DESC, ID ASC;

END;

Below is part of the output on a test database. I don't have a rich selection of cached plans.

result set

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.