SQL Server Management Studio Github Copilot activity

As AI is making it's entry into the workplaces, we started looking into it, to enable the developers to use it directly in SQL Server Management Studio (SSMS). A common complaint I keep bumping into both in our company and when meeting other database professionals around, is the fact that it is not forthcomming in regards to what it actually does/run on the instance.

We needed a way to find out what it does, and this was our solution:

Setting up the Xevent session:

ALTER EVENT SESSION [CaptureSSMSCopilot] ON DATABASE STATE = STOP;
DROP EVENT SESSION  [CaptureSSMSCopilot] ON DATABASE;

CREATE EVENT SESSION [CaptureSSMSCopilot] ON DATABASE
ADD EVENT sqlserver.sql_statement_completed (
    ACTION (
        sqlserver.client_app_name,
        sqlserver.client_hostname,
        sqlserver.sql_text,
        sqlserver.username,
        sqlserver.query_hash
    )
    WHERE sqlserver.client_app_name = N'Microsoft SQL Server Management Studio - GitHub Copilot'
       OR sqlserver.client_app_name = N'Microsoft SQL Server Management Studio - Copilot Completions'
)
ADD TARGET package0.ring_buffer (
    SET max_memory = 51200
)
WITH (
    MAX_DISPATCH_LATENCY = 5 SECONDS
);

ALTER EVENT SESSION [CaptureSSMSCopilot] ON DATABASE STATE = START;

Setup the table and procedure, to capture the data:

DROP TABLE IF EXISTS dbo.CopilotQueryLog;

CREATE TABLE dbo.CopilotQueryLog (
    id                  INT IDENTITY(1,1) NOT NULL,
    sql_hash            BINARY(32)        NOT NULL,  -- SHA2_256 of full sql_text
    sql_text            NVARCHAR(MAX)     NOT NULL,  -- full batch sent by Copilot
    sample_statement    NVARCHAR(MAX)     NULL,       -- one example sub-statement
    client_app          NVARCHAR(256)     NULL,
    username            NVARCHAR(256)     NULL,
    host                NVARCHAR(256)     NULL,
    first_seen          DATETIME2         NOT NULL DEFAULT SYSUTCDATETIME(),
    last_seen           DATETIME2         NOT NULL DEFAULT SYSUTCDATETIME(),
    execution_count     INT               NOT NULL DEFAULT 1,
    total_duration_us   BIGINT            NOT NULL DEFAULT 0,
    total_cpu_us        BIGINT            NOT NULL DEFAULT 0,
    total_logical_reads BIGINT            NOT NULL DEFAULT 0,
    CONSTRAINT PK_CopilotQueryLog      PRIMARY KEY (id),
    CONSTRAINT UQ_CopilotQueryLog_Hash UNIQUE (sql_hash)
);
GO

CREATE OR ALTER PROCEDURE dbo.usp_DrainCopilotXE
AS
BEGIN
    SET NOCOUNT ON;

    SELECT
        HASHBYTES('SHA2_256', LTRIM(RTRIM(
            event_data.value('(action[@name="sql_text"]/value)[1]', 'nvarchar(max)')
        )))                                                                              AS sql_hash,
        LTRIM(RTRIM(
            event_data.value('(action[@name="sql_text"]/value)[1]', 'nvarchar(max)')
        ))                                                                               AS sql_text,
        LTRIM(RTRIM(
            event_data.value('(data[@name="statement"]/value)[1]',  'nvarchar(max)')
        ))                                                                               AS sample_statement,
        event_data.value('(action[@name="client_app_name"]/value)[1]', 'nvarchar(256)') AS client_app,
        event_data.value('(action[@name="username"]/value)[1]',        'nvarchar(256)') AS username,
        event_data.value('(action[@name="client_hostname"]/value)[1]', 'nvarchar(256)') AS host,
        event_data.value('(@timestamp)[1]',                            'datetime2')     AS ts,
        event_data.value('(data[@name="duration"]/value)[1]',          'bigint')        AS duration_us,
        event_data.value('(data[@name="cpu_time"]/value)[1]',          'bigint')        AS cpu_us,
        event_data.value('(data[@name="logical_reads"]/value)[1]',     'bigint')        AS logical_reads
    INTO #xe_raw
    FROM (
        SELECT CAST(target_data AS XML) AS target_xml
        FROM sys.dm_xe_database_session_targets t
        JOIN sys.dm_xe_database_sessions        s ON s.address = t.event_session_address
        WHERE s.name        = 'CaptureSSMSCopilot'
          AND t.target_name = 'ring_buffer'
    ) AS ring
    CROSS APPLY target_xml.nodes('//RingBufferTarget/event') AS x(event_data)
    WHERE event_data.value('(action[@name="sql_text"]/value)[1]', 'nvarchar(max)') IS NOT NULL;

    WITH aggregated AS (
        SELECT
            sql_hash,
            MAX(sql_text)           AS sql_text,
            MAX(sample_statement)   AS sample_statement,
            MAX(client_app)         AS client_app,
            MAX(username)           AS username,
            MAX(host)               AS host,
            MIN(ts)                 AS first_seen,
            MAX(ts)                 AS last_seen,
            COUNT(*)                AS execution_count,
            SUM(duration_us)        AS total_duration_us,
            SUM(cpu_us)             AS total_cpu_us,
            SUM(logical_reads)      AS total_logical_reads
        FROM #xe_raw
        GROUP BY sql_hash
    )
    MERGE dbo.CopilotQueryLog AS tgt
    USING aggregated           AS src ON tgt.sql_hash = src.sql_hash
    WHEN MATCHED THEN UPDATE SET
        tgt.last_seen           = src.last_seen,
        tgt.execution_count     = tgt.execution_count     + src.execution_count,
        tgt.total_duration_us   = tgt.total_duration_us   + src.total_duration_us,
        tgt.total_cpu_us        = tgt.total_cpu_us        + src.total_cpu_us,
        tgt.total_logical_reads = tgt.total_logical_reads + src.total_logical_reads
    WHEN NOT MATCHED BY TARGET THEN INSERT (
        sql_hash, sql_text, sample_statement, client_app, username, host,
        first_seen, last_seen, execution_count,
        total_duration_us, total_cpu_us, total_logical_reads
    ) VALUES (
        src.sql_hash, src.sql_text, src.sample_statement, src.client_app, src.username, src.host,
        src.first_seen, src.last_seen, src.execution_count,
        src.total_duration_us, src.total_cpu_us, src.total_logical_reads
    );

    DROP TABLE #xe_raw;
END;

If you need to capture the data on a regular interval, you should just schedule the newly created procedure.

EXEC dbo.usp_DrainCopilotXE;

Querying our collected data:

SELECT
    LEFT(sql_text, 100)     AS sql_preview,
    sample_statement,
    client_app,
    username,
    execution_count,
    first_seen,
    last_seen,
    total_logical_reads
FROM dbo.CopilotQueryLog
ORDER BY last_seen DESC;