TEMPDB Usage

To find the usage of tempdb:

select
* from sys.dm_db_session_space_usage spujoin sys.dm_exec_sessions s on s.session_id = spu.session_idjoin sys.dm_exec_requests r on s.session_id = r.session_idcross apply sys.dm_exec_sql_text(sql_handle) t order by internal_objects_alloc_page_count desc-----------------------------------


SELECT SUM(unallocated_extent_page_count) AS [free pages], (SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]FROM sys.dm_db_file_space_usage;
--The following query returns the total number of pages used by the version store and the total space in MB used by the version store in tempdb.SELECT SUM(version_store_reserved_page_count) AS [version store pages used], (SUM(version_store_reserved_page_count)*1.0/128) AS [version store space in MB] FROM sys.dm_db_file_space_usage;
SELECT transaction_id FROM sys.dm_tran_active_snapshot_database_transactions ORDER BY elapsed

Tempdb
session File usage sys.dm_db_session_space_usage : Returns the number of pages allocated and deallocated by each session for the database.sys.dm_exec_sessions: Gives details about the sessions.SELECTsys.dm_exec_sessions.session_id AS [SESSION ID],DB_NAME(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_dealloc_page_count * 8) AS [SPACE Deallocated FOR USER Objects (in KB)],(internal_objects_alloc_page_count * 8) AS [SPACE Allocated FOR Internal Objects (in KB)],(internal_objects_dealloc_page_count * 8) AS [SPACE Deallocated FOR Internal Objects (in KB)],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_usageINNER joinsys.dm_exec_sessionsON sys.dm_db_session_space_usage.session_id = sys.dm_exec_sessions.session_id

GO--A long running transaction may prevent cleanup of transaction log thus eating up all log space available resulting space crisis for all other applications.SELECTtransaction_id AS [Transacton ID],[name] AS [TRANSACTION Name],transaction_begin_time AS [TRANSACTION BEGIN TIME],DATEDIFF(mi, transaction_begin_time, GETDATE()) AS [Elapsed TIME (in MIN)],CASE transaction_type
WHEN 1 THEN 'Read/write'WHEN 2 THEN 'Read-only'WHEN 3 THEN 'System'WHEN 4 THEN 'Distributed'END AS [TRANSACTION Type],CASE transaction_state
WHEN 0 THEN 'The transaction has not been completely initialized yet.'WHEN 1 THEN 'The transaction has been initialized but has not started.'WHEN 2 THEN 'The transaction is active.'WHEN 3 THEN 'The transaction has ended. This is used for read-only transactions.'WHEN 4 THEN 'The commit process has been initiated on the distributed transaction. This is for distributed transactions only. The distributed transaction is still active but further processing cannot take place.'WHEN 5 THEN 'The transaction is in a prepared state and waiting resolution.'WHEN 6 THEN 'The transaction has been committed.'WHEN 7 THEN 'The transaction is being rolled back.'WHEN 8 THEN 'The transaction has been rolled back.'END AS [TRANSACTION Description]FROM sys.dm_tran_active_transactions
GO--Long running Queries--sys.dm_exec_requests : Returns information regarding the requests made to the database server.SELECTHOST_NAME AS [System Name],program_name AS [Application Name],DB_NAME(database_id) AS [DATABASE Name],USER_NAME(USER_ID) AS [USER Name],connection_id AS [CONNECTION ID],sys.dm_exec_requests.session_id AS [CURRENT SESSION ID],blocking_session_id AS [Blocking SESSION ID],start_time AS [Request START TIME],sys.dm_exec_requests.status AS [Status],command AS [Command Type],(SELECT TEXT FROM sys.dm_exec_sql_text(sql_handle)) AS [Query TEXT],wait_type AS [Waiting Type],wait_time AS [Waiting Duration],wait_resource AS [Waiting FOR Resource],sys.dm_exec_requests.transaction_id AS [TRANSACTION ID],percent_complete AS [PERCENT Completed],estimated_completion_time AS [Estimated COMPLETION TIME (in mili sec)],sys.dm_exec_requests.cpu_time AS [CPU TIME used (in mili sec)],(memory_usage * 8) AS [Memory USAGE (in KB)],sys.dm_exec_requests.total_elapsed_time AS [Elapsed TIME (in mili sec)]FROM sys.dm_exec_requestsINNER joinsys.dm_exec_sessionsON sys.dm_exec_requests.session_id = sys.dm_exec_sessions.session_idWHERE DB_NAME(database_id) = 'tempdb'GO
select * from sys.dm_db_session_space_usage spujoin sys.dm_exec_sessions s on s.session_id = spu.session_idjoin sys.dm_exec_requests r on s.session_id = r.session_idcross apply sys.dm_exec_sql_text(sql_handle) t order by internal_objects_alloc_page_count desc--------------------------------------------------------------------------------

Blocking Analysis

USE [master] GO /****** Object: Table [dbo].[MG_BlockingAnalysis] Script Date: 07/17/2013 11:19:14 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[MG_BlockingAnalysis]( [WaitMSQty] [int] NOT NULL, [CallingSpId] [smallint] NOT NULL, [CallingUserName] [nvarchar](30) NULL, [CallingProgramName] [nvarchar](40) NULL, [BlockingSpId] [smallint] NULL, [DbName] [nvarchar](128) NULL, [CallingSQL] [varchar](max) NULL, [CallingResourceId] [bigint] NULL, [CallingResourceType] [nvarchar](30) NULL, [CallingRequestMode] [nvarchar](30) NULL, [BlockingSQL] [varchar](max) NULL, [BlockingResourceType] [nvarchar](30) NULL, [BlockingRequestMode] [nvarchar](30) NULL, [BlockingResourceId] [bigint] NULL, [BlockingObjectName] [nvarchar](255) NULL, [BlockingCursorSQL] [nvarchar](max) NULL, [dateadded] [datetime] NULL ) ON [PRIMARY] GO USE [master] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO create function [dbo].[fn_GetResourceName] (@res_id bigint, @db_id int) returns varchar(255) as begin declare @db_name nvarchar(255)= DB_NAME(@db_id) declare @objname nvarchar(255), @out nvarchar(255) declare @dyn_sql nvarchar(500) = N'SELECT @out = object_name(object_id) FROM ' + @db_name + '.sys.partitions ps1 WHERE ps1.hobt_id = @res_id' exec sp_executesql @dyn_sql, N'@res_id nvarchar(100),@out nvarchar(100) output', @res_id, @out OUTPUT return @out end GO SET ANSI_PADDING OFF GO while 1 = 1 begin insert into master..MG_BlockingAnalysis SELECT er.wait_time AS WaitMSQty , er.session_id AS CallingSpId , LEFT(nt_user_name, 30) AS CallingUserName , LEFT(ces.program_name, 40) AS CallingProgramName , er.blocking_session_id AS BlockingSpId , DB_NAME(er.database_id) AS DbName , CAST(csql.text AS varchar(max)) AS CallingSQL , clck.CallingResourceId , clck.CallingResourceType , clck.CallingRequestMode , CAST(bsql.text AS varchar(max)) AS BlockingSQL , blck.BlockingResourceType , blck.BlockingRequestMode , blck.BlockingResourceId , blck.BlockingObjectName , bng_txt.text as BlockingCursorSQL , GETDATE() as dateadded --into master..MG_BlockingAnalysis FROM master.sys.dm_exec_requests er WITH (NOLOCK) JOIN master.sys.dm_exec_sessions ces WITH (NOLOCK) ON er.session_id = ces.session_id CROSS APPLY fn_get_sql (er.sql_handle) csql JOIN ( -- Retrieve lock information for calling process, return only one record to -- report information at the session level SELECT cl.request_session_id AS CallingSpId , MIN(cl.resource_associated_entity_id) AS CallingResourceId , MIN(LEFT(cl.resource_type, 30)) AS CallingResourceType , MIN(LEFT(cl.request_mode, 30)) AS CallingRequestMode -- (i.e. schema, update, etc.) FROM master.sys.dm_tran_locks cl WITH (nolock) WHERE cl.request_status = 'WAIT' -- Status of the lock request = waiting GROUP BY cl.request_session_id ) AS clck ON er.session_id = clck.CallingSpid JOIN ( -- Retrieve lock information for blocking process -- Only one record will be returned (one possibility, for instance, -- is for multiple row locks to occur) SELECT bl.request_session_id AS BlockingSpId , bl.resource_associated_entity_id AS BlockingResourceId ,CASE WHEN bl.resource_type = 'OBJECT' THEN OBJECT_NAME(bl.resource_associated_entity_id) WHEN bl.resource_type IN ('KEY', 'PAGE', 'RID') THEN dbo.fn_GetResourceName(bl.resource_associated_entity_id, DB_ID()) ELSE 'n.a.' END AS BlockingObjectName , MIN(LEFT(bl.resource_type, 30)) AS BlockingResourceType , MIN(LEFT(bl.request_mode, 30)) AS BlockingRequestMode FROM master.sys.dm_tran_locks bl WITH (nolock) GROUP BY bl.request_session_id , bl.resource_associated_entity_id ,CASE WHEN bl.resource_type = 'OBJECT' THEN OBJECT_NAME(bl.resource_associated_entity_id) WHEN bl.resource_type IN ('KEY', 'PAGE', 'RID') THEN dbo.fn_GetResourceName(bl.resource_associated_entity_id, DB_ID()) ELSE 'n.a.' END ) AS blck ON er.blocking_session_id = blck.BlockingSpId AND clck.CallingResourceId = blck.BlockingResourceId JOIN master.sys.dm_exec_connections ber WITH (NOLOCK) ON er.blocking_session_id = ber.session_id CROSS APPLY fn_get_sql (ber.most_recent_sql_handle) bsql --Get the sql code if it is 'hidden' by a JDBC \ Cursor FETCH API call outer apply sys.dm_exec_cursors (er.blocking_session_id) bng outer apply sys.dm_exec_sql_text (bng.sql_handle) bng_txt WHERE ces.is_user_process = 1 AND er.wait_time > 0 waitfor delay '00:00:03' end

UNUSED INDEX

SELECT TOP 25 o.name AS ObjectName , i.name AS IndexName , i.index_id AS IndexID , dm_ius.user_seeks AS UserSeek , dm_ius.user_scans AS UserScans , dm_ius.user_lookups AS UserLookups , dm_ius.user_updates AS UserUpdates , p.TableRows , 'DROP INDEX ' + QUOTENAME(i.name) + ' ON ' + QUOTENAME(s.name) + '.' + QUOTENAME(OBJECT_NAME(dm_ius.OBJECT_ID)) AS 'drop statement' FROM sys.dm_db_index_usage_stats dm_ius INNER JOIN sys.indexes i ON i.index_id = dm_ius.index_id AND dm_ius.OBJECT_ID = i.OBJECT_ID INNER JOIN sys.objects o ON dm_ius.OBJECT_ID = o.OBJECT_ID INNER JOIN sys.schemas s ON o.schema_id = s.schema_id INNER JOIN (SELECT SUM(p.rows) TableRows, p.index_id, p.OBJECT_ID FROM sys.partitions p GROUP BY p.index_id, p.OBJECT_ID) p ON p.index_id = dm_ius.index_id AND dm_ius.OBJECT_ID = p.OBJECT_ID WHERE OBJECTPROPERTY(dm_ius.OBJECT_ID,'IsUserTable') = 1 AND dm_ius.database_id = DB_ID() AND i.type_desc = 'nonclustered' AND i.is_primary_key = 0 AND i.is_unique_constraint = 0 AND dm_ius.[user_seeks] +dm_ius.[user_scans] + dm_ius.[user_lookups] = 0 ORDER BY (dm_ius.user_seeks + dm_ius.user_scans + dm_ius.user_lookups) ASC