cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Majk
Frequent Visitor

[GetCatalogExtendedContentData] procedure killing server

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]:

 

scr.PNG

 

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.

2 REPLIES 2
stpnet
Impactful Individual
Impactful Individual

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

 

Majk
Frequent Visitor

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.

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.