最新回復
  • 5月前
    1 #

    您可以使用此查詢来获取整个服務器上正在執行的查詢的列表,包括它们的程序ID,執行了多长時間,執行在哪个資料庫中以及執行它们的人 ,等待統計資訊,阻止查詢ID等。我建議將其放入儲存過程中,以便您可以根据需要轻松呼叫它(即使使用預設引數),也可以使用SQL Agent作業定期安排它来記錄日志.結果到表中。

    DECLARE @Database VARCHAR(100) = NULL 
    DECLARE @EntityName VARCHAR(100) = NULL
    DECLARE @ExcludeMyQueries BIT = 0
    DECLARE @RunningThreadsOnly BIT = 0
    DECLARE @LocksOnly BIT = 0
    DECLARE @MinDuration INT = 0
    DECLARE @OutputMode INT = 0 -- TODO: Setup formally documented modes, and clean up usage below, can even have different kinds of modes like Verbose, vs EmergenciesOnly, etc
    DECLARE @ExecutionDate DATETIME = GETDATE();
    SELECT 
        SP.HostName,
        SP.SPID,    
        ER.request_id AS RequestId,
        ER.percent_complete AS PercentComplete, 
        DATEDIFF(s, start_time, @ExecutionDate) AS Duration,
        CAST(((DATEDIFF(s, start_time, @ExecutionDate)) / 3600) AS VARCHAR) + ' hour(s), '
            + CAST((DATEDIFF(s, start_time, @ExecutionDate) % 3600) / 60 AS VARCHAR) + 'min, ' 
            + CAST((DATEDIFF(s, start_time, @ExecutionDate) % 60) AS VARCHAR) + ' sec' AS RunningTime, 
        CAST((estimated_completion_time / 3600000) AS VARCHAR) + ' hour(s), ' 
            + CAST((estimated_completion_time % 3600000) / 60000 AS VARCHAR) + 'min, ' 
            + CAST((estimated_completion_time % 60000) / 1000 AS VARCHAR) + ' sec' AS EstimatedTimeRemaining, 
        DATEADD(SECOND, estimated_completion_time/1000, @ExecutionDate) AS EstimatedCompletionDate, 
        ER.Command,
        ER.blocking_session_id AS BlockingSessionId, 
        LastWaitType,  
        SP.[DBID],  
        DB_NAME(SP.[DBID]) AS DbName,       
        --[TEXT] AS EntityText,
        CPU,
        ER.plan_handle AS PlanHandle,
        ER.query_plan_hash AS QueryPlanHash,
        LOGIN_TIME AS LoginTime,
        LOGINAME AS LoginName, 
        SP.[Status],
        [PROGRAM_NAME] AS ProgramName,
        NT_DOMAIN AS NT_Domain, 
        NT_USERNAME AS NT_Username, 
        @@SERVERNAME AS ServerName,
        @ExecutionDate AS ExecutionDate 
    INTO #ExecutingQueries
    FROM sys.sysprocesses SP  
    INNER JOIN sys.dm_exec_requests ER 
        ON sp.spid = ER.session_id 
    WHERE --TEXT NOT LIKE N'%spGetRunningQueries%'
        --AND 
        DB_NAME(SP.dbid) NOT IN ('msdb','master','Distribution')    
        AND
        (
            @Database IS NULL
            OR (@Database IS NOT NULL AND @Database = DB_NAME(SP.[DBID]))
        )
        AND
        (
            @ExcludeMyQueries = 0
            OR (@ExcludeMyQueries = 1 AND hostname <> HOST_NAME())
        )   
        AND
        (
            @RunningThreadsOnly = 0
            OR (@RunningThreadsOnly = 1 AND SP.[Status] = 'RUNNABLE')
        )
        AND 
        (
            @LocksOnly = 0
            OR (@LocksOnly = 1 AND ER.blocking_session_id <> 0) -- TODO: Show the source running query that IS the blocking session ID (will need to join / union this in somehow?)
        )
        AND DATEDIFF(s, start_time, @ExecutionDate) >= @MinDuration
    -- TODO: Clean this up and DON'T USE SELECT *
    IF (@OutputMode = 0) -- Everything mode
    BEGIN
        SELECT *
        FROM #ExecutingQueries
    END
    ELSE IF (@OutputMode = 1) -- Lightweight mode
    BEGIN
        SELECT 
            HostName,
            SPID,
            RequestId,
            RunningTime,
            BlockingSessionId,
            LastWaitType,
            DbName,
            LEFT(EntityText, 100) AS EntityText,
            PlanHandle,
            [Status]
        FROM #ExecutingQueries
    END
    

    使用它,您可以查看執行時間最长的查詢( DurationRunningTime 专欄),並通過查看 LastWaitType来了解為什麼它们執行了這麼长時間 和/或 BlockingSessionId 列。

    這是我根据過去發現的一些查詢建立的一項正在进行的工作(不記得確切的位置,但是如果有人能够識別並知道来源,我很乐意 引用),我经常用它来除錯SQL Server上的問题。

  • 5月前
    2 #

    您可能在tempdb中有一个活動事務.您可以看到有效的交易計數。

    select @@TRANCOUNT
    

    您還可以檢查tempdb的內容以找到它。

    SELECT * FROM tempdb..sysobjects
    

    我將要做的是,在遇到此錯誤時檢查正在執行的查詢/作業,以便準確地發現問题所在。

  • 有關更新統計資訊和索引維護作業的問题
  • sql server:應该將"教师"和"學生"放在一个共同的"人"表中吗?