cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Majk Frequent Visitor
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 Established Member
Established Member

Re: [GetCatalogExtendedContentData] procedure killing server

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
Frequent Visitor

Re: [GetCatalogExtendedContentData] procedure killing server

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
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Top Kudoed Authors
Users Online
Currently online: 174 members 1,626 guests
Please welcome our newest community members: