SELECT 
    request_session_id AS session_id,
    OBJECT_NAME(resource_associated_entity_id) AS object_name
FROM sys.dm_tran_locks
WHERE resource_type = 'OBJECT';

DECLARE @session_id INT;
SET @session_id = 55;

DECLARE @query VARCHAR(100);
SET @query = 'KILL ' + CAST(@session_id AS VARCHAR);
EXEC(@query);

SELECT 
    t1.resource_type AS lock_type,
    DB_NAME(t1.resource_database_id) AS database_name,
    t1.resource_associated_entity_id AS locked_object,
    t1.request_mode AS waiting_lock_mode,
    t1.request_session_id AS waiting_session,
    t2.wait_duration_ms AS wait_time_ms,
    (
        SELECT text
        FROM sys.dm_exec_requests r
        CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
        WHERE r.session_id = t1.request_session_id
    ) AS waiting_query,
    t2.blocking_session_id AS blocking_session,
    (
        SELECT text
        FROM sys.sysprocesses p
        CROSS APPLY sys.dm_exec_sql_text(p.sql_handle) AS st
        WHERE p.spid = t2.blocking_session_id
    ) AS blocking_query
FROM sys.dm_tran_locks t1
INNER JOIN sys.dm_os_waiting_tasks t2 
    ON t1.lock_owner_address = t2.resource_address;

CREATE PROCEDURE sp_TerminateDatabaseConnections
    @database_name VARCHAR(100)
AS
BEGIN
    IF DB_ID(@database_name) IS NULL
    BEGIN
        PRINT '데이터베이스가 존재하지 않습니다.';
        RETURN;
    END

    DECLARE @kill_command VARCHAR(30);
    DECLARE connection_cursor CURSOR FOR
        SELECT 'KILL ' + CAST(spid AS VARCHAR) AS cmd
        FROM master..sysprocesses
        WHERE db_name(dbid) = @database_name
        AND spid != @@SPID
        AND dbid != 0;

    OPEN connection_cursor;
    FETCH NEXT FROM connection_cursor INTO @kill_command;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        EXEC(@kill_command);
        FETCH NEXT FROM connection_cursor INTO @kill_command;
    END

    CLOSE connection_cursor;
    DEALLOCATE connection_cursor;
END;

SELECT TOP 15
    (qs.total_worker_time / 1000) AS total_cpu_time_ms,
    qs.execution_count AS execution_count,
    (qs.total_worker_time / qs.execution_count / 1000) AS avg_cpu_time_ms,
    qs.last_execution_time AS last_executed,
    (qs.max_worker_time / 1000) AS max_cpu_time_ms,
    SUBSTRING(
        qt.text,
        (qs.statement_start_offset / 2) + 1,
        (
            CASE 
                WHEN qs.statement_end_offset = -1 
                THEN DATALENGTH(qt.text)
                ELSE qs.statement_end_offset
            END - qs.statement_start_offset
        ) / 2 + 1
    ) AS query_text,
    qt.text AS full_query,
    DB_NAME(qt.dbid) AS database_name,
    OBJECT_NAME(qt.objectid, qt.dbid) AS object_name
FROM sys.dm_exec_query_stats qs WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE qs.execution_count > 1
ORDER BY qs.total_worker_time DESC;