Folder Blocking

Last updated 11 November 2020.

Blocking - Performance troubleshooting and profiling for Dynamics NAV | BC and SQL Server

Checks SQL Server in scope for some of most common skewed Best Practices and performance issues.
Valid from SQL Server 2012 onwards. By default all databases in the SQL Server instance are eligible for the several database specific checks, and you may use the optional parameter to narrow these checks to specific databases.

IMPORTANT pre-requisites:

Only a system administrator/local host administrator is able to perform all checks or functions.

DISCLAIMER:
This code and information are provided "AS IS" without warranty of any kind, either expressed or implied. Furthermore, the author or Olav Treffurt IT Consulting shall not be liable for any damages you may sustain by using this information, whether direct, indirect, special, incidental or consequential, even if it has been advised of the possibility of such damages.

DESCRIPTION:
Performance tuning is the process of analyzing query plans, execution plans, indexes, partitioning, etc. and finding ways to reduce the time a query takes or reduce resource usage by that query. There are 4 events that I found can help you to identify queries that can be performance tuned. They are deadlocks, application aborts, blocking, and long running queries.

Contains the following informational sections:

General

SQL Query

Description

Version

Blocking XEvents

In the view, you can see detailed information about the records captured by the blocked process session. This view allows you to find the location in the AL source code using the SQL Server data.

For more information, see the Blocking Processes Overview - V8 Search XE - Product Documentation

2.0

Blocking Application

In the view you get information about how often an application blocked another application. This is NAV/BC interesting, because it is easy to see if third party products are interfering with the day-to-day operations in Dynamics.

2.0

Blocking Waitresource

When you’re interpreting the Blocked Process Report – it is important to understand what resources are blocked.

Blocked Waitresource > 30
This view shows all waiting resources that have occurred more than 30 times in the filtered period. From this you can very often recognize that this is a systematic error from the code. In the rarest cases, the event is caused by a manual action of a user.

Reading through the Report details , the waitresource indicates the resource needed by the task. Let’s break down the details to understand the resource.

Decoding Key and Page WaitResource for Deadlocks and Blocking

Here’s a reference on how to decode them.

All of this information is out there on the internet already in various places, it’s just spread out! I’m going to pull the whole thing together, from DBCC PAGE to hobt_id to the undocumented %%physloc%% and %%lockres%% functions.

First we’ll talk through PAGE lock waits, then we’ll hit the KEY lock waits.

1) waitresource=“PAGE: 6:3:70133 ” = Database_Id : FileId : PageNumber

If your query was waiting on a page level lock, SQL Server gives you the page address.

Breaking “PAGE: 6:3:70133” down, we’ve got:

database_id=6
data_file_id = 3
page_number = 70133

1.1) Decode the database_id

Find the database name with this query:

SELECT 
                                                name 
                                            FROM sys.databases 
                                            WHERE database_id=6;
                                            GO

SELECT
name
FROM sys.databases
WHERE database_id=6;
GO

SELECT 
                                                name 
                                            FROM sys.databases 
                                            WHERE database_id=6;
                                            GO

That’s the WideWorldImporters sample database on my SQL Server instance.

1.2) Look up the data file name — if you’re interested

We’re going to use the data file id in the next step to find the name of the table. You can just move on. But if you’re curious about the name of the data file, you can look it up by using the database and plugging the data file id into this query:

USE WideWorldImporters;
                                            GO
                                            SELECT 
                                                name, 
                                                physical_name
                                            FROM sys.database_files
                                            WHERE file_id = 3;
                                            GO

USE WideWorldImporters;
GO
SELECT
name,
physical_name
FROM sys.database_files
WHERE file_id = 3;
GO

USE WideWorldImporters;
                                            GO
                                            SELECT 
                                                name, 
                                                physical_name
                                            FROM sys.database_files
                                            WHERE file_id = 3;
                                            GO

1.3) Get the name of the object from DBCC PAGE

We know this is page # 70133 in data file 3 in the WideWorldImporters database. We can look at that page with the undocumented DBCC PAGE and Trace Flag 3604.

Note: I prefer running DBCC page against a restored backup elsewhere, because it’s not supported. In some cases, running DBCC PAGE can cause stack dumps.

/* This trace flag makes DBCC PAGE output go to our Messages tab
                                            instead of the SQL Server Error Log file */
                                            DBCC TRACEON (3604);
                                            GO
                                            /* DBCC PAGE (DatabaseName, FileNumber, PageNumber, DumpStyle)*/
                                            DBCC PAGE ('WideWorldImporters',3,70133,2);
                                            GO

/* This trace flag makes DBCC PAGE output go to our Messages tab
instead of the SQL Server Error Log file */
DBCC TRACEON (3604);
GO
/* DBCC PAGE (DatabaseName, FileNumber, PageNumber, DumpStyle)*/
DBCC PAGE ('WideWorldImporters',3,70133,2);
GO

/* This trace flag makes DBCC PAGE output go to our Messages tab
                                            instead of the SQL Server Error Log file */
                                            DBCC TRACEON (3604);
                                            GO
                                            /* DBCC PAGE (DatabaseName, FileNumber, PageNumber, DumpStyle)*/
                                            DBCC PAGE ('WideWorldImporters',3,70133,2);
                                            GO

Scrolling down in the output, I can find the object_id and IndexId:

Whew, almost there!

I can now find the table and index name with this query:

USE WideWorldImporters;
                                            GO
                                            SELECT 
                                                sc.name as schema_name, 
                                                so.name as object_name, 
                                                si.name as index_name
                                            FROM sys.objects as so 
                                            JOIN sys.indexes as si on 
                                                so.object_id=si.object_id
                                            JOIN sys.schemas AS sc on 
                                                so.schema_id=sc.schema_id
                                            WHERE 
                                                so.object_id = 94623380
                                                and si.index_id = 1;
                                            GO

USE WideWorldImporters;
GO
SELECT
sc.name as schema_name,
so.name as object_name,
si.name as index_name
FROM sys.objects as so
JOIN sys.indexes as si on
so.object_id=si.object_id
JOIN sys.schemas AS sc on
so.schema_id=sc.schema_id
WHERE
so.object_id = 94623380
and si.index_id = 1;
GO

USE WideWorldImporters;
                                            GO
                                            SELECT 
                                                sc.name as schema_name, 
                                                so.name as object_name, 
                                                si.name as index_name
                                            FROM sys.objects as so 
                                            JOIN sys.indexes as si on 
                                                so.object_id=si.object_id
                                            JOIN sys.schemas AS sc on 
                                                so.schema_id=sc.schema_id
                                            WHERE 
                                                so.object_id = 94623380
                                                and si.index_id = 1;
                                            GO

And behold, this lock wait was on the PK_Sales_OrderLines index on the Sales.OrderLines table.

Note: In SQL Server 2014 and higher, you could also find the object name using the undocumented sys.dm_db_database_page_allocations dynamic management object. But you have to query all the pages in the database, which seems not as awesome against large databases — so I listed the DBCC page method.

1.4) Can I see the data on the page that was locked?

Well, yes. But … do you really need to?

This is slow even on small tables. But it’s kinda fun, so… since you read this far… let’s talk about %%physloc%%!

%%physloc%% is an undocumented piece of magic that will return the physical record locator for every row. You can use %%physloc%% with sys.fn_PhysLocFormatter in SQL Server 2008 and higher.

Now that we know that the page lock wait was on Sales.OrderLines, we can see all the data in that table on data file = 3 and page number = 70133 with this query:

Use WideWorldImporters;
                                            GO
                                            SELECT 
                                                sys.fn_PhysLocFormatter (%%physloc%%),
                                                *
                                            FROM Sales.OrderLines (NOLOCK)
                                            WHERE sys.fn_PhysLocFormatter (%%physloc%%) like '(3:70133%'
                                            GO

Use WideWorldImporters;
GO
SELECT
sys.fn_PhysLocFormatter (%%physloc%%),
*
FROM Sales.OrderLines (NOLOCK)
WHERE sys.fn_PhysLocFormatter (%%physloc%%) like '(3:70133%'
GO

Use WideWorldImporters;
                                            GO
                                            SELECT 
                                                sys.fn_PhysLocFormatter (%%physloc%%),
                                                *
                                            FROM Sales.OrderLines (NOLOCK)
                                            WHERE sys.fn_PhysLocFormatter (%%physloc%%) like '(3:70133%'
                                            GO

Like I said, this is slow even on tiny tables. I’ve added NOLOCK to the query because while we want a glance at this info, we have no guarantee that it’s the way it was earlier when the blocking happened anyway– we’re guessing, so we may as well do dirty reads.

But woo hoo, it gives me a clean display of the 25 rows which the query was fighting for:

That’s enough detail on waitresource=PAGE.

What if you were waiting on a KEY?

2) waitresource=“KEY: 6:72057594041991168 (ce52f92a058c)” = Database_Id, HOBT_Id ( Magic hash that you can decode with %%lockres%% if you really want)

If your query was trying to lock a row in an an index and was blocked, you get a totally different style of address.

Breaking “6:72057594041991168 (ce52f92a058c)” down, we’ve got:

database_id = 6
hobt_id = 72057594041991168
magic hash value = (ce52f92a058c)

2.1) Decode the database_id

This works the exact same way it did for the page example above! Find the database name with this query:

SELECT 
                                                name 
                                            FROM sys.databases 
                                            WHERE database_id=6;
                                            GO

SELECT
name
FROM sys.databases
WHERE database_id=6;
GO

SELECT 
                                                name 
                                            FROM sys.databases 
                                            WHERE database_id=6;
                                            GO

In my case, that’s still the WideWorldImporters sample database.

2.2) Decode the hobt_id

We need to use that database, and then query sys.partitions, with some helper joins to figure out the table and index name…

USE WideWorldImporters;
                                            GO
                                            SELECT 
                                                sc.name as schema_name, 
                                                so.name as object_name, 
                                                si.name as index_name
                                            FROM sys.partitions AS p
                                            JOIN sys.objects as so on 
                                                p.object_id=so.object_id
                                            JOIN sys.indexes as si on 
                                                p.index_id=si.index_id and 
                                                p.object_id=si.object_id
                                            JOIN sys.schemas AS sc on 
                                                so.schema_id=sc.schema_id
                                            WHERE hobt_id = 72057594041991168;
                                            GO

USE WideWorldImporters;
GO
SELECT
sc.name as schema_name,
so.name as object_name,
si.name as index_name
FROM sys.partitions AS p
JOIN sys.objects as so on
p.object_id=so.object_id
JOIN sys.indexes as si on
p.index_id=si.index_id and
p.object_id=si.object_id
JOIN sys.schemas AS sc on
so.schema_id=sc.schema_id
WHERE hobt_id = 72057594041991168;
GO

USE WideWorldImporters;
                                            GO
                                            SELECT 
                                                sc.name as schema_name, 
                                                so.name as object_name, 
                                                si.name as index_name
                                            FROM sys.partitions AS p
                                            JOIN sys.objects as so on 
                                                p.object_id=so.object_id
                                            JOIN sys.indexes as si on 
                                                p.index_id=si.index_id and 
                                                p.object_id=si.object_id
                                            JOIN sys.schemas AS sc on 
                                                so.schema_id=sc.schema_id
                                            WHERE hobt_id = 72057594041991168;
                                            GO

That tells me that the query was waiting for a lock on Application.Countries, using the PK_Application_Countries index.

2.3) Now for some %%lockres%% magic – if you want to figure out which row was locked

If I really want to know exactly which row the lock needed, I can decode that by querying the table itself. We can use the undocumented %%lockres%% function to find the row equal to that magic hash value.

Note that this is going to scan the table, and on large tables that might not be so awesome all the time:

SELECT
                                                *
                                            FROM Application.Countries (NOLOCK)
                                            WHERE %%lockres%% = '(ce52f92a058c)';
                                            GO

SELECT
*
FROM Application.Countries (NOLOCK)
WHERE %%lockres%% = '(ce52f92a058c)';
GO

SELECT
                                                *
                                            FROM Application.Countries (NOLOCK)
                                            WHERE %%lockres%% = '(ce52f92a058c)';
                                            GO

Voila, the row we were fighting for appears!

2.0

Blocking Statements

Returns the text of the SQL batch that is identified by the specified sql_handle.

This query will show you the SQLHandle that was unique in the blocked process report and contains a value other than blank or 0x0.

The SQLHandle is a varbinary data type that is suitable for binary data of variable length. The sql_handle is a token that references a stored procedure or a batch.
You can be accessed for example via the function "Dashboard -> SQL Server -> SQL Server Check -> Performace | Monitoring -> SQL Handle Short ".

2.0

Blocking Dashboard

From version 3 onwards, you have the option of filtering data volumes for display. Before each option, the data filter dialog to include the data display.

Workflow	Description	Version
1. Date	The overview always starts with the oldest and youngest date of the imported records. You can change the date by moving the sliders.	2.0
2. Time	The time display is a 24-hour display. You can limit the desired time period using dropdown boxes.	3.0
3. Check Count	Press the button to display the number of filtered records.	3.0
4. State	The number of filtered records is displayed here.	3.0
5 "OK" Button	Press the "OK" button to display the result in the dialog box.	3.0

Since version 3 of V8 Search XE dashboard and charts are displayed as HTMl pages You can also use these pages outside the application. The files are located in the specified working directory of V8 search XE. The dashboard currently consists of two areas:

1. Tables

Page	Description	Version
BL (DateTime \| same App)	View Target Data imports the accumulated data from multiple .XEL files from the given event session. After successful import, you will receive a message about the number of records imported into the V8 database. For more information, see the Creating Extended Events - V8 Search XE - Product Documentation	2.0
BL (DateTime \| div. App)	See the above description. For more information, see the Creating Extended Events - V8 Search XE - Product Documentation	3.0
BL (TSQL \| Time \| Count)	See the above description. For more information, see the Creating Extended Events - V8 Search XE - Product Documentation	3.0

2. Charts

Page	Description	Version
BL (TSQL \| Time \| Count)	View Target Data imports the accumulated data from multiple .XEL files from the given event session. After successful import, you will receive a message about the number of records imported into the V8 database. For more information, see the Creating Extended Events - V8 Search XE - Product Documentation	2.0

3.0

Blocking vs Long Duration

In the view, you can see detailed information about the records captured by the blocked process session. The displayed data are in the long duration range and identified as blocking process.

DESCRIPTION:
This overview contains the most important information to determine the causes of table locks from Dynanamics objects.

3.0

Requirements

Installing

Licensing

Configuring V8 client

Configuring V8 services

SQL Server instance for V8

General

SQL Server

SQL Database

System

Dashboard

Ribbon UI

TSQL Analyze

XE Profiler

DockPanels

Dialogs

Analysis graph

Dynamics NAV/BC

Troubleshooting

Release notes and
other versions

Folder Blocking

Blocking - Performance troubleshooting and profiling for Dynamics NAV | BC and SQL Server

Contains the following informational sections:

General

1) waitresource=“PAGE: 6:3:70133 ” = Database_Id : FileId : PageNumber

1.1) Decode the database_id

1.2) Look up the data file name — if you’re interested

1.3) Get the name of the object from DBCC PAGE

1.4) Can I see the data on the page that was locked?

2) waitresource=“KEY: 6:72057594041991168 (ce52f92a058c)” = Database_Id, HOBT_Id ( Magic hash that you can decode with %%lockres%% if you really want)

2.1) Decode the database_id

2.2) Decode the hobt_id

2.3) Now for some %%lockres%% magic – if you want to figure out which row was locked

Requirements

Installing

Licensing

Configuring V8 client

Configuring V8 services

SQL Server instance for V8

General

SQL Server

SQL Database

System

Dashboard

Ribbon UI

TSQL Analyze

XE Profiler

DockPanels

Dialogs

Analysis graph

Dynamics NAV/BC

Troubleshooting

Release notes and other versions

Folder Blocking

Blocking - Performance troubleshooting and profiling for Dynamics NAV | BC and SQL Server

Contains the following informational sections:

General

1) waitresource=“PAGE: 6:3:70133 ” = Database_Id : FileId : PageNumber

1.1) Decode the database_id

1.2) Look up the data file name — if you’re interested

1.3) Get the name of the object from DBCC PAGE

1.4) Can I see the data on the page that was locked?

2) waitresource=“KEY: 6:72057594041991168 (ce52f92a058c)” = Database_Id, HOBT_Id ( Magic hash that you can decode with %%lockres%% if you really want)

2.1) Decode the database_id

2.2) Decode the hobt_id

2.3) Now for some %%lockres%% magic – if you want to figure out which row was locked

Release notes and
other versions