生成环境中，经常会捕获到一些消耗CPU和内存资源较多的Query，有一些来自某个APP，有一些来Client，还有一些来自报表服务器。通常报表服务器连接过来的都是通过配置好的共享DataSource, 所以很难判断是谁。 下面介绍一下我是如何根据Query快速找到Report Owner：
第一步： 将SSRS服务器所有Report的定义信息取出，并转换成可读的XML类型：
SELECT
[Path]
,CASE [Type]
WHEN 2 THEN 'Report'
WHEN 5 THEN 'Data Source'
END AS TypeName
,CAST(CAST(content AS varbinary(max)) AS xml) as command
, [Description] into #temp1
FROM PBIReportServer.dbo.[Catalog] CTG
WHERE
[Type] IN (2, 5)
第二步：将XML字段中Dataset中CommandText取出：
SELECT *, command.value('
declare namespace ns="http://schemas.microsoft.com/sqlserver/reporting/2016/01/reportdefinition";
(/ns:Report/ns:DataSets/ns:DataSet/ns:Query/ns:CommandText)[1] ', 'varchar(max)') as Result into #temp2
FROM #temp1
SELECT *
from #temp2
where Result like '%query statement%'
第三步：根据上面找到的Report Path，找到 Report Owner：
SELECT
ItemID -- Unique Identifier
, [Path] --Path including object name
, [Name] --Just the objectd name
, ParentID --The ItemID of the folder in which it resides
, CASE [Type] --Type, an int which can be converted using this case statement.
WHEN 1 THEN 'Folder'
WHEN 2 THEN 'Report'
WHEN 3 THEN 'File'
WHEN 4 THEN 'Linked Report'
WHEN 5 THEN 'Data Source'
WHEN 6 THEN 'Report Model - Rare'
WHEN 7 THEN 'Report Part - Rare'
WHEN 8 THEN 'Shared Data Set - Rare'
WHEN 9 THEN 'Image'
ELSE CAST(Type as varchar(100))
END AS TypeName
--, content
, LinkSourceID --If a linked report then this is the ItemID of the actual report.
, [Description] --This is the same information as can be found in the GUI
, [Hidden] --Is the object hidden on the screen or not
, CreatedBy.UserName CreatedBy
, CreationDate
, ModifiedBy.UserName ModifiedBy
FROM
[PBIReportServer].dbo.[Catalog] CTG
INNER JOIN
[PBIReportServer].dbo.Users CreatedBy ON CTG.CreatedByID = CreatedBy.UserID
INNER JOIN
[PBIReportServer].dbo.Users ModifiedBy ON CTG.ModifiedByID = ModifiedBy.UserID
where path in (
'/Contoso/Report1',
'/Contoso/Report2')
附：解析XML的另一个方法：
--drop table #temp1
--go
;
WITH ItemContentBinaries AS
(
SELECT
ItemID,Name,[Type]
,CASE Type
WHEN 2 THEN 'Report'
WHEN 5 THEN 'Data Source'
WHEN 7 THEN 'Report Part'
WHEN 8 THEN 'Shared Dataset'
When 13 Then 'Power BI Report'
ELSE 'Other'
END AS TypeDescription
,CONVERT(varbinary(max),Content) AS Content
FROM PBIReportServer.dbo.Catalog
WHERE Type IN (2,5,7,8,13) --AND ItemID = @ItemID
),
--The second CTE strips off the BOM if it exists...
ItemContentNoBOM AS
(
SELECT
ItemID,Name,[Type],TypeDescription
,CASE
WHEN LEFT(Content,3) = 0xEFBBBF
THEN CONVERT(varbinary(max),SUBSTRING(Content,4,LEN(Content)))
ELSE
Content
END AS Content
FROM ItemContentBinaries
)
--The old outer query is now a CTE to get the content in its xml form only...
,ItemContentXML AS
(
SELECT
ItemID,Name,[Type],TypeDescription
,CONVERT(xml,Content) AS ContentXML
FROM ItemContentNoBOM
)
--now use the XML data type to extract the queries, and their command types and text....
SELECT
ItemID,Name,[Type],TypeDescription,ContentXML
,ISNULL(Query.value('(./*:CommandType/text())[1]','nvarchar(1024)'),'Query') AS CommandType
,Query.value('(./*:CommandText/text())[1]','nvarchar(max)') AS CommandText into #temp1
FROM ItemContentXML
--Get all the Query elements (The "*:" ignores any xml namespaces)
CROSS APPLY ItemContentXML.ContentXML.nodes('//*:Query') Queries(Query)
select * from #temp1