[GetCatalogExtendedContentData] procedure killing server

[Majk]

Hello there,

Im running on-premise PowerBI Report Server on MS-SQL Server and after some time the reports on server become unavailable to users. My monitoring shows that this is caused by resource wait type ASYNC_NETWORK_IO (orange line) and associated stored procedure [dbo].[GetCatalogExtendedContentData]:

I assume this procedure is called by server itself, but user action such as report refresh might be calling it. From query I understand what it does, but since values in database are hashed I don't know the purpose of it.

Can anyone describe me when and how is this procedure called, so I can find the problematic report / user action causing the problem? Or if there is any documentation regarding these SP, I would welcome it as well.

[Anonymous]

I'm not sure if this is useful. But the SSRS ReportServer DB has two tables in it worth looking at

NOTE MS deliberately don't provide much (if any) documentation on this stuff as they may change the schema/behaviour entirely in any given SSRS release.

dbo.Catalog which holds the information about the reports. If the thing is an old fashioned SSRS report or a KPI etc the actual definition (the content of the RDL file) is held in the Content column

The Type column in this has previously been documented as (I can't remember wher I got this list)

1 = Folder
2 = Report
3 = Resources
4 = Linked Report
5 = Data Source
6 = Report Model
7 = Report Part (SQL 2008 R2, unverified)
8 = Shared Dataset (SQL 2008 R2)

we do seem to have a new type value of 13 which is a PBIX report

When the report is a PBIX report then you will see that the content column of dbo.Catalog is NULL

At this point you can take a look in dbo.CatalogItemExtendedContent table and you will see multiple rows for the PBIX each with a different ContentType

CatalogItem
DataModel
PowerBIReportDefinition

So when you save a report onto the PBI-SSRSS Server you get the actual PBIX file (PowerBIReportDefinition) it also gets split into its visualisation (CatalogItem) and the compressed data blob (DataModel) this is so that scheduled refresh of the data can push data into just the data blob.

When you view a report the datablob gets spun up in a cpative SSAS instance and the visualisation bit gets sent to be rendered on the client, the two are hooked togetehr with some services so as the usre clicks aorund new queries get generated and sent to the data model and returned to the client to be re-rendered.

So I suspect you've got somehting funny going on with the fetch of the content blobs.

You can take a look at the sizes of these using a query like this

SELECT	c.Path,
		c.Name
	   ,cic.ContentType
	   ,CAST(LEN(cic.Content) AS MONEY) / CAST(1024 * 1024 AS MONEY) AS ContentMb
	   ,cic.ModifiedDate
FROM	dbo.CatalogItemExtendedContent cic
			LEFT OUTER JOIN dbo.Catalog c
				ON c.ItemID = cic.ItemId
ORDER BY c.Path, c.Name, c.ItemID

I do find that once the data blob gets beyond a certain size the server does feel unresponsive at certain points.

Quite how all this is handled when the data blob is being refreshed I'm not entirely sure. DOes a read get blocked while a refresh happens?

I would speculate that the data blob for the example you are looking at is pretty sizeable and that may be part of your problem.

While this isn't an answer, I hope it will point you at some things to look at or investigate that might help.

regards

Steve

[omnia_saleh]

how to see content of CatalogItem,DataModel,PowerBIReportDefinition

[Majk]

So far I was only able to improve the situation by increasing memory of VM machine from 16GB to 32GB and maximum memory of SQL Server from 12GB to 20GB, even thought it only uses +/- 4GB. Output buffer also increased in size and therefore can hold more data and ASYNC_NETWORK_IO wait times are lowered by 40%.

I've contacted official MS support and will keep poking to the server.