WordPress.com



/* Script to get various tempdb-oriented outputs. Each option is a separate output, though session-level outputs can optionally be combined.Author: John Kauffman, the Jolly DBA, Issues: tempdb file capacity as reported by sys.master_files is the configured value. it doesn't reflect size changes because of growth. long-lasting locks on sys.objects can cause certain outputs to hang because of the options related to sessions, i collect data in 3 separate pulls. in high volume systems, mis-alignments happen. */set deadlock_priority lowUSE TEMPDBDECLARE @SHOW_OLDEST_TRANSACTION BIT = 0DECLARE @SHOW_LOG_INFO BIT = 1DECLARE @SHOW_PERFMON_COUNTERS BIT = 0 -- STRAIGHT DUMP - DOESN'T COLLECT 2 POINTS AND DISPLAY CALC'D VALUES.DECLARE @SHOW_FILE_UTILIZATION_BY_FUNCTION BIT = 0 -- SPACE USED BY INTERNAL OBJECTS, VERSION STORE, ETC.DECLARE @SHOW_FILE_SPACE BIT = 0 -- KNOWN ISSUE WITH FILE CAPACITY (PULLS VALUE THAT RESETS AT SERVICE RESTART)DECLARE @SHOW_LOCKS_IN_TEMPDB BIT = 0/* USER TABLE OUTPUT, WITH OPTIONS*/DECLARE @SHOW_USER_TABLES_IN_TEMPDB BIT = 0 -- WOULD BE GREAT TO LINK TO SESSIONS GENERATING THEM. MAYBE SOMEDAY...DECLARE @INCLUDE_FILE_BREAKOUT_FOR_USER_TABLES BIT = 0 -- NOT SPEEDY IF TEMPDB IS LARGE!DECLARE @INCLUDE_CACHED_VS_SPOOLED_PAGES BIT = 0 -- CAN BE RUN WITH OR WITHOUT FILE BREAKOUT. NOT SPEEDY IF DATA CACHE IS LARGE!/* session-level outputs*/DECLARE @SHOW_ACTIVE_TRANSACTIONS BIT = 1 -- DATA FROM SYS.DM_TRAN_ACTIVE_SNAPSHOT_DATABASE_TRANSACTIONSDECLARE @SHOW_SESSION_SPACE_USAGE BIT = 1 -- DATA FROM SYS.DM_DB_SESSION_SPACE_USAGEDECLARE @SINGLE_OUTPUT_BY_SESSION_ID BIT = 1 -- if 1, get single output combining SYS.DM_TRAN_ACTIVE_SNAPSHOT_DATABASE_TRANSACTIONS and SYS.DM_DB_SESSION_SPACE_USAGEDECLARE @INCLUDE_SESSION_INFO BIT = 1 -- ADD INFO FROM SYS.SESSIONS / SYS.REQUESTS TO THE SESSION-LEVEL OUTPUTS. DECLARE @SESSION_ID INT = null -- NULL FOR ALL, OR FILTER BY SPID/* IF ASKING FOR CONSOLIDATED OUTPUT, MAKE SURE YOU GENERATE THE SEPARATE COMPONENTS*/IF @SINGLE_OUTPUT_BY_SESSION_ID = 1 BEGIN SET @SHOW_ACTIVE_TRANSACTIONS = 1 SET @SHOW_SESSION_SPACE_USAGE = 1 END -- IF @SINGLE_OUTPUT_BY_SESSION_ID = 1------------------------------------------------------------------------------------------------------------/* OUTPUT - OLDEST OPEN TRANSACTION*/------------------------------------------------------------------------------------------------------------if @show_oldest_transaction = 1 begin IF OBJECT_ID(N'TEMPDB..#OpenTranStatus') is not null DROP TABLE #OpenTranStatus CREATE TABLE #OpenTranStatus ( ActiveTransaction varchar(25), Details sql_variant ) -- Execute the command, putting the results in the table. INSERT INTO #OpenTranStatus EXEC ('DBCC OPENTRAN WITH TABLERESULTS, NO_INFOMSGS'); declare @now datetime = getdate() declare @spid int, @startTime datetime, @duration_seconds int select @spid = cast(details as int) from #OpenTranStatus where ActiveTransaction = 'oldact_spid' select @startTime = cast(details as datetime) from #OpenTranStatus where ActiveTransaction = 'oldact_starttime' select @duration_seconds = datediff(second, @starttime, @now) select 'oldest_transaction', @spid as spid, @starttime as Start_time, @duration_seconds as Duration_secs, cast(@duration_seconds/60.0 as decimal(10, 2)) as duration_minutes end -- if @show_oldest_transaction = 1------------------------------------------------------------------------------------------------------------/* OUTPUT - LOG SIZE*/------------------------------------------------------------------------------------------------------------if @show_log_info = 1 begin select @@SERVERNAME, log_reuse_wait_desc AS TEMPDB_LOG_REUSE_WAIT , (select cntr_value from sys.dm_os_performance_counters c where c.counter_name= 'percent log used' and instance_name = 'tempdb') as pct_log_used , 'Tempdb Log flushes automatically at 70%. if >70%, look at active transactions.' from sys.databases where database_id = 2 end --if @show_log_info = 1------------------------------------------------------------------------------------------------------------/* OUTPUT - PERFMON COUNTERS*/----------------------------------------------------------------------------------------------------------------------------------IF @SHOW_PERFMON_COUNTERS = 1 BEGIN select * from sys.dm_os_performance_counters where instance_name = 'tempdb' or object_name like '%transact%' END -- IF @SHOW_PERFMON_COUNTERS = 1----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------/* OUTPUT - TEMPDB FILE UTILIZATION BY TYPE OF USE (INTERNAL, USER, VERSION STORE)*/------------------------------------------------------------------------------------------------------------------------------------IF @SHOW_FILE_UTILIZATION_BY_FUNCTION = 1 BEGIN SELECT CAST(getdate() AS DATE) AS CAPTURE_DATE , DATEPART(HOUR, getdate()) AS CAPTURE_HOUR , DATEPART(MINUTE, getdate()) AS CAPTURE_MINUTE , GETDATE() AS CAPTURE_DATETIME , CAST(SERVERPROPERTY('SERVERNAME') AS SYSNAME) AS SERVER_INSTANCE , CAST(SUM(USER_OBJECT_RESERVED_PAGE_COUNT)*8.0 /(SUM (UNALLOCATED_EXTENT_PAGE_COUNT)*8 + SUM (VERSION_STORE_RESERVED_PAGE_COUNT)*8 + SUM (INTERNAL_OBJECT_RESERVED_PAGE_COUNT)*8 + SUM (USER_OBJECT_RESERVED_PAGE_COUNT)*8)*100 AS DECIMAL(6, 2)) AS USER_OBJECTS_PCT , CAST(SUM (INTERNAL_OBJECT_RESERVED_PAGE_COUNT)*8.0 /(SUM (UNALLOCATED_EXTENT_PAGE_COUNT)*8 + SUM (VERSION_STORE_RESERVED_PAGE_COUNT)*8 + SUM (INTERNAL_OBJECT_RESERVED_PAGE_COUNT)*8 + SUM (USER_OBJECT_RESERVED_PAGE_COUNT)*8)*100 AS DECIMAL(6, 2)) AS INTERNAL_OBJ_PCT , CAST(SUM (VERSION_STORE_RESERVED_PAGE_COUNT)*8.0 /(SUM (UNALLOCATED_EXTENT_PAGE_COUNT)*8 + SUM (VERSION_STORE_RESERVED_PAGE_COUNT)*8 + SUM (INTERNAL_OBJECT_RESERVED_PAGE_COUNT)*8 + SUM (USER_OBJECT_RESERVED_PAGE_COUNT)*8)*100 AS DECIMAL(6, 2)) AS VERSION_STORE_PCT , CAST(SUM (MIXED_EXTENT_PAGE_COUNT)*8.0 /(SUM (UNALLOCATED_EXTENT_PAGE_COUNT)*8 + SUM (VERSION_STORE_RESERVED_PAGE_COUNT)*8 + SUM (INTERNAL_OBJECT_RESERVED_PAGE_COUNT)*8 + SUM (USER_OBJECT_RESERVED_PAGE_COUNT)*8)*100 AS DECIMAL(6, 2)) AS MIXED_EXTENT_PCT , CAST(SUM (UNALLOCATED_EXTENT_PAGE_COUNT)*8.0 /(SUM (UNALLOCATED_EXTENT_PAGE_COUNT)*8 + SUM (VERSION_STORE_RESERVED_PAGE_COUNT)*8 + SUM (INTERNAL_OBJECT_RESERVED_PAGE_COUNT)*8 + SUM (USER_OBJECT_RESERVED_PAGE_COUNT)*8)*100 AS DECIMAL(6, 2)) AS FREE_PCT , SUM (USER_OBJECT_RESERVED_PAGE_COUNT)*8 AS USER_OBJECTS_KB , SUM (INTERNAL_OBJECT_RESERVED_PAGE_COUNT)*8 AS INTERNAL_OBJECTS_KB , SUM (VERSION_STORE_RESERVED_PAGE_COUNT)*8 AS VERSION_STORE_KB , SUM (MIXED_EXTENT_PAGE_COUNT)*8 AS MIXED_EXTENT_KB , SUM (UNALLOCATED_EXTENT_PAGE_COUNT)*8 AS FREE_SPACE_KB FROM tempdb.SYS.DM_DB_FILE_SPACE_USAGE WHERE DATABASE_ID = 2 -- TEMPDB END -- IF @SHOW_FILE_UTILIZATION_BY_FUNCTION = 1------------------------------------------------------------------------------------------------------------------------------------/* OUTPUT - TEMPDB FILE UTILIZATION*/------------------------------------------------------------------------------------------------------------------------------------IF @SHOW_FILE_SPACE = 1BEGIN IF OBJECT_ID(N'TEMPDB..#FILE_SIZE') is not null DROP TABLE #FILE_SIZE IF OBJECT_ID(N'TEMPDB..#DB_FILES') is not null DROP TABLE #DB_FILES IF OBJECT_ID(N'TEMPDB..##SPACE_USED') is not null DROP TABLE ##SPACE_USED IF OBJECT_ID(N'TEMPDB..##FILE_GROUPS') is not null DROP TABLE ##FILE_GROUPS SELECT D.NAME AS DB_NAME , D.DATABASE_ID AS DB_ID , F.DATA_SPACE_ID AS FILE_GROUP_ID , F.FILE_ID AS FILE_ID , F.TYPE_DESC AS FILE_TYPE_DESC , F.NAME AS FILE_NAME , F.PHYSICAL_NAME AS FILE_PHYSICAL_NAME , CAST(F.SIZE * 8/1024.0 AS DECIMAL(18,2)) AS FILE_CAPACITY_MB , MAX_SIZE AS FILE_MAX_SIZE , GROWTH AS FILE_GROWTH , IS_PERCENT_GROWTH AS FILE_IS_PERCENT_GROWTH , ROW_NUMBER() OVER(ORDER BY D.NAME, F.TYPE_DESC DESC , F.NAME) AS ROW_NUM , QUOTENAME(d.name) as db_quotename INTO #DB_FILES FROM SYS.MASTER_FILES F JOIN SYS.DATABASES D ON D.DATABASE_ID = F.DATABASE_ID where d.state_desc= 'online' and d.name = 'tempdb' DECLARE @COUNTER INT = 1 DECLARE @MAX_COUNTER INT SELECT @MAX_COUNTER = MAX(ROW_NUM) FROM #DB_FILES DECLARE @DB_NAME SYSNAME DECLARE @FILE_ID INT DECLARE @FILE_NAME NVARCHAR(MAX) DECLARE @FILE_GROUP_ID INT DECLARE @SQL_TEXT NVARCHAR(MAX) CREATE TABLE ##SPACE_USED (DB_NAME SYSNAME, FILE_ID INT, FILE_USED_MB DECIMAL(18, 2)) CREATE TABLE ##FILE_GROUPS (DB_NAME SYSNAME, FILE_ID INT, FILE_GROUP_ID INT, FILE_GROUP_NAME SYSNAME) WHILE @COUNTER <= @MAX_COUNTER BEGIN SELECT @DB_NAME = DB_NAME, @FILE_ID = FILE_ID, @FILE_NAME = FILE_NAME, @FILE_GROUP_ID = FILE_GROUP_ID FROM #DB_FILES WHERE ROW_NUM = @COUNTER SET @SQL_TEXT = '' SET @SQL_TEXT = 'USE [@DB_NAME] INSERT INTO ##SPACE_USED(DB_NAME, FILE_ID, FILE_USED_MB) SELECT ''[' + @DB_NAME+ ']'', ' + CAST(@FILE_ID AS NVARCHAR(10)) + ', CAST(FILEPROPERTY(''' + @FILE_NAME + ''',''SPACEUSED'') AS DECIMAL(18, 2))/8.00 /16.00 ' SET @SQL_TEXT = REPLACE(@SQL_TEXT, '@DB_NAME', @DB_NAME) SET @SQL_TEXT = REPLACE(@SQL_TEXT, '@FILE_ID', @FILE_ID) SET @SQL_TEXT = REPLACE(@SQL_TEXT, '@FILE_NAME', @FILE_NAME) EXEC (@SQL_TEXT) SET @COUNTER = @COUNTER + 1 END SELECT CAST(getdate() AS DATE) AS CAPTURE_DATE , DATEPART(HOUR, getdate()) AS CAPTURE_HOUR , DATEPART(MINUTE, getdate()) AS CAPTURE_MINUTE , GETDATE() AS CAPTURE_DATETIME , CAST(SERVERPROPERTY('SERVERNAME') AS SYSNAME) AS SERVER_INSTANCE , SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS SERVER , SUBSTRING(FILE_PHYSICAL_NAME, 1, CHARINDEX('\', FILE_PHYSICAL_NAME, 4)) AS VOLUME , F.DB_ID , F.DB_NAME , CASE WHEN F.FILE_TYPE_DESC = 'LOG' THEN 'N/A - LOG' ELSE CASE FILEGROUP_NAME(F.FILE_GROUP_ID) WHEN 'PRIMARY' THEN '.PRIMARY' ELSE FILEGROUP_NAME(F.FILE_GROUP_ID) END END AS FILEGROUP_NAME , F.FILE_ID , F.FILE_NAME , F.FILE_TYPE_DESC AS FILE_TYPE , F.FILE_CAPACITY_MB , U.FILE_USED_MB , F.FILE_CAPACITY_MB - U.FILE_USED_MB AS FILE_FREE_MB , CASE WHEN F.FILE_CAPACITY_MB = 0 THEN 0 ELSE CAST(U.FILE_USED_MB/F.FILE_CAPACITY_MB *100 AS DECIMAL(18, 2)) END AS FILE_USED_PCT , CASE WHEN F.FILE_CAPACITY_MB = 0 THEN 0 ELSE CAST((F.FILE_CAPACITY_MB - U.FILE_USED_MB)/F.FILE_CAPACITY_MB *100 AS DECIMAL(18, 2)) END AS FILE_FREE_PCT , CASE WHEN FILE_CAPACITY_MB = 0 THEN 'N/A' WHEN ROUND(CAST(F.FILE_CAPACITY_MB - U.FILE_USED_MB AS FLOAT)/F.FILE_CAPACITY_MB *100 , 2) <10 THEN 'CRITICAL - 10% OR LESS FREE' WHEN ROUND(CAST(F.FILE_CAPACITY_MB - U.FILE_USED_MB AS FLOAT)/F.FILE_CAPACITY_MB *100 , 2) <20 THEN 'WARNING - 20% OR LESS FREE' ELSE 'OKAY' END AS FILE_STATUS , F.FILE_PHYSICAL_NAME , F.FILE_MAX_SIZE , F.FILE_GROWTH , F.FILE_IS_PERCENT_GROWTH INTO #FILE_SIZE FROM ##SPACE_USED U JOIN #DB_FILES F ON F.DB_quoteNAME = U.DB_NAME AND F.FILE_ID = U.FILE_ID select * from #FILE_SIZE end -- IF @SHOW_FILE_SPACE = 1------------------------------------------------------------------------------------------------------------------------------------/* SESSION-LEVEL OUTPUTS*/------------------------------------------------------------------------------------------------------------------------------------IF @INCLUDE_SESSION_INFO = 1begin IF OBJECT_ID(N'TEMPDB..#SESSION') is not null DROP TABLE #SESSION CREATE TABLE #SESSION (SESSION_ID SMALLINT , BLOCKING_SESSION_ID SMALLINT , TRANSACTION_COUNT TINYINT , DB_NAME NVARCHAR(128) , STATUS NVARCHAR(300) , LOGIN_TIME DATETIME , REQ_WAIT_TYPE NVARCHAR(60) , REQ_COMMAND NVARCHAR(160) , SQL_STATEMENT NVARCHAR(MAX) , OBJECT_ID INT , OBJECT_NAME NVARCHAR(128) , PERCENT_COMPLETE REAL , ESTIMATED_COMPLETION_TIME BIGINT , HOST_NAME NVARCHAR(128) , PROGRAM_NAME NVARCHAR(128) , CLIENT_INTERFACE_NAME NVARCHAR(320) , LOGIN_NAME NVARCHAR(128) , SESSION_TOTAL_ELAPSED_TIME INT , LAST_REQUEST_START_TIME DATETIME , LAST_REQUEST_END_TIME DATETIME , REQ_START_TIME DATETIME , REQ_TOTAL_ELAPSED_TIME INT , REQ_WAIT_TIME INT , REQ_LAST_WAIT_TYPE NVARCHAR(600) , REQ_ROW_COUNT BIGINT , TRANSACTION_ISOLATION_LEVEL SMALLINT , CONCAT_NULL_YIELDS_NULL BIT , ARITHABORT BIT , ANSI_PADDING BIT , ANSI_NULLS BIT , DEADLOCK_PRIORITY INT , NEST_LEVEL INT --, QUERY_PLAN XML ) /*PULL INFO FOR ALL TRANSACTIONS*/ INSERT INTO #SESSION SELECT S.SESSION_ID , R.BLOCKING_SESSION_ID , S.open_transaction_count , D.NAME AS DB_NAME , S.STATUS , S.LOGIN_TIME , R.WAIT_TYPE AS REQ_WAIT_TYPE , MAND AS REQ_COMMAND , (SELECT TOP 1 SUBSTRING(S2.TEXT,STATEMENT_START_OFFSET / 2+1 , ( (CASE WHEN STATEMENT_END_OFFSET = -1 THEN (LEN(CONVERT(NVARCHAR(MAX),S2.TEXT)) * 2) ELSE STATEMENT_END_OFFSET END) - STATEMENT_START_OFFSET) / 2+1)) AS SQL_STATEMENT , s2.OBJECTID AS OBJECT_ID , OBJECT_NAME(S2.OBJECTID, S.DATABASE_ID) AS OBJECT_NAME , R.PERCENT_COMPLETE , R.ESTIMATED_COMPLETION_TIME , S.HOST_NAME , S.PROGRAM_NAME , S.CLIENT_INTERFACE_NAME , S.LOGIN_NAME , S.TOTAL_ELAPSED_TIME AS SESSION_TOTAL_ELAPSED_TIME , S.LAST_REQUEST_START_TIME , S.LAST_REQUEST_END_TIME , R.START_TIME AS REQ_START_TIME , R.TOTAL_ELAPSED_TIME AS REQ_TOTAL_ELAPSED_TIME , R.WAIT_TIME AS REQ_WAIT_TIME , R.LAST_WAIT_TYPE AS REQ_LAST_WAIT_TYPE , R.ROW_COUNT AS REQ_ROW_COUNT , R.TRANSACTION_ISOLATION_LEVEL , R.CONCAT_NULL_YIELDS_NULL , R.ARITHABORT , R.ANSI_PADDING , R.ANSI_NULLS , R.DEADLOCK_PRIORITY , R.NEST_LEVEL FROM SYS.DM_EXEC_SESSIONS AS S LEFT JOIN SYS.DM_EXEC_REQUESTS AS R ON R.SESSION_ID = S.SESSION_ID LEFT JOIN SYS.DATABASES AS D ON D.DATABASE_ID = R.DATABASE_ID OUTER APPLY SYS.DM_EXEC_SQL_TEXT (SQL_HANDLE) AS S2 WHERE S.SESSION_ID = @SESSION_ID OR @SESSION_ID IS NULLend -- IF @INCLUDE_SESSION_INFO = 1if @SHOW_ACTIVE_TRANSACTIONS = 1 begin IF OBJECT_ID(N'TEMPDB..#ACTIVE_TEMPDB_TRANSACTIONS') is not null DROP TABLE #ACTIVE_TEMPDB_TRANSACTIONS SELECT * INTO #ACTIVE_TEMPDB_TRANSACTIONS FROM SYS.DM_TRAN_ACTIVE_SNAPSHOT_DATABASE_TRANSACTIONS where @session_id is null or session_id = @session_id IF @INCLUDE_SESSION_INFO = 0 AND @SINGLE_OUTPUT_BY_SESSION_ID = 0 BEGIN SELECT * FROM #ACTIVE_TEMPDB_TRANSACTIONS ORDER BY ELAPSED_TIME_SECONDS DESC END -- IF @INCLUDE_SESSION_INFO = 0 AND @SINGLE_OUTPUT_BY_SESSION_ID = 0 IF @INCLUDE_SESSION_INFO = 1 AND @SINGLE_OUTPUT_BY_SESSION_ID = 0 BEGIN SELECT 'ACTIVE TEMPDB' AS SOURCE , X.HOST_NAME , X.PROGRAM_NAME , T.SESSION_ID , X.TRANSACTION_COUNT , T.MAX_VERSION_CHAIN_TRAVERSED , T.AVERAGE_VERSION_CHAIN_TRAVERSED , T.ELAPSED_TIME_SECONDS , X.STATUS , X.LOGIN_TIME , X.LAST_REQUEST_END_TIME , X.REQ_START_TIME , X.REQ_TOTAL_ELAPSED_TIME , X.BLOCKING_SESSION_ID , X.DB_NAME , X.REQ_WAIT_TYPE , X.REQ_COMMAND , X.SQL_STATEMENT , X.OBJECT_ID , X.OBJECT_NAME , T.TRANSACTION_ID , T.TRANSACTION_SEQUENCE_NUM , MIT_SEQUENCE_NUM , T.IS_SNAPSHOT , T.FIRST_SNAPSHOT_SEQUENCE_NUM , X.PERCENT_COMPLETE , X.ESTIMATED_COMPLETION_TIME , X.CLIENT_INTERFACE_NAME , X.LOGIN_NAME , X.SESSION_TOTAL_ELAPSED_TIME , X.LAST_REQUEST_START_TIME , X.REQ_WAIT_TIME --, X.QUERY_PLAN FROM #ACTIVE_TEMPDB_TRANSACTIONS T LEFT JOIN #SESSION X ON X.SESSION_ID = T.SESSION_ID ORDER BY ELAPSED_TIME_SECONDS DESC END -- IF @INCLUDE_SESSION_INFO = 1 AND @SINGLE_OUTPUT_BY_SESSION_ID = 0 end -- if @SHOW_ACTIVE_TRANSACTIONS = 1------------------------------------------------------------------------------------------------------------------------------------if @SHOW_SESSION_SPACE_USAGE = 1 begin IF OBJECT_ID(N'TEMPDB..#SESSION_SPACE_USAGE') is not null DROP TABLE #SESSION_SPACE_USAGE SELECT SESSION_ID , DATABASE_ID , USER_OBJECTS_ALLOC_PAGE_COUNT , USER_OBJECTS_DEALLOC_PAGE_COUNT , USER_OBJECTS_ALLOC_PAGE_COUNT - USER_OBJECTS_DEALLOC_PAGE_COUNT AS OUTSTANDING_USER_ALLOC_PAGE_COUNT , INTERNAL_OBJECTS_ALLOC_PAGE_COUNT , INTERNAL_OBJECTS_DEALLOC_PAGE_COUNT , INTERNAL_OBJECTS_ALLOC_PAGE_COUNT - INTERNAL_OBJECTS_DEALLOC_PAGE_COUNT AS OUTSTANDING_INTERNAL_ALLOC_PAGE_COUNT , (USER_OBJECTS_ALLOC_PAGE_COUNT - USER_OBJECTS_DEALLOC_PAGE_COUNT ) + (INTERNAL_OBJECTS_ALLOC_PAGE_COUNT - INTERNAL_OBJECTS_DEALLOC_PAGE_COUNT) AS TOTAL_OUTSTANDING_ALLOC_PAGE_COUNT INTO #SESSION_SPACE_USAGE FROM SYS.DM_DB_SESSION_SPACE_USAGE where @session_id is null or session_id = @session_id IF @INCLUDE_SESSION_INFO = 0 AND @SINGLE_OUTPUT_BY_SESSION_ID = 0 BEGIN SELECT * FROM #SESSION_SPACE_USAGE WHERE TOTAL_OUTSTANDING_ALLOC_PAGE_COUNT <> 0 ORDER BY TOTAL_OUTSTANDING_ALLOC_PAGE_COUNT DESC END -- IF @INCLUDE_SESSION_INFO = 0 AND @SINGLE_OUTPUT_BY_SESSION_ID = 0 IF @INCLUDE_SESSION_INFO = 1 AND @SINGLE_OUTPUT_BY_SESSION_ID = 0 BEGIN SELECT 'SESSION SPACE USAGE' AS SOURCE , X.HOST_NAME , X.PROGRAM_NAME , t.SESSION_ID , x.TRANSACTION_COUNT , t.DATABASE_ID , t.TOTAL_OUTSTANDING_ALLOC_PAGE_COUNT , t.USER_OBJECTS_ALLOC_PAGE_COUNT , t.USER_OBJECTS_DEALLOC_PAGE_COUNT , t.OUTSTANDING_USER_ALLOC_PAGE_COUNT , t.INTERNAL_OBJECTS_ALLOC_PAGE_COUNT , t.INTERNAL_OBJECTS_DEALLOC_PAGE_COUNT , t.OUTSTANDING_INTERNAL_ALLOC_PAGE_COUNT , X.BLOCKING_SESSION_ID , X.TRANSACTION_COUNT , X.DB_NAME , X.STATUS , X.LOGIN_TIME , X.REQ_WAIT_TYPE , X.REQ_COMMAND , X.SQL_STATEMENT , X.OBJECT_ID , X.OBJECT_NAME , X.PERCENT_COMPLETE , X.ESTIMATED_COMPLETION_TIME , X.CLIENT_INTERFACE_NAME , X.LOGIN_NAME , X.SESSION_TOTAL_ELAPSED_TIME , X.LAST_REQUEST_START_TIME , X.LAST_REQUEST_END_TIME , X.REQ_START_TIME , X.REQ_TOTAL_ELAPSED_TIME , X.REQ_WAIT_TIME --, X.QUERY_PLAN FROM #SESSION_SPACE_USAGE T LEFT JOIN #SESSION X ON X.SESSION_ID = T.SESSION_ID ORDER BY TOTAL_OUTSTANDING_ALLOC_PAGE_COUNT DESC END -- IF @INCLUDE_SESSION_INFO = 1 AND @SINGLE_OUTPUT_BY_SESSION_ID = 0 END -- if @SHOW_SESSION_SPACE_USAGE = 1----------------------------------------------------------------------------------------IF @SINGLE_OUTPUT_BY_SESSION_ID = 1 AND @INCLUDE_SESSION_INFO = 0 BEGIN SELECT COALESCE(A.SESSION_ID, U.SESSION_ID) AS SESSION_ID , A.TRANSACTION_ID , A.TRANSACTION_SEQUENCE_NUM , MIT_SEQUENCE_NUM , A.IS_SNAPSHOT , A.FIRST_SNAPSHOT_SEQUENCE_NUM , A.MAX_VERSION_CHAIN_TRAVERSED , A.AVERAGE_VERSION_CHAIN_TRAVERSED , A.ELAPSED_TIME_SECONDS , U.USER_OBJECTS_ALLOC_PAGE_COUNT , U.USER_OBJECTS_DEALLOC_PAGE_COUNT , U.OUTSTANDING_USER_ALLOC_PAGE_COUNT , U.INTERNAL_OBJECTS_ALLOC_PAGE_COUNT , U.INTERNAL_OBJECTS_DEALLOC_PAGE_COUNT , U.OUTSTANDING_INTERNAL_ALLOC_PAGE_COUNT , U.TOTAL_OUTSTANDING_ALLOC_PAGE_COUNT FROM #ACTIVE_TEMPDB_TRANSACTIONS A FULL JOIN (SELECT * FROM #SESSION_SPACE_USAGE WHERE TOTAL_OUTSTANDING_ALLOC_PAGE_COUNT <> 0) U ON U.SESSION_ID = A.SESSION_ID ORDER BY ELAPSED_TIME_SECONDS DESC , TOTAL_OUTSTANDING_ALLOC_PAGE_COUNT DESC END --IF @SINGLE_OUTPUT_BY_SESSION_ID = 1 AND @INCLUDE_SESSION_INFO = 0IF @SINGLE_OUTPUT_BY_SESSION_ID = 1 AND @INCLUDE_SESSION_INFO = 1 BEGIN SELECT COALESCE(A.SESSION_ID, U.SESSION_ID) AS SESSION_ID , COALESCE(X.TRANSACTION_COUNT , X2.TRANSACTION_COUNT ) AS OPEN_TRANSACTIONS , COALESCE(X.HOST_NAME , X2.HOST_NAME ) AS HOST_NAME , A.ELAPSED_TIME_SECONDS AS CURRENT_REQ_TIME , COALESCE(X.STATUS , X2.STATUS ) AS STATUS , COALESCE(X.LOGIN_TIME , X2.LOGIN_TIME ) AS LOGIN_TIME , COALESCE(X.LAST_REQUEST_END_TIME , X2.LAST_REQUEST_END_TIME ) AS LAST_REQUEST_END_TIME , COALESCE(X.REQ_START_TIME , X2.REQ_START_TIME ) AS REQ_START_TIME , U.TOTAL_OUTSTANDING_ALLOC_PAGE_COUNT , U.USER_OBJECTS_ALLOC_PAGE_COUNT , U.USER_OBJECTS_DEALLOC_PAGE_COUNT , U.OUTSTANDING_USER_ALLOC_PAGE_COUNT , U.INTERNAL_OBJECTS_ALLOC_PAGE_COUNT , U.INTERNAL_OBJECTS_DEALLOC_PAGE_COUNT , U.OUTSTANDING_INTERNAL_ALLOC_PAGE_COUNT , A.MAX_VERSION_CHAIN_TRAVERSED , A.AVERAGE_VERSION_CHAIN_TRAVERSED , COALESCE(X.PROGRAM_NAME , X2.PROGRAM_NAME ) AS PROGRAM_NAME , COALESCE(X.BLOCKING_SESSION_ID , X2.BLOCKING_SESSION_ID ) AS BLOCKING_SESSION_ID , COALESCE(X.DB_NAME , X2.DB_NAME ) AS DB_NAME , COALESCE(X.REQ_WAIT_TYPE , X2.REQ_WAIT_TYPE ) AS REQ_WAIT_TYPE , COALESCE(X.REQ_COMMAND , X2.REQ_COMMAND ) AS REQ_COMMAND , COALESCE(X.SQL_STATEMENT , X2.SQL_STATEMENT ) AS SQL_STATEMENT , COALESCE(X.OBJECT_ID , X2.OBJECT_ID ) AS OBJECT_ID , COALESCE(X.OBJECT_NAME , X2.OBJECT_NAME ) AS OBJECT_NAME , COALESCE(X.PERCENT_COMPLETE , X2.PERCENT_COMPLETE ) AS PERCENT_COMPLETE , COALESCE(X.ESTIMATED_COMPLETION_TIME , X2.ESTIMATED_COMPLETION_TIME ) AS ESTIMATED_COMPLETION_TIME , COALESCE(X.CLIENT_INTERFACE_NAME , X2.CLIENT_INTERFACE_NAME ) AS CLIENT_INTERFACE_NAME , COALESCE(X.LOGIN_NAME , X2.LOGIN_NAME ) AS LOGIN_NAME , COALESCE(X.SESSION_TOTAL_ELAPSED_TIME , X2.SESSION_TOTAL_ELAPSED_TIME) AS SESSION_TOTAL_ELAPSED_TIME , COALESCE(X.LAST_REQUEST_START_TIME , X2.LAST_REQUEST_START_TIME ) AS LAST_REQUEST_START_TIME , COALESCE(X.REQ_TOTAL_ELAPSED_TIME , X2.REQ_TOTAL_ELAPSED_TIME ) AS REQ_TOTAL_ELAPSED_TIME , COALESCE(X.REQ_WAIT_TIME , X2.REQ_WAIT_TIME ) AS REQ_WAIT_TIME , A.TRANSACTION_ID , A.TRANSACTION_SEQUENCE_NUM , MIT_SEQUENCE_NUM , A.IS_SNAPSHOT , A.FIRST_SNAPSHOT_SEQUENCE_NUM -- , COALESCE(X.QUERY_PLAN , X2.QUERY_PLAN ) AS QUERY_PLAN FROM #ACTIVE_TEMPDB_TRANSACTIONS A FULL JOIN #SESSION_SPACE_USAGE U ON U.SESSION_ID = A.SESSION_ID LEFT JOIN #SESSION X ON X.SESSION_ID = A.SESSION_ID LEFT JOIN #SESSION X2 ON X2.SESSION_ID = U.SESSION_ID ORDER BY ELAPSED_TIME_SECONDS DESC , TOTAL_OUTSTANDING_ALLOC_PAGE_COUNT DESC END --IF @SINGLE_OUTPUT_BY_SESSION_ID = 1 AND @INCLUDE_SESSION_INFO = 1--------------------------------------------------------------------------------------------------------------------------/* OUTPUT - locks on objects in TempDB*/--------------------------------------------------------------------------------------------------------------------------if @show_locks_in_tempdb = 1 begin select 'locks', request_session_id, RESOURCE_DATABASE_ID, resource_type, request_mode, request_status, count(*) AS ROW_COUNT from sys.dm_tran_locks WHERE NOT(RESOURCE_TYPE = 'DATABASE' AND REQUEST_MODE = 'S') and resource_database_id = 2 group by request_session_id, RESOURCE_DATABASE_ID, resource_type, request_mode, request_status order by RESOURCE_DATABASE_ID, request_session_id, resource_type, request_mode, request_status end --if @show_locks_in_tempdb = 1--------------------------------------------------------------------------------------------------------------------------/* OUTPUT - user tables in tempdb*/--------------------------------------------------------------------------------------------------------------------------if @SHOW_USER_TABLES_IN_TEMPDB = 1begin declare @show_system_tables bit = 0 -- NOT CODED IF OBJECT_ID(N'TEMPDB..#jjk_tmptbl_values') is not null DROP TABLE #jjk_tmptbl_values -- Temporary Tables and Their Size SELECT TBL.name AS ObjName , TBL.OBJECT_ID , charindex('___', tbl.name) as start_underscore , tbl.create_date ,STAT.row_count AS Row_Count ,STAT.used_page_count * 8 AS Used_Size_KB ,STAT.reserved_page_count * 8 AS Reverved_Size_KB , STAT.USED_PAGE_COUNT , STAT.RESERVED_PAGE_COUNT , pART.hobt_id , pART.partition_id into #jjk_tmptbl_values FROM tempdb.sys.partitions AS PART INNER JOIN tempdb.sys.dm_db_partition_stats AS STAT ON PART.partition_id = STAT.partition_id AND PART.partition_number = STAT.partition_number INNER JOIN tempdb.sys.tables (NOLOCK) AS TBL ON STAT.object_id = TBL.object_id where TBL.name not like '#[ab][0-9, a-z]%' and len(TBL.name) <> 9 /* get rid of the system-generated temp tables*/ IF @INCLUDE_FILE_BREAKOUT_FOR_USER_TABLES = 0 AND @INCLUDE_CACHED_VS_SPOOLED_PAGES = 0 BEGIN select case when start_underscore = 0 then ObjName else substring(ObjName, 1, start_underscore - 1 ) end as ObjName , OBJECT_ID , create_date , Row_Count , Used_Size_KB , used_page_count , Reverved_Size_KB , reserved_page_count from #jjk_tmptbl_values order by Row_Count desc END --IF @INCLUDE_FILE_BREAKOUT_FOR_USER_TABLES = 0 AND @INCLUDE_CACHED_VS_SPOOLED_PAGES = 0IF @INCLUDE_CACHED_VS_SPOOLED_PAGES = 1 BEGIN IF OBJECT_ID(N'TEMPDB..#allocations') is not null DROP TABLE #allocations select P.object_id , P.ObjName , au.allocation_unit_id , au.data_pages , used_pages , total_pages into #allocations from sys.allocation_units as au inner join #jjk_tmptbl_values as p on au.container_id = p.hobt_id and (au.type = 1 or au.type = 3) union all select P.object_id , P.ObjName , au.allocation_unit_id , au.data_pages , used_pages , total_pages from sys.allocation_units as au inner join #jjk_tmptbl_values as p on au.container_id = P.partition_id and (au.type = 2) IF OBJECT_ID(N'TEMPDB..#buffer') is not null DROP TABLE #buffer select object_id , FILE_ID , count(*)as CACHED_PAGE_COUNT into #buffer from sys.dm_os_buffer_descriptors as bd inner join #allocations obj on bd.allocation_unit_id = obj.allocation_unit_id where database_id = db_id() group by object_id, FILE_ID END --IF @INCLUDE_CACHED_VS_SPOOLED_PAGES = 1 IF @INCLUDE_FILE_BREAKOUT_FOR_USER_TABLES = 1 BEGIN IF OBJECT_ID(N'TEMPDB..#PAGE_ALLOCATIONS') is not null DROP TABLE #PAGE_ALLOCATIONS select * into #PAGE_ALLOCATIONS from sys.dm_db_database_page_allocations(2, null, null, null, 'limited') IF OBJECT_ID(N'TEMPDB..#OBJECT_FILE') is not null DROP TABLE #OBJECT_FILE SELECT OBJECT_ID , EXTENT_FILE_ID AS FILE_ID , COUNT(*) AS PAGE_COUNT INTO #OBJECT_FILE FROM #PAGE_ALLOCATIONS GROUP BY OBJECT_ID , EXTENT_FILE_ID END -- IF @INCLUDE_FILE_BREAKOUT_FOR_USER_TABLES = 1 IF @INCLUDE_FILE_BREAKOUT_FOR_USER_TABLES = 1 AND @INCLUDE_CACHED_VS_SPOOLED_PAGES = 0 BEGIN select TBLS.OBJECT_ID , case when start_underscore = 0 then ObjName else substring(ObjName, 1, start_underscore - 1 ) end as ObjName , create_date , Row_Count , Used_Size_KB , Reverved_Size_KB , used_page_count , reserved_page_count , FILE_ID , PAGE_COUNT AS FILE_PAGE_COUNT from #jjk_tmptbl_values TBLS LEFT join #OBJECT_FILE PAGES on PAGES.object_id = TBLS.object_id where ObjName not like '#[ab][0-9, a-z]%' and len(objname) <> 9 ORDER BY ObjName , OBJECT_ID , FILE_ID END -- IF @INCLUDE_FILE_BREAKOUT_FOR_USER_TABLES = 1 AND @INCLUDE_CACHED_VS_SPOOLED_PAGES = 0 IF @INCLUDE_FILE_BREAKOUT_FOR_USER_TABLES = 1 AND @INCLUDE_CACHED_VS_SPOOLED_PAGES = 1 BEGIN select TBLS.OBJECT_ID , case when start_underscore = 0 then ObjName else substring(ObjName, 1, start_underscore - 1 ) end as ObjName , create_date , Row_Count , Used_Size_KB , Reverved_Size_KB , used_page_count , reserved_page_count , PAGES.FILE_ID , PAGE_COUNT AS FILE_PAGE_COUNT , B.CACHED_PAGE_COUNT , PAGE_COUNT - B.CACHED_PAGE_COUNT AS PAGES_SPOOLED_TO_DISK , CASE WHEN COALESCE(PAGE_COUNT, 0) = 0 THEN 0 ELSE CAST( B.CACHED_PAGE_COUNT*1.0/PAGE_COUNT AS DECIMAL(6, 2)) END AS CACHED_PCT from #jjk_tmptbl_values TBLS LEFT join #OBJECT_FILE PAGES on PAGES.object_id = TBLS.object_id LEFT JOIN #buffer B ON B.object_id = TBLS.OBJECT_ID AND B.file_id = PAGES.FILE_ID ORDER BY ObjName , OBJECT_ID , FILE_ID END IF @INCLUDE_FILE_BREAKOUT_FOR_USER_TABLES = 0 AND @INCLUDE_CACHED_VS_SPOOLED_PAGES = 1 BEGIN IF OBJECT_ID(N'TEMPDB..#BUFFER2') is not null DROP TABLE #BUFFER2 SELECT OBJECT_ID, SUM(CACHED_PAGE_COUNT) AS CACHED_PAGE_COUNT INTO #BUFFER2 FROM #BUFFER GROUP BY OBJECT_ID select TBLS.OBJECT_ID , case when start_underscore = 0 then ObjName else substring(ObjName, 1, start_underscore - 1 ) end as ObjName , create_date , Row_Count , Used_Size_KB , Reverved_Size_KB , used_page_count , reserved_page_count , B.CACHED_PAGE_COUNT , reserved_page_count - B.CACHED_PAGE_COUNT AS PAGES_SPOOLED_TO_DISK , CASE WHEN COALESCE(reserved_page_count, 0) = 0 THEN 0 ELSE CAST( B.CACHED_PAGE_COUNT*1.0/reserved_page_count AS DECIMAL(6, 2)) END AS CACHED_PCT from #jjk_tmptbl_values TBLS LEFT JOIN #buffer2 B ON B.object_id = TBLS.OBJECT_ID ORDER BY ObjName , OBJECT_ID end -- IF @INCLUDE_FILE_BREAKOUT_FOR_USER_TABLES = 1 AND @INCLUDE_CACHED_VS_SPOOLED_PAGES = 1END --if @SHOW_USER_TABLES_IN_TEMPDB = 1 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download