Query stuck for over 15hours

tobz 161 Reputation points
2021-07-13T17:14:41.843+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,914 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,564 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Seeya Xi-MSFT 16,451 Reputation points
    2021-07-14T06:11:42.137+00:00

    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.

    0 comments No comments