View the execution status during MSSQL execution

Create a stored procedure: dba_WhatSQLIsExecuting

Then execute the stored procedure to view the relevant information.

The execution status during ms SQL Execution to view information such as the currently executing SQL

Which SQL statement is currently executed, and so on, which can help to progress the SQL Execution for a long time.

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 executing.

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

Parameters: None.

Revision History:

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

Example Usage:

1. exec YourServerName. master. dbo. dba_WhatSQLIsExecuting

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

BEGIN

-- Do not lock anything, and do 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> 50 -- Ignore system spids.

AND session_Id not in (@ SPID) -- Ignore this current statement.

Order by 1, 2

END

GO