Jason A. Long (5/28/2015)
First thing first, here is the query that I'm using...
WITH ContentToVarBin AS (
SELECT
c.Name,
CAST(c.Content AS VARBINARY(MAX)) AS Content
FROM
ReportServer.dbo.Catalog c
WHERE
[Type] = 2
), ContentToXML AS (--The second CTE strips off the BOM (Byte Order Mark) if it exists...
SELECT
cvb.Name,
CAST(CASE
WHEN LEFT(cvb.Content, 3) = 0xEFBBBF
THEN STUFF(cvb.Content, 1, 3, '')
ELSE cvb.Content
END AS XML) AS ContentXML
FROM
ContentToVarBin cvb
)
SELECT
ctx.Name,
DataSet.value('(./*:DataSet/@Name)[1]','nvarchar(max)') AS DataSetName,
ISNULL(Query.value('(./*:CommandType/text())[1]','nvarchar(1024)'),'TextQuery') AS CommandType,
Query.value('(./*:CommandText/text())[1]','nvarchar(max)') AS CommandText
FROM ContentToXML ctx
--Get all the Query elements (The "*:" ignores any xml namespaces)
CROSS APPLY ctx.ContentXML.nodes('//*:Query') Queries(Query)
CROSS APPLY ctx.ContentXML.nodes('//*:DataSets') DataSet(DataSet)
The query is based on code that I found here[/url]...
The basic idea is to query out all of data set names & command text (proc names) from all of the reports in the catalog, so that we have an accurate idea for which procs are being used in which reports...
The query above pulls the information exactly the way I want it to but the performance leaves a lot to be desired and execution plan... Well let's just say it's expensive.
The problem is that I'm not a fan of anything XML when it comes to SQL... Consequently, I'm not as proficient at writing XML based queries as I probably could/should be. I was hoping that someone may have some performance related advise to offer.
The basic stats...
- The Catalog table has 1,473 "Type = 2" rows (Reports).
- The Content column, for those rows is 1.2 gigs.
- Total rows returned for the query is 4,161
- Query execution time = 5:20 Mins.
Thanks in advance,
Jason
I actually did a presentation that covered this very topic at a recent BI user group but don't have the code handy at the moment.
My experience has been that, unless you get a parallel query plan when querying ReportServer.dbo.Catalog.Content the the performance is a disaster.
See this article for forcing a parallel plan. I bet this will help a great deal:
http://sqlblog.com/blogs/adam_machanic/archive/2013/07/11/next-level-parallel-plan-porcing.aspx
All you have to do is create the function that Adam provides in his article then add an addition CROSS APPLY that references the function like so:
CROSS APPLY make_parallel()
-- Itzik Ben-Gan 2001