Query stuck for over 15hours

Question

Query stuck for over 15hours

tobz 161

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

Viorel 89,136

Check if the next query works:

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

tobz 161 Reputation points

2021-07-13T19:23:25.51+00:00

Thanks Viorel, unfortunately its still stiuck
Viorel 89,136 Reputation points

2021-07-13T19:35:48.857+00:00

Did you check if the inner SELECT TOP(100)..., executed separately, has the same issue? Then check the SELECT *... part.
tobz 161 Reputation points

2021-07-13T19:39:03.417+00:00

Hi Viorel,

So I can see the SELECT TOP (100) is where the issue is coming from. This seems to be running fast for a very small database but takes forever for a large database
Viorel 89,136 Reputation points

2021-07-13T19:43:51.207+00:00

If even SELECT TOP (100) * FROM sys.[fn_dblog] (NULL, NULL) is slow, then it seems to be a problem of fn_dblog.
tobz 161 Reputation points

2021-07-13T19:50:29.573+00:00

Any suggestion on how to get it run faster or alternative method for getting the Current LSN
Ryan Abbey 1,106 Reputation points

2021-07-13T20:54:00.37+00:00

Can you try filter on [Transaction Begin]... e.g. [Transaction Begin] > '2021-07-10' ... will give an idea it is able to reduce what it needs to scan through. If that proves to be quicker, then picking on a time that you can be certain there will be more recent transactions can help reduce your time - e.g. current date - 1 month

How frequently do you need to run this? From reading on fn_dblog, the first parameter is "first LSN" so if you can track what you last received, you can use that as a starting point for subsequent transaction
Seeya Xi-MSFT 15,196 Reputation points Microsoft Vendor

2021-07-16T07:22:34.843+00:00

Hi @tobz ,

We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

Viorel 89,136 Reputation points

2021-07-13T18:17:57.977+00:00

Check if the next query works:

SELECT TOP(1) '0x' + REPLACE([Current LSN], ':', '') AS CurrentLSN FROM ( SELECT *, ABS([S1].[Xact ID] - LAG([S1].[Xact ID]) OVER (ORDER BY [Transaction Begin] DESC, [Current LSN] DESC)) AS diff FROM ( SELECT TOP (100) [fd].[Current LSN], [fd].[Operation], [fd].[Xact ID], [fd].[End Time], [fd].[Transaction Begin] FROM sys.[fn_dblog](NULL, NULL) AS [fd] WHERE [fd].[Operation] = N'LOP_COMMIT_XACT' ORDER BY [fd].[Transaction Begin] DESC, [fd].[Current LSN] DESC ) S1 ) S2 WHERE [S2].[diff] IS NOT NULL order by [diff] desc
tobz 161 Reputation points

2021-07-13T19:23:25.51+00:00

Thanks Viorel, unfortunately its still stiuck
Viorel 89,136 Reputation points

2021-07-13T19:35:48.857+00:00

Did you check if the inner SELECT TOP(100)..., executed separately, has the same issue? Then check the SELECT *... part.
tobz 161 Reputation points

2021-07-13T19:39:03.417+00:00

Hi Viorel,

So I can see the SELECT TOP (100) is where the issue is coming from. This seems to be running fast for a very small database but takes forever for a large database
Viorel 89,136 Reputation points

2021-07-13T19:43:51.207+00:00

If even SELECT TOP (100) * FROM sys.[fn_dblog] (NULL, NULL) is slow, then it seems to be a problem of fn_dblog.
tobz 161 Reputation points

2021-07-13T19:50:29.573+00:00

Any suggestion on how to get it run faster or alternative method for getting the Current LSN
Ryan Abbey 1,106 Reputation points

2021-07-13T20:54:00.37+00:00

Can you try filter on [Transaction Begin]... e.g. [Transaction Begin] > '2021-07-10' ... will give an idea it is able to reduce what it needs to scan through. If that proves to be quicker, then picking on a time that you can be certain there will be more recent transactions can help reduce your time - e.g. current date - 1 month

How frequently do you need to run this? From reading on fn_dblog, the first parameter is "first LSN" so if you can track what you last received, you can use that as a starting point for subsequent transaction
Seeya Xi-MSFT 15,196 Reputation points Microsoft Vendor

2021-07-16T07:22:34.843+00:00

Hi @tobz ,

We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

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.

Query stuck for over 15hours

1 answer

Activity