View execution status during MSSQL execution

To create a stored procedure: dba_whatsqlisexecuting

Then execute the stored procedure to see the relevant information.

Execution status during MS SQL execution to view information such as SQL that is currently executing

Which SQL is currently executing, and so on, this can help a long time SQL execution make progress bar.

Use [RMA_DWH]

Go

/****** object:storedprocedure [dbo]. [Dba_whatsqlisexecuting] Script date:07/12/2013 10:28:27 ******/

SET ANSI_NULLS on

Go

SET QUOTED_IDENTIFIER ON

Go

CREATE PROC [dbo]. [Dba_whatsqlisexecuting]

As

/*--------------------------------------------------------------------

Purpose:shows What individual SQL statements are currently.

----------------------------------------------------------------------

Parameters:none.

Revision History:

24/07/2008 Ian_Stirk@yahoo.com Initial Version

Example Usage:

1. Exec YourServerName.master.dbo.dba_WhatSQLIsExecuting

---------------------------------------------------------------------*/

BEGIN

--Does not lock anything, and does not get held up by any locks.

SET TRANSACTION Isolation Level READ UNCOMMITTED

--What SQL statements Are currently Running?

SELECT [Spid] = session_id

, ecid

, [Database] = db_name (sp.dbid)

, [User] = Nt_username

, [Status] = Er.status

, [wait] = Wait_type

, [individual Query] = SUBSTRING (Qt.text,

ER.STATEMENT_START_OFFSET/2,

(case when er.statement_end_offset =-1

THEN LEN (CONVERT (NVARCHAR (MAX), Qt.text)) * 2

ELSE Er.statement_end_offset End-

Er.statement_start_offset)/2)

, [Parent Query] = Qt.text

, program = Program_name

, Hostname

, Nt_domain

, start_time

From sys.dm_exec_requests ER

INNER JOIN sys.sysprocesses sp on er.session_id = Sp.spid

CROSS APPLY sys.dm_exec_sql_text (er.sql_handle) as Qt

WHERE session_id >-Ignore system SPIDs.

and session_id not in (@ @SPID)-Ignore This current statement.

Order by 1, 2

End

Go