A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
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.
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
Thanks Viorel, unfortunately its still stiuck
Did you check if the inner SELECT TOP(100)..., executed separately, has the same issue? Then check the SELECT *... part.
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
If even SELECT TOP (100) * FROM sys.[fn_dblog] (NULL, NULL) is slow, then it seems to be a problem of fn_dblog.
Any suggestion on how to get it run faster or alternative method for getting the Current LSN
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
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.
Sign in to comment
Sort by: Most helpful
Hi @tobz ,
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.
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.
Sign in to comment