sql-perf

/*
	Readme
		This tool lets use inspect what is happening in the DB at a given time (including the actual SQL that is running, SPID info, and the actual query plan)
		First, run the SQL int he comment.  This will generate the temp table.  Then run the while loop for as long as you want to inspect the DB	

		Some useful queries

			SELECT TOP 40
			QueryText,
			COUNT(*)
			FROM #DbDiagnositcs
			GROUP BY QueryText
			ORDER BY COUNT(*) DESC;

			Tells you how often a query was a part of a sample.  If a query appears a lot,
			it is either 1 - long running or 2- a short running query that is called a lot
*/

/*
	select 
		GetDate() InsertTime,
		spid,
		(select text from sys.dm_exec_sql_text(er.plan_handle)) queryText,
		waittype, 
		waittime, 
		blocked, 
		lastwaittype, 
		waitresource, 
		open_tran, 
		last_batch, 
		hostname, 
		program_name, 
		nt_domain,
		nt_username, 
		loginame ,
		(select query_plan from sys.dm_exec_query_plan(er.plan_handle)) QueryPlan,
		er.start_time,
		er.status,
		er.cpu_time,
		er.reads,
		er.writes,
		er.logical_reads,
		er.blocking_session_id,
		er.transaction_isolation_level
		INTO #DbDiagnositcs
	from master.dbo.sysprocesses P
	INNER JOIN sys.dm_exec_requests er on er.session_id = p.spid
	where P.spid > 50 AND spid <> @@SPID
	and P.status not in ('background', 'sleeping')
	
	TRUNCATE TABLE #DbDiagnositcs
*/

DECLARE @time DATETIME;

WHILE (1 = 1)
BEGIN
    SET @time = GETDATE();
    INSERT INTO #DbDiagnositcs
    SELECT @time InsertTime,
           spid,
           (
               SELECT text FROM sys.dm_exec_sql_text(er.plan_handle)
           ) queryText,
           waittype,
           waittime,
           blocked,
           lastwaittype,
           waitresource,
           open_tran,
           last_batch,
           hostname,
           program_name,
           nt_domain,
           nt_username,
           loginame,
           (
               SELECT query_plan FROM sys.dm_exec_query_plan(er.plan_handle)
           ) QueryPlan,
           er.start_time,
           er.status,
           er.cpu_time,
           er.reads,
           er.writes,
           er.logical_reads,
           er.blocking_session_id,
           er.transaction_isolation_level
    FROM master.dbo.sysprocesses P
        INNER JOIN sys.dm_exec_requests er
            ON er.session_id = P.spid
    WHERE P.spid > 50
          AND spid <> @@SPID
          AND P.status NOT IN ( 'background', 'sleeping' );
    WAITFOR DELAY '00:00:01';
END;