程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> 關於MYSQL數據庫 >> 查看MSSQL 執行過程中執行狀態

查看MSSQL 執行過程中執行狀態

編輯:關於MYSQL數據庫

       創建一個存儲過程:dba_WhatSQLIsExecuting

      然後執行這個存儲過程就可以查看相關的信息了。

      MS SQL 執行過程中執行狀態,可查看當前正在執行的sql等信息

      當前執行到哪句SQL,等,這個可以幫助長時間的SQL執行做進度條。

      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 [email protected] 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

    1. 上一頁:
    2. 下一頁:
    Copyright © 程式師世界 All Rights Reserved