Блог

следующая ситуация: у нас есть сервер Azure с базой данных, а диск с базой данных TempDB заполнен. На данный момент мы могли бы найти процесс с большими потребностями в ресурсах в activity monitor. Процесс нельзя было остановить, потому что откат не мог быть выполнен из-за переполненного диска.

Теперь у меня есть два вопроса:

1. Может ли процесс быть причиной огромных файлов tempdb?
2. Кто-нибудь может объяснить, почему процессу требуется так много ресурсов?

Прилагаю скриншот монитора активности и код процедуры.

Процедура запускается каждые 10 секунд с помощью агента SQL Server. Цикл внутри процедуры не такой приятный, но нам нужна была статистика, и она работала хорошо в течение последних 3 недель.

 CREATE PROCEDURE Log_Blocking_Processes
AS
BEGIN
    IF OBJECT_ID('dbo.Log_Blocked_Processes', 'U') IS NULL
        BEGIN
            SELECT  GETDATE() as Tag,
                    db.name DBName,
                    tl.request_session_id,
                    wt.blocking_session_id,
                    OBJECT_NAME(p.OBJECT_ID) BlockedObjectName,
                    tl.resource_type,
                    h1.TEXT AS RequestingText,
                    h2.TEXT AS BlockingText,
                    tl.request_mode,
                    er.transaction_id as Requesting_Transaction_ID,
                    tst.transaction_id as Blocking_Transaction_ID,
                    tat.name as Blocking_Transaction_Name,
                    er.wait_time/1000 as Wait_Time_SEC
            INTO Log_Blocked_Processes
            FROM sys.dm_tran_locks AS tl
            INNER JOIN sys.databases db ON db.database_id = tl.resource_database_id
            INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address
            INNER JOIN sys.partitions AS p ON p.hobt_id = tl.resource_associated_entity_id
            INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_id
            INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id = wt.blocking_session_id
            INNER JOIN sys.dm_exec_requests er ON er.session_id = tl.request_session_id AND er.blocking_session_id = wt.blocking_session_id AND er.blocking_session_id <> 0
            INNER JOIN sys.dm_tran_session_transactions tst ON tst.session_id = wt.blocking_session_id
            INNER JOIN sys.dm_tran_active_transactions tat ON tat.transaction_id = tst.transaction_id
            CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
            CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2
            WHERE db.database_id = DB_ID();

            RETURN;
        END;

    DECLARE @DBName nvarchar(128),
            @request_session_id int,
            @blocking_session_id smallint,
            @BlockedObjectName nvarchar(128),
            @resource_type nvarchar(60),
            @RequestingText nvarchar(MAX),
            @BlockingText nvarchar(MAX),
            @request_mode nvarchar(60),
            @Requesting_Transaction_ID bigint,
            @Blocking_Transaction_ID bigint,
            @Blocking_Transaction_Name nvarchar(32),
            @Wait_Time_SEC int,
            @Tag DATE,
            @Mycount int,
            @LastInsertDay DATE,
            @counter int = 1;

    DECLARE blocked_processes_cursor CURSOR FOR   
        SELECT  cast(GETDATE() as DATE) as Tag,
                db.name DBName,
                tl.request_session_id,
                wt.blocking_session_id,
                OBJECT_NAME(p.OBJECT_ID) BlockedObjectName,
                tl.resource_type,
                h1.TEXT AS RequestingText,
                h2.TEXT AS BlockingText,
                tl.request_mode,
                er.transaction_id as Requesting_Transaction_ID,
                tst.transaction_id as Blocking_Transaction_ID,
                tat.name as Blocking_Transaction_Name,
                er.wait_time/1000 as Wait_Time_SEC
        FROM sys.dm_tran_locks AS tl
        INNER JOIN sys.databases db ON db.database_id = tl.resource_database_id
        INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address
        INNER JOIN sys.partitions AS p ON p.hobt_id = tl.resource_associated_entity_id
        INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_id
        INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id = wt.blocking_session_id
        INNER JOIN sys.dm_exec_requests er ON er.session_id = tl.request_session_id AND er.blocking_session_id = wt.blocking_session_id AND er.blocking_session_id <> 0
        INNER JOIN sys.dm_tran_session_transactions tst ON tst.session_id = wt.blocking_session_id
        INNER JOIN sys.dm_tran_active_transactions tat ON tat.transaction_id = tst.transaction_id
        CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
        CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2
        WHERE db.database_id = DB_ID();

    WHILE @counter <= 4
    BEGIN
        OPEN blocked_processes_cursor  
        FETCH NEXT FROM blocked_processes_cursor INTO @Tag, @DBName, @request_session_id, @blocking_session_id, @BlockedObjectName, @resource_type, @RequestingText, @BlockingText, @request_mode, @Requesting_Transaction_ID, @Blocking_Transaction_ID, @Blocking_Transaction_Name, @Wait_Time_SEC;

        WHILE @@FETCH_STATUS = 0  
        BEGIN
            SELECT @Mycount = count(*), @LastInsertDay = cast(max(Tag) as date) FROM Log_Blocked_Processes WHERE Requesting_Transaction_ID = @Requesting_Transaction_ID AND Blocking_Transaction_ID = @Blocking_Transaction_ID;
            IF @Mycount > 0 AND @LastInsertDay = cast(getdate() as date)
                BEGIN
                    UPDATE Log_Blocked_Processes
                    SET Wait_Time_SEC = @Wait_Time_SEC
                    WHERE Requesting_Transaction_ID = @Requesting_Transaction_ID
                    AND Blocking_Transaction_ID = @Blocking_Transaction_ID;
                END;
            ELSE
                BEGIN
                    INSERT INTO [Log_Blocked_Processes]([Tag],[DBName],[request_session_id],[blocking_session_id],[BlockedObjectName],[resource_type],[RequestingText],[BlockingText],[request_mode],[Requesting_Transaction_ID],[Blocking_Transaction_ID],[Blocking_Transaction_Name],[Wait_Time_SEC])
                    VALUES(GETDATE(),@DBName,@request_session_id,@blocking_session_id,@BlockedObjectName,@resource_type,@RequestingText,@BlockingText,@request_mode,@Requesting_Transaction_ID,@Blocking_Transaction_ID,@Blocking_Transaction_Name,@Wait_Time_SEC)
                END;

            FETCH NEXT FROM blocked_processes_cursor INTO @Tag, @DBName, @request_session_id, @blocking_session_id, @BlockedObjectName, @resource_type, @RequestingText, @BlockingText, @request_mode, @Requesting_Transaction_ID, @Blocking_Transaction_ID, @Blocking_Transaction_Name, @Wait_Time_SEC;
        END;

        SET @counter = @counter   1;
        CLOSE blocked_processes_cursor;
        WAITFOR DELAY '00:00:02';
    END;

    DEALLOCATE blocked_processes_cursor;
END;