tempdb

Skrypt dzięki uprzejmości Igora Yaremenko 🙂

-- create event session
CREATE EVENT SESSION [Database_Growth_Watchdog]
    ON SERVER
    ADD EVENT sqlserver.database_file_size_change
    ( ACTION ( sqlserver.client_app_name ,
               sqlserver.client_hostname ,
               sqlserver.database_name ,
               sqlserver.session_nt_username ,
               sqlserver.sql_text ,
               sqlserver.username )
     WHERE ( [database_id] = ( 2 )))
    ADD TARGET package0.event_file
    ( SET filename = N'D:\Temp\Database_Growth_Wathdog.xel', max_file_size = ( 10 ))
    WITH ( MAX_MEMORY = 4096KB ,
           EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS ,
           MAX_DISPATCH_LATENCY = 1 SECONDS ,
           MAX_EVENT_SIZE = 0KB ,
           MEMORY_PARTITION_MODE = NONE ,
           TRACK_CAUSALITY = OFF ,
           STARTUP_STATE = ON );
GO


-- parsing event session log
DECLARE @TraceFileLocation NVARCHAR(255) = N'D:\Temp\Database_Growth_Wathdog*.xel';
WITH FileSizeChangedEvent
AS ( SELECT object_name Event ,
            CONVERT(XML, event_data) Data
     FROM   sys.fn_xe_file_target_read_file(@TraceFileLocation, NULL, NULL, NULL) ) ,
     FileSizeChangedEventParsed
AS ( SELECT Data.value('(/event/@timestamp)[1]', 'DATETIME') EventTime ,
            Data.query('/event/action[@name=''client_hostname'']/value').value('.', 'VARCHAR(MAX)') ClientHostname ,
            Data.query('/event/action[@name=''username'']/value').value('.', 'VARCHAR(MAX)') ClientUsername ,
            Data.query('/event/action[@name=''client_app_name'']/value').value('.', 'VARCHAR(MAX)') ClientAppName ,
            Data.query('/event/action[@name=''database_name'']/value').value('.', 'VARCHAR(MAX)') ClientAppDBName ,
            Data.query('/event/data[@name=''database_id'']/value').value('.', 'INT') SystemDatabaseId ,
            Data.query('/event/data[@name=''file_name'']/value').value('.', 'VARCHAR(MAX)') SystemDatabaseFileName ,
            Data.query('/event/data[@name=''file_type'']/text').value('.', 'VARCHAR(MAX)') SystemDatabaseFileType ,
            Data.query('/event/data[@name=''is_automatic'']/text').value('.', 'VARCHAR(MAX)') SystemIsAutomaticGrowth ,
            Data.query('/event/data[@name=''size_change_kb'']/value').value('.', 'BIGINT') GrowthInKB ,
            Data.query('/event/data[@name=''total_size_kb'']/value').value('.', 'BIGINT') TotalSizeInKB ,
            Data.query('/event/data[@name=''duration'']/value').value('.', 'BIGINT') SystemDuration ,
            Data.query('/event/action[@name=''sql_text'']/value').value('.', 'VARCHAR(MAX)') SQLCommandText
     FROM   FileSizeChangedEvent )
SELECT *
FROM   FileSizeChangedEventParsed;

-- create event session

CREATE EVENT SESSION [Database_Growth_Watchdog]

ON SERVER

ADD EVENT sqlserver.database_file_size_change

( ACTION ( sqlserver.client_app_name ,

sqlserver.client_hostname ,

sqlserver.database_name ,

sqlserver.session_nt_username ,

sqlserver.sql_text ,

sqlserver.username )

WHERE ( [database_id] = ( 2 )))

ADD TARGET package0.event_file

( SET filename = N'D:\Temp\Database_Growth_Wathdog.xel', max_file_size = ( 10 ))

WITH ( MAX_MEMORY = 4096KB ,

EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS ,

MAX_DISPATCH_LATENCY = 1 SECONDS ,

MAX_EVENT_SIZE = 0KB ,

MEMORY_PARTITION_MODE = NONE ,

TRACK_CAUSALITY = OFF ,

STARTUP_STATE = ON );

-- parsing event session log

DECLARE @TraceFileLocation NVARCHAR(255) = N'D:\Temp\Database_Growth_Wathdog*.xel';

WITH FileSizeChangedEvent

AS ( SELECT object_name Event ,

CONVERT(XML, event_data) Data

FROM sys.fn_xe_file_target_read_file(@TraceFileLocation, NULL, NULL, NULL) ) ,

FileSizeChangedEventParsed

AS ( SELECT Data.value('(/event/@timestamp)[1]', 'DATETIME') EventTime ,

Data.query('/event/action[@name=''client_hostname'']/value').value('.', 'VARCHAR(MAX)') ClientHostname ,

Data.query('/event/action[@name=''username'']/value').value('.', 'VARCHAR(MAX)') ClientUsername ,

Data.query('/event/action[@name=''client_app_name'']/value').value('.', 'VARCHAR(MAX)') ClientAppName ,

Data.query('/event/action[@name=''database_name'']/value').value('.', 'VARCHAR(MAX)') ClientAppDBName ,

Data.query('/event/data[@name=''database_id'']/value').value('.', 'INT') SystemDatabaseId ,

Data.query('/event/data[@name=''file_name'']/value').value('.', 'VARCHAR(MAX)') SystemDatabaseFileName ,

Data.query('/event/data[@name=''file_type'']/text').value('.', 'VARCHAR(MAX)') SystemDatabaseFileType ,

Data.query('/event/data[@name=''is_automatic'']/text').value('.', 'VARCHAR(MAX)') SystemIsAutomaticGrowth ,

Data.query('/event/data[@name=''size_change_kb'']/value').value('.', 'BIGINT') GrowthInKB ,

Data.query('/event/data[@name=''total_size_kb'']/value').value('.', 'BIGINT') TotalSizeInKB ,

Data.query('/event/data[@name=''duration'']/value').value('.', 'BIGINT') SystemDuration ,

Data.query('/event/action[@name=''sql_text'']/value').value('.', 'VARCHAR(MAX)') SQLCommandText

FROM FileSizeChangedEvent )

SELECT *

FROM FileSizeChangedEventParsed;

SELECT  sys.dm_exec_sessions.session_id AS [SESSION ID] ,
        DB_NAME(su.database_id) AS [DATABASE Name] ,
        host_name AS [System Name] ,
        program_name AS [Program Name] ,
        login_name AS [USER Name] ,
        status ,
        cpu_time AS [CPU TIME (in milisec)] ,
        total_scheduled_time AS [Total Scheduled TIME (in milisec)] ,
        total_elapsed_time AS [Elapsed TIME (in milisec)] ,
        ( memory_usage * 8 ) AS [Memory USAGE (in KB)] ,
        ( user_objects_alloc_page_count * 8 ) AS [SPACE Allocated FOR USER Objects (in KB)] ,
        ( user_objects_alloc_page_count * 8 ) / 1024 AS [SPACE Allocated FOR USER Objects (in MB)] ,
        ( user_objects_dealloc_page_count * 8 ) AS [SPACE Deallocated FOR USER Objects (in KB)] ,
        ( user_objects_dealloc_page_count * 8 ) / 1024 AS [SPACE Deallocated FOR USER Objects (in MB)] ,
        ( internal_objects_alloc_page_count * 8 ) AS [SPACE Allocated FOR Internal Objects (in KB)] ,
        ( internal_objects_alloc_page_count * 8 ) / 1024 AS [SPACE Allocated FOR Internal Objects (in MB)] ,
        ( internal_objects_dealloc_page_count * 8 ) AS [SPACE Deallocated FOR Internal Objects (in KB)] ,
        ( internal_objects_dealloc_page_count * 8 ) / 1024 AS [SPACE Deallocated FOR Internal Objects (in MB)] ,
        CASE is_user_process
          WHEN 1 THEN 'user session'
          WHEN 0 THEN 'system session'
        END AS [SESSION Type] ,
        row_count AS [ROW COUNT]
FROM    sys.dm_db_session_space_usage su
        INNER JOIN sys.dm_exec_sessions ON su.session_id = sys.dm_exec_sessions.session_id
ORDER BY [SPACE Allocated FOR USER Objects (in KB)] DESC;

SELECT sys.dm_exec_sessions.session_id AS [SESSION ID] ,

DB_NAME(su.database_id) AS [DATABASE Name] ,

host_name AS [System Name] ,

program_name AS [Program Name] ,

login_name AS [USER Name] ,

status ,

cpu_time AS [CPU TIME (in milisec)] ,

total_scheduled_time AS [Total Scheduled TIME (in milisec)] ,

total_elapsed_time AS [Elapsed TIME (in milisec)] ,

( memory_usage * 8 ) AS [Memory USAGE (in KB)] ,

( user_objects_alloc_page_count * 8 ) AS [SPACE Allocated FOR USER Objects (in KB)] ,

( user_objects_alloc_page_count * 8 ) / 1024 AS [SPACE Allocated FOR USER Objects (in MB)] ,

( user_objects_dealloc_page_count * 8 ) AS [SPACE Deallocated FOR USER Objects (in KB)] ,

( user_objects_dealloc_page_count * 8 ) / 1024 AS [SPACE Deallocated FOR USER Objects (in MB)] ,

( internal_objects_alloc_page_count * 8 ) AS [SPACE Allocated FOR Internal Objects (in KB)] ,

( internal_objects_alloc_page_count * 8 ) / 1024 AS [SPACE Allocated FOR Internal Objects (in MB)] ,

( internal_objects_dealloc_page_count * 8 ) AS [SPACE Deallocated FOR Internal Objects (in KB)] ,

( internal_objects_dealloc_page_count * 8 ) / 1024 AS [SPACE Deallocated FOR Internal Objects (in MB)] ,

CASE is_user_process

WHEN 1 THEN 'user session'

WHEN 0 THEN 'system session'

END AS [SESSION Type] ,

row_count AS [ROW COUNT]

FROM sys.dm_db_session_space_usage su

INNER JOIN sys.dm_exec_sessions ON su.session_id = sys.dm_exec_sessions.session_id

ORDER BY [SPACE Allocated FOR USER Objects (in KB)] DESC;

me -v --db mssql &>> blog

BI subiektywnie i bez cenzury...

Tag: tempdb

Rozrost tempdb – Extended Events + podgląd via TSQL

tempdb – sprawdzenie rozmiarów po sesji