search

microsoft / tigertoolbox

Toolbox repository for Tiger team
http://aka.ms/sqlserverteam
Other
1.47k stars 739 forks source link

usp_whatsup @qrystats=1 throwing error #140

Open sqlworldwide opened 5 years ago

sqlworldwide commented 5 years ago

SQL Server Version:Microsoft SQL Server 2016 (SP2) (KB4052908) - 13.0.5026.0 (X64) Mar 18 2018 09:11:49 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows Server 2016 Standard 10.0 (Build 14393: )

If I call usp_whatsup with @qrystats=1, I am getting following error: Msg 6841, Level 16, State 1, Line 1 FOR XML could not serialize the data for node 'text()' because it contains a character (0x0000) which is not allowed in XML. To retrieve this data using FOR XML, convert it to binary, varbinary or image data type and use the BINARY BASE64 directive.

pmasl commented 5 years ago

Should be fixed in the latest upload. Thanks for reporting.

mjkirkpatrick commented 5 years ago

still an issue with the latest version

sqlworldwide commented 5 years ago

Sorry I did not check. Are you asking me or letting me know.  Taiob 

On Wednesday, June 19, 2019, 12:12:23 PM EDT, Michael Kirkpatrick <notifications@github.com> wrote:

still an issue with the latest version

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub, or mute the thread.

mjkirkpatrick commented 5 years ago

Letting you know. Thanks.

On Wed, Jun 19, 2019 at 11:57 AM sqlworldwide notifications@github.com wrote:

Sorry I did not check. Are you asking me or letting me know. Taiob

On Wednesday, June 19, 2019, 12:12:23 PM EDT, Michael Kirkpatrick < notifications@github.com> wrote:

still an issue with the latest version

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub, or mute the thread.

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/microsoft/tigertoolbox/issues/140?email_source=notifications&email_token=AHAMICGCPPDPMF2XLHUHN2LP3J6THA5CNFSM4HJF3W22YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGODYC2NWA#issuecomment-503686872, or mute the thread https://github.com/notifications/unsubscribe-auth/AHAMICCD3HIDC5X6FOP6STTP3J6THANCNFSM4HJF3W2Q .

pmasl commented 5 years ago

I'm sorry but I cannot repro... Character 0x0000 is NCHAR(0) and it's escaped in the REPLACE function. It would help if you can dump your DMV into a table and share. Specifically, replacing the following in the sproc:

    (SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
        st.text,
        NCHAR(0),N''?''),NCHAR(1),N''?''),NCHAR(2),N''?''),NCHAR(3),N''?''),NCHAR(4),N''?''),NCHAR(5),N''?''),NCHAR(6),N''?''),NCHAR(7),N''?''),NCHAR(8),N''?''),NCHAR(11),N''?''),NCHAR(12),N''?''),NCHAR(14),N''?''),NCHAR(15),N''?''),NCHAR(16),N''?''),NCHAR(17),N''?''),NCHAR(18),N''?''),NCHAR(19),N''?''),NCHAR(20),N''?''),NCHAR(21),N''?''),NCHAR(22),N''?''),NCHAR(23),N''?''),NCHAR(24),N''?''),NCHAR(25),N''?''),NCHAR(26),N''?''),NCHAR(27),N''?''),NCHAR(28),N''?''),NCHAR(29),N''?''),NCHAR(30),N''?''),NCHAR(31),N''?'') 
        AS [text()]
        FROM sys.dm_exec_sql_text(qs.sql_handle) AS st
        FOR XML PATH(''''), TYPE) AS [sqltext],

With:

    (SELECT st.text FROM sys.dm_exec_sql_text(qs.sql_handle) AS st)  AS [sqltext],
mjkirkpatrick commented 5 years ago

I may have found the issue. It is in the REQUESTS report. All the times that it failed with the above error, the input buffer contained a semicolon. Example: ReportServer.dbo.CleanOrphanedSnapshots;1 . This occurred on both system and user stored procs. I replaced the first SELECT with : SELECT IB.EVENT_INFO FROM sys.dm_exec_input_buffer(er.session_id, er.request_id) AS ib) AS [sqltext]. I hope this helps. Environment: 13.0.5337.0 SP2 CU7

pmasl commented 5 years ago

Thanks for that, but it's not the semicolon. Coming from input buffer or exec_sql_text is not the cause, rather the XML formatting encountering an illegal char. And if running in a version that supports sys.dm_exec_input_buffer, that is already executed (see column name input_buffer after running_batch and running_statement) - but that doesn't necessarily give you the running statement of a batch, so it's not really a replacement. :)

For example input buffer gives you: ReportServer.dbo.CleanOrphanedSnapshots;1 Then running_batch gives you: CREATE PROCEDURE ReportServer.dbo.CleanOrphanedSnapshots @someparam AS ... And running_statement gives you for example a specific statement running inside the sproc at that moment.

So if you can please send me the text to that ReportServer.dbo.CleanOrphanedSnapshots sproc, assuming that's an offending module, I can work with that to see if XML conversion encounters the issue.

mjkirkpatrick commented 5 years ago

Hope this helps: USE [ReportServer] GO /** Object: StoredProcedure [dbo].[CleanOrphanedSnapshots] Script Date: 6/21/2019 8:13:04 AM **/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO

ALTER PROCEDURE [dbo].[CleanOrphanedSnapshots] @Machine nvarchar(512), @PermanentSnapshotCount int, @TemporarySnapshotCount int, @PermanentChunkCount int, @TemporaryChunkCount int, @PermanentMappingCount int, @TemporaryMappingCount int, @PermanentSegmentCount int, @TemporarySegmentCount int, @SnapshotsCleaned int OUTPUT, @ChunksCleaned int OUTPUT, @MappingsCleaned int OUTPUT, @SegmentsCleaned int OUTPUT AS SELECT @SnapshotsCleaned = 0, @ChunksCleaned = 0, @MappingsCleaned = 0, @SegmentsCleaned = 0 ;

-- use readpast rather than NOLOCK.  using
-- nolock could cause us to identify snapshots
-- which have had the refcount decremented but
-- the transaction is uncommitted which is dangerous.
-- the exception to above is when doing a SELECT subclause
-- to determine rows to DELETE as we don't want to
-- delete the uncommitted data.

SET DEADLOCK_PRIORITY LOW

-- cleanup of segmented chunk information happens
-- top->down.  meaning we delete chunk metadata, then
-- mappings, then segment data.  the reason for doing
-- this is because it minimizes the io read cost since
-- each delete step tells us the work that we need to
-- do in the next step.  however, there is the potential
-- for failure at any step which can leave orphaned data
-- structures.  we have another cleanup tasks
-- which will scavenge this orphaned data and clean it up
-- so we don't need to be 100% robust here.  this also
-- means that we can play tricks like using readpast in the
-- dml operations so that concurrent deletes will minimize
-- blocking of each other.
-- also, we optimize this cleanup for the scenario where the chunk is
-- not shared.  this means that if we detect that a chunk is shared
-- we will not delete any of its mappings.  there is potential for this
-- to miss removing a chunk because it is shared and we are concurrently
-- deleting the other snapshot (both see the chunk as shared...).  however
-- we don't deal with that case here, and will instead orphan the chunk
-- mappings and segments.  that is ok, we will just remove them when we
-- scan for orphaned mappings/segments.

declare @cleanedSnapshots table (SnapshotDataId uniqueidentifier primary key) ;
declare @cleanedChunks table (ChunkId uniqueidentifier) ;
declare @cleanedChunks2 table (ChunkId uniqueidentifier primary key) ;
declare @cleanedSegments table (ChunkId uniqueidentifier, SegmentId uniqueidentifier) ;
declare @deleteCount int ;

begin transaction
-- remove the actual snapshot entry
-- we do this transacted with cleaning up chunk
-- data because we do not lazily clean up old ChunkData table.
-- we also do this before cleaning up segmented chunk data to
-- get this SnapshotData record out of the table so another parallel
-- cleanup task does not attempt to delete it which would just cause
-- contention and reduce cleanup throughput.
DELETE TOP (@PermanentSnapshotCount) SnapshotData
output deleted.SnapshotDataID into @cleanedSnapshots (SnapshotDataId)
FROM SnapshotData with(readpast)
WHERE   SnapshotData.PermanentRefCount <= 0 AND
        SnapshotData.TransientRefCount <= 0 ;
SET @SnapshotsCleaned = @@ROWCOUNT;

-- clean up RS2000/RS2005 chunks
set @deleteCount = 20;
while (@deleteCount = 20)
begin
    delete top(20) c
    from ChunkData c with (readpast)
    join @cleanedSnapshots cs ON c.SnapshotDataID = cs.SnapshotDataId;

    set @deleteCount = @@ROWCOUNT;
    SET @ChunksCleaned = @ChunksCleaned + @deleteCount;
end
commit

-- clean up chunks
set @deleteCount = @PermanentChunkCount;
while (@deleteCount = @PermanentChunkCount)
begin
    delete top (@PermanentChunkCount) SC
    output deleted.ChunkId into @cleanedChunks(ChunkId)
    from SegmentedChunk SC with (readpast)
    join @cleanedSnapshots cs on SC.SnapshotDataId = cs.SnapshotDataId ;
    set @deleteCount = @@ROWCOUNT;
    set @ChunksCleaned =  @ChunksCleaned + @deleteCount;
end ;

-- This is added based on the Execution Plan. It should speed
-- up the "clean up unused mapping" operation below.
insert into @cleanedChunks2
select distinct ChunkId from @cleanedChunks;

-- clean up unused mappings
-- using NOLOCK hint in the SELECT subquery to include the dirty uncommitted rows so
-- that those rows are excluded from the DELETE query
set @deleteCount = @PermanentMappingCount;
while (@deleteCount = @PermanentMappingCount)
begin
    delete top(@PermanentMappingCount) CSM
    output deleted.ChunkId, deleted.SegmentId into @cleanedSegments (ChunkId, SegmentId)
    from ChunkSegmentMapping CSM with (readpast)
    join @cleanedChunks2 cc ON CSM.ChunkId = cc.ChunkId
    where not exists (
        select 1 from SegmentedChunk SC with(nolock)
        where SC.ChunkId = cc.ChunkId )
    and not exists (
        select 1 from [ReportServerTempDB].dbo.SegmentedChunk TSC with(nolock)
        where TSC.ChunkId = cc.ChunkId ) ;
    set @deleteCount = @@ROWCOUNT ;
    set @MappingsCleaned = @MappingsCleaned + @deleteCount ;
end ;

-- clean up segments
set @deleteCount = @PermanentSegmentCount;
while (@deleteCount = @PermanentSegmentCount)
begin
    delete top (@PermanentSegmentCount) S
    from Segment S with (readpast)
    join @cleanedSegments cs on S.SegmentId = cs.SegmentId
    where not exists (
        select 1 from ChunkSegmentMapping csm with (nolock)
        where csm.SegmentId = cs.SegmentId ) ;
    set @deleteCount = @@ROWCOUNT ;
    set @SegmentsCleaned = @SegmentsCleaned + @deleteCount ;
end

DELETE FROM @cleanedSnapshots ;
DELETE FROM @cleanedChunks ;
DELETE FROM @cleanedSegments ;

begin transaction
DELETE TOP (@TemporarySnapshotCount) [ReportServerTempDB].dbo.SnapshotData
output deleted.SnapshotDataID into @cleanedSnapshots(SnapshotDataId)
FROM [ReportServerTempDB].dbo.SnapshotData with(readpast)
WHERE   [ReportServerTempDB].dbo.SnapshotData.PermanentRefCount <= 0 AND
        [ReportServerTempDB].dbo.SnapshotData.TransientRefCount <= 0 AND
        [ReportServerTempDB].dbo.SnapshotData.Machine = @Machine ;
SET @SnapshotsCleaned = @SnapshotsCleaned + @@ROWCOUNT ;

DELETE [ReportServerTempDB].dbo.ChunkData FROM [ReportServerTempDB].dbo.ChunkData with (readpast)
INNER JOIN @cleanedSnapshots cs
ON [ReportServerTempDB].dbo.ChunkData.SnapshotDataID = cs.SnapshotDataId
SET @ChunksCleaned = @ChunksCleaned + @@ROWCOUNT
commit

set @deleteCount = 1 ;
while (@deleteCount > 0)
begin
    delete SC
    output deleted.ChunkId into @cleanedChunks(ChunkId)
    from [ReportServerTempDB].dbo.SegmentedChunk SC with (readpast)
    join @cleanedSnapshots cs on SC.SnapshotDataId = cs.SnapshotDataId ;
    set @deleteCount = @@ROWCOUNT ;
    set @ChunksCleaned =  @ChunksCleaned + @deleteCount ;
end ;

-- clean up unused mappings
-- using NOLOCK hint in the SELECT subquery to include the dirty uncommitted rows so
-- that those rows are excluded from the DELETE query
set @deleteCount = 1 ;
while (@deleteCount > 0)
begin
    delete top(@TemporaryMappingCount) CSM
    output deleted.ChunkId, deleted.SegmentId into @cleanedSegments (ChunkId, SegmentId)
    from [ReportServerTempDB].dbo.ChunkSegmentMapping CSM with (readpast)
    join @cleanedChunks cc ON CSM.ChunkId = cc.ChunkId
    where not exists (
        select 1 from [ReportServerTempDB].dbo.SegmentedChunk SC with(nolock)
        where SC.ChunkId = cc.ChunkId ) ;
    set @deleteCount = @@ROWCOUNT ;
    set @MappingsCleaned = @MappingsCleaned + @deleteCount ;
end ;

select distinct ChunkId from @cleanedSegments ;

-- clean up segments
-- using NOLOCK hint in the SELECT subquery to include the dirty uncommitted rows so
-- that those rows are excluded from the DELETE query
set @deleteCount = 1
while (@deleteCount > 0)
begin
    delete top (@TemporarySegmentCount) S
    from [ReportServerTempDB].dbo.Segment S with (readpast)
    join @cleanedSegments cs on S.SegmentId = cs.SegmentId
    where not exists (
        select 1 from [ReportServerTempDB].dbo.ChunkSegmentMapping csm with(nolock)
        where csm.SegmentId = cs.SegmentId ) ;
    set @deleteCount = @@ROWCOUNT ;
    set @SegmentsCleaned = @SegmentsCleaned + @deleteCount ;
end
mjkirkpatrick commented 5 years ago

might be this one: msdb.dbo.sp_readrequest;1

running stmt: WAITFOR(RECEIVE conversation_handle, service_contract_name, message_type_name, message_body FROM ExternalMailQueue INTO @msgs), TIMEOUT @rec_timeout

running batch:

-- sp_readrequest : Reads a request from the the queue and returns its -- contents. CREATE PROCEDURE sp_readrequest @receive_timeout INT -- the max time this read will wait for new message AS BEGIN SET NOCOUNT ON

-- Table to store message information.
DECLARE @msgs TABLE
(
   [conversation_handle] uniqueidentifier,
   [service_contract_name] nvarchar(256),
   [message_type_name] nvarchar(256),
   [message_body] varbinary(max)
)

-- Declare variables to store row values fetched from the cursor
DECLARE 
    @exit                   INT,
    @mailitem_id            INT,
    @profile_id             INT,
    @conversation_handle    uniqueidentifier,
    @service_contract_name  NVARCHAR(256),
    @message_type_name      NVARCHAR(256),
    @xml_message_body       VARCHAR(max),
    @timediff               INT,
    @rec_timeout            INT,
    @start_time             DATETIME,
    @localmessage           NVARCHAR(256),
    @rc                     INT

--Init variables
SELECT @start_time = GETDATE(),
       @timediff = 0,
       @exit = 0,
       @rc = 0

WHILE (@timediff &lt;= @receive_timeout)
BEGIN
    -- Delete all messages from @msgs table
    DELETE FROM @msgs

    -- Pick all message from queue
    SET @rec_timeout = @receive_timeout - @timediff
    WAITFOR(RECEIVE conversation_handle, service_contract_name, message_type_name, message_body 
            FROM ExternalMailQueue INTO @msgs), TIMEOUT @rec_timeout

    -- Check if there was some error in reading from queue
    SET @rc = @@ERROR
    IF (@rc &lt;&gt; 0)
    BEGIN
       IF(@rc &lt; 4) -- make sure return code is not in reserved range (1-3)
           SET @rc = 4

       --Note: we will get error no. 9617 if the service queue 'ExternalMailQueue' is currently disabled.
       BREAK
    END

   --If there is no message in the queue return 1 to indicate a timeout
    IF NOT EXISTS(SELECT * FROM @msgs)
    BEGIN
      SET @rc = 1
      BREAK
    END

    -- Create a cursor to iterate through the messages.
    DECLARE msgs_cursor CURSOR FOR
    SELECT conversation_handle, 
        service_contract_name, 
        message_type_name,
        CONVERT(VARCHAR(MAX), message_body)
    FROM @msgs;

    -- Open the cursor
    OPEN msgs_cursor;

    -- Perform the first fetch and store the values in the variables.
    FETCH NEXT FROM msgs_cursor
    INTO 
        @conversation_handle,
        @service_contract_name,
        @message_type_name,
        @xml_message_body

    -- Check @@FETCH_STATUS to see if there are any more rows to fetch.
    WHILE (@@FETCH_STATUS = 0)
    BEGIN
        -- Check if the message is a send mail message
        IF(@message_type_name = N'{//www.microsoft.com/databasemail/messages}SendMail')
        BEGIN
            DECLARE @xmlblob xml

            SET @xmlblob = CONVERT(xml, @xml_message_body) 

            SELECT  @mailitem_id = MailRequest.Properties.value('(MailItemId)[1]', 'int') 
            FROM @xmlblob.nodes('
            declare namespace requests="http://schemas.microsoft.com/databasemail/requests";
            /requests:SendMail') 
            AS MailRequest(Properties) 

            -- get account information 
            SELECT @profile_id = profile_id
            FROM sysmail_mailitems 
            WHERE mailitem_id = @mailitem_id

            IF(@profile_id IS NULL) -- mail item has been deleted from the database
            BEGIN
               -- log warning
               SET @localmessage = FORMATMESSAGE(14667, @mailitem_id)
               exec msdb.dbo.sysmail_logmailevent_sp @event_type=2, @description=@localmessage

               -- Resource clean-up
               IF(@conversation_handle IS NOT NULL)
                  END CONVERSATION @conversation_handle;

               -- return code has special meaning and will be propagated to the calling process
               SET @rc = 2
            END
            ELSE
            BEGIN
               -- This returns the mail item to the client as multiple result sets
               EXEC sp_MailItemResultSets
                       @mailitem_id            = @mailitem_id,
                       @profile_id             = @profile_id,
                       @conversation_handle    = @conversation_handle,
                       @service_contract_name  = @service_contract_name,
                       @message_type_name      = @message_type_name

               SET @exit = 1 -- make sure we exit outer loop
            END

            -- always break from the loop upon processing SendMail message
            BREAK
        END
        -- Check if the message is a dialog timer. This is used for account retries
        ELSE IF(@message_type_name = N'http://schemas.microsoft.com/SQL/ServiceBroker/DialogTimer')
        BEGIN
            -- Handle the retry case. - DialogTimer is used for send mail reties
            EXEC @rc = sp_process_DialogTimer
                        @conversation_handle    = @conversation_handle,
                        @service_contract_name  = @service_contract_name,
                        @message_type_name      = N'{//www.microsoft.com/databasemail/messages}SendMail'

            -- Always break from the loop upon processing DialogTimer message
            -- In case of failure return code from procedure call will simply be propagated to the calling process
            SET @exit = 1 -- make sure we exit outer loop
            BREAK
        END
        -- Error case
        ELSE IF (@message_type_name = 'http://schemas.microsoft.com/SQL/ServiceBroker/Error')
        -- Error in the conversation, hence ignore all the messages of this conversation.
            BREAK

        -- This is executed as long as fetch succeeds.
        FETCH NEXT FROM msgs_cursor
        INTO 
            @conversation_handle,
            @service_contract_name,
            @message_type_name,
            @xml_message_body
    END

    CLOSE msgs_cursor;
    DEALLOCATE msgs_cursor;

    -- Check if we read any request or only SSB generated messages
    -- If a valid request is read with or without an error break out of loop
    IF (@exit = 1 OR @rc &lt;&gt; 0)
        BREAK

   --Keep track of how long this sp has been running
    select @timediff = DATEDIFF(ms, @start_time, getdate()) 
END

-- propagate return code to the calling process
RETURN @rc

END

maliqiakbar commented 4 years ago

Microsoft SQL Server 2014 (SP2) (KB3171021) - 12.0.5000.0 (X64) Jun 17 2016 19:14:09 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 (Build 9600: )

Facing the same error message when running part below:

;WITH tsu AS (SELECT session_id, SUM(user_objects_alloc_page_count) AS user_objects_alloc_page_count, 
SUM(user_objects_dealloc_page_count) AS user_objects_dealloc_page_count, 
SUM(internal_objects_alloc_page_count) AS internal_objects_alloc_page_count, 
SUM(internal_objects_dealloc_page_count) AS internal_objects_dealloc_page_count FROM sys.dm_db_task_space_usage (NOLOCK) GROUP BY session_id)
SELECT 'Requests' AS [Information], es.session_id, DB_NAME(er.database_id) AS [database_name], OBJECT_NAME(qp.objectid, qp.dbid) AS [object_name],
    (SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
        qt.text,
        NCHAR(0),N'?'),NCHAR(1),N'?'),NCHAR(2),N'?'),NCHAR(3),N'?'),NCHAR(4),N'?'),NCHAR(5),N'?'),NCHAR(6),N'?'),NCHAR(7),N'?'),NCHAR(8),N'?'),NCHAR(11),N'?'),NCHAR(12),N'?'),NCHAR(14),N'?'),NCHAR(15),N'?'),NCHAR(16),N'?'),NCHAR(17),N'?'),NCHAR(18),N'?'),NCHAR(19),N'?'),NCHAR(20),N'?'),NCHAR(21),N'?'),NCHAR(22),N'?'),NCHAR(23),N'?'),NCHAR(24),N'?'),NCHAR(25),N'?'),NCHAR(26),N'?'),NCHAR(27),N'?'),NCHAR(28),N'?'),NCHAR(29),N'?'),NCHAR(30),N'?'),NCHAR(31),N'?') 
        AS [text()]
        FROM sys.dm_exec_sql_text(er.sql_handle) AS qt
        FOR XML PATH(''), TYPE) AS [running_batch],
    (SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
        SUBSTRING(qt2.text,
        1+(CASE WHEN er.statement_start_offset = 0 THEN 0 ELSE er.statement_start_offset/2 END),
        1+(CASE WHEN er.statement_end_offset = -1 THEN DATALENGTH(qt2.text) ELSE er.statement_end_offset/2 END - (CASE WHEN er.statement_start_offset = 0 THEN 0 ELSE er.statement_start_offset/2 END))),
        NCHAR(0),N'?'),NCHAR(1),N'?'),NCHAR(2),N'?'),NCHAR(3),N'?'),NCHAR(4),N'?'),NCHAR(5),N'?'),NCHAR(6),N'?'),NCHAR(7),N'?'),NCHAR(8),N'?'),NCHAR(11),N'?'),NCHAR(12),N'?'),NCHAR(14),N'?'),NCHAR(15),N'?'),NCHAR(16),N'?'),NCHAR(17),N'?'),NCHAR(18),N'?'),NCHAR(19),N'?'),NCHAR(20),N'?'),NCHAR(21),N'?'),NCHAR(22),N'?'),NCHAR(23),N'?'),NCHAR(24),N'?'),NCHAR(25),N'?'),NCHAR(26),N'?'),NCHAR(27),N'?'),NCHAR(28),N'?'),NCHAR(29),N'?'),NCHAR(30),N'?'),NCHAR(31),N'?') 
        AS [text()]
        FROM sys.dm_exec_sql_text(er.sql_handle) AS qt2
        FOR XML PATH(''), TYPE) AS [running_statement],
    er.status,
    er.command,
    qp.query_plan,
    er.percent_complete,
    CONVERT(VARCHAR(20),DATEADD(ms,er.estimated_completion_time,GETDATE()),20) AS [ETA_completion_time],
    (er.cpu_time/1000) AS cpu_time_sec,
    (er.reads*8)/1024 AS physical_reads_KB,
    (er.logical_reads*8)/1024 AS logical_reads_KB,
    (er.writes*8)/1024 AS writes_KB,
    (er.total_elapsed_time/1000)/60 AS elapsed_minutes,
    er.wait_type,
    er.wait_resource,
    er.last_wait_type,
    (SELECT CASE
        WHEN pageid = 1 OR pageid % 8088 = 0 THEN 'Is_PFS_Page'
        WHEN pageid = 2 OR pageid % 511232 = 0 THEN 'Is_GAM_Page'
        WHEN pageid = 3 OR (pageid - 1) % 511232 = 0 THEN 'Is_SGAM_Page'
        WHEN pageid IS NULL THEN NULL
        ELSE 'Is_not_PFS_GAM_SGAM_page' END
    FROM (SELECT CASE WHEN er.[wait_type] LIKE 'PAGE%LATCH%' AND er.[wait_resource] LIKE '%:%'
        THEN CAST(RIGHT(er.[wait_resource], LEN(er.[wait_resource]) - CHARINDEX(':', er.[wait_resource], LEN(er.[wait_resource])-CHARINDEX(':', REVERSE(er.[wait_resource])))) AS int)
        ELSE NULL END AS pageid) AS latch_pageid
    ) AS wait_resource_type,
    er.wait_time AS wait_time_ms,
    er.cpu_time AS cpu_time_ms,
    er.open_transaction_count,
    DATEADD(s, (er.estimated_completion_time/1000), GETDATE()) AS estimated_completion_time,
    CASE WHEN mg.wait_time_ms IS NULL THEN DATEDIFF(ms, mg.request_time, mg.grant_time) ELSE mg.wait_time_ms END AS [grant_wait_time_ms],
    LEFT (CASE COALESCE(er.transaction_isolation_level, es.transaction_isolation_level)
        WHEN 0 THEN '0-Unspecified'
        WHEN 1 THEN '1-ReadUncommitted'
        WHEN 2 THEN '2-ReadCommitted'
        WHEN 3 THEN '3-RepeatableRead'
        WHEN 4 THEN '4-Serializable'
        WHEN 5 THEN '5-Snapshot'
        ELSE CONVERT (VARCHAR(30), er.transaction_isolation_level) + '-UNKNOWN'
    END, 30) AS transaction_isolation_level,
    mg.requested_memory_kb,
    mg.granted_memory_kb,
    mg.ideal_memory_kb,
    mg.query_cost,
    ((((ssu.user_objects_alloc_page_count + tsu.user_objects_alloc_page_count) -
        (ssu.user_objects_dealloc_page_count + tsu.user_objects_dealloc_page_count))*8)/1024) AS user_obj_in_tempdb_MB,
    ((((ssu.internal_objects_alloc_page_count + tsu.internal_objects_alloc_page_count) -
        (ssu.internal_objects_dealloc_page_count + tsu.internal_objects_dealloc_page_count))*8)/1024) AS internal_obj_in_tempdb_MB,
    es.[host_name],
    es.login_name,
    --es.original_login_name,
    es.[program_name],
    --ec.client_net_address,
    es.is_user_process,
    g.name AS workload_group
FROM sys.dm_exec_requests (NOLOCK) er
    LEFT OUTER JOIN sys.dm_exec_query_memory_grants (NOLOCK) mg ON er.session_id = mg.session_id AND er.request_id = mg.request_id
    LEFT OUTER JOIN sys.dm_db_session_space_usage (NOLOCK) ssu ON er.session_id = ssu.session_id
    LEFT OUTER JOIN sys.dm_exec_sessions (NOLOCK) es ON er.session_id = es.session_id
    LEFT OUTER JOIN tsu ON tsu.session_id = ssu.session_id
    LEFT OUTER JOIN sys.dm_resource_governor_workload_groups (NOLOCK) g ON es.group_id = g.group_id
    OUTER APPLY sys.dm_exec_query_plan(er.plan_handle) qp
WHERE er.session_id <> @@SPID AND es.is_user_process = 1
ORDER BY er.total_elapsed_time DESC, er.logical_reads DESC, [database_name], session_id