Query stuck for over 15hours

Question

Hi Guys,

I have this query that I use to get the current LSN in a database snapshot and I noticed it takes forever to run.

SELECT '0x' + REPLACE(LEFT([S3].[Current LSN], LEN([S3].[Current LSN])), ':', '') AS CurrentLSN
FROM
(
    SELECT *,
           ROW_NUMBER() OVER (ORDER BY [S2].[diff] DESC) AS RNK2
    FROM
    (
        SELECT *,
               ABS([S1].[Xact ID] - LAG([S1].[Xact ID]) OVER (ORDER BY RNK)) AS diff
        FROM
        (
            SELECT TOP (100)
                   [fd].[Current LSN],
                   [fd].[Operation],
                   [fd].[Xact ID],
                   [fd].[End Time],
                   [fd].[Transaction Begin],
                   ROW_NUMBER() OVER (ORDER BY [fd].[Transaction Begin] DESC, [fd].[Current LSN] DESC) AS RNK
            FROM sys.[fn_dblog](NULL, NULL) AS [fd]
            WHERE [fd].[Operation] = 'LOP_COMMIT_XACT'
            ORDER BY [RNK]
        ) S1
    ) S2
    WHERE [S2].[diff] IS NOT NULL
) S3
WHERE [S3].[RNK2] = 1;

Please anyone has an idea what could be causing the bottleneck in the query and how I can improve the performance.

Thanks

Answer 1

Hi @tobz ,

Any suggestion on how to get it run faster or alternative method for getting the Current LSN

You can also use the DBCC Log() command to see log information which includes Current LSN, but this command will not give you detail information.
As for the slow data query, it may be caused by the excessive amount of your data. Can you query with conditions as mentioned by Ryan?
The query should be optimized, and full table scans should be avoided as much as possible. First, you should consider establishing indexes on the columns involved in where and order by. This is where you should pay attention to improve the query speed when the data is large.
And you can refer to this blog about fn_dump_dblog.

Best regards,
Seeya

---

If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.