PerformanceCollector.BlockAndWaitStats.sql

--blow the table away and recreate it. 
IF NOT EXISTS (select * from sys.objects where object_id = object_id('PerformanceCollector.BlockAndWaitStats'))
BEGIN
CREATE TABLE PerformanceCollector.BlockAndWaitStats 
	(ObjId			bigint identity(1,1) not null			--key
	,CurrTime		datetime		NOT NULL				--time of the block/wait.
	,spid			smallint		NOT NULL				--SPID		
	,BlockingSpid	smallint		NOT NULL				--SPID of the session that is blocking the request.  0 is not blocked.  -2 is orphaned
																--dist transaction, -4 is undetermined due to state transitions 
	,IsHeadBlocker	bit				NOT NULL				--finds any blocking tasks that are not themselves blocked.  
	,DBName			varchar(50)		NULL					--database against which the request arrived		
	,waittime		bigint			NULL				--total wait time for this wait type, in ms, inclusive of signal_wait_time. 
	,wait_type		varchar(60)	NULL					--name of the wait type, ie LCK_M_X, WRITELOG, NETWORKIO,etc
	,wait_resource	varchar(512)	NULL					--if the request is blocked then what is it waiting on, ie db:file:objectid
	,status			varchar(30)	NOT NULL				--status of the request (running, runnable, sleeping, suspended, etc)
	,CommandType	varchar(32)		NOT NULL				--type of command (DBCC, SELECT, INSERT, BACKUP LOG, etc)
	,cpu_ms			int				NULL					--CPU used (in ms) at the request level
	,physical_io		bigint			NULL
	,SQLText		varchar(MAX)	NULL					--text of the request
	,program_name	varchar(256)	NOT NULL				--from what application
	,hostname		varchar(256)	NULL					--who made the call
	,login_name		varchar(128)	NOT NULL				--SQL Server login name under which the session is currently executing. 
	,last_batch		datetime		NOT NULL				--Timestamp when the request arrived.
	,open_tran		smallint		NOT NULL				--number of open transactions for the request
	,tran_iso_level	smallint		NULL					--transaction isolation level of the request
	,statement_start_offset  int		NULL					--used to calculate StatementText in the API view
	,statement_end_offset int		NULL
	,TransactionName nvarchar(256) NULL
)
END;
GO
IF NOT EXISTS (SELECT 1 from SYS.INDEXES where name='BlockAndWaitStats_CL' and object_id = object_id('PerformanceCollector.BlockAndWaitStats'))
   CREATE Clustered INDEX BlockAndWaitStats_CL ON PerformanceCollector.BlockAndWaitStats(CurrTime)
GO