Search For a Query in RDL Files with PowerShell

In tracking down poorly performing queries for clients I often encounter the query text in a trace file I’ve captured, but don’t know the source of the query. I’ve found that many of the poorest performing queries are those written into the reports the business users need to make their decisions. If I can’t figure out where they came from, usually years after the queries were written, I can’t fix them.

First thing I did was find a great utility called RSScripter, which opens up a Windows dialog that allows you to connect to a Reporting Services server and script the reports to RDL source files in a directory you specify. By exporting the RDL files from the Reporting Services server I can search through and find the queries I need to find.

The next thing I do is set a string variable to contain the query in question, which I do here using a here-string:

$q = @'SELECT  d.SalesOrderID,
        d.OrderQty,
        h.OrderDate,
        o.Description,
        o.StartDate,
        o.EndDate
FROM    Sales.SalesOrderDetail d
        INNER JOIN Sales.SalesOrderHeader h ON d.SalesOrderID = h.SalesOrderID
        INNER JOIN Sales.SpecialOffer o ON d.SpecialOfferID = o.SpecialOfferID
WHERE   d.SpecialOfferID <> 1'@

Now, when RSScripter runs it pulls out everything from RS, and I’m only interested in the RDL files so I use the Get-ChildItem cmdlet and pipe the output through the Where-Object cmdlet to select just those files whose extension is ‘.rdl’, and assign that to a variable.

$rdls = Get-ChildItem | where-object {$_.Extension -eq '.rdl'}

Now I have a collection of RDL file objects, which I can iterate through with the foreach command. For each one I’m going to display the file name, then use the Get-Content cmdlet to read the contents of the RDL file, and pipe that to the Select-String cmdlet to look for the query string. If a match is found it’ll display that line after the file name.

foreach ($rdl in $rdls) { write-output $rdl.Name; get-content $rdl.FullName | select-string $q }

While this may be a little crude, it’s kind of an ad-hoc way of finding where a query comes from. There are recursive options for Get-ChildItem that’ll search the entire subdirectory tree, but for now I didn’t need that.

I hope this helps you find some of those troublesome queries you’re struggling with.

Allen

(This blog was originally posted on October 16, 2012.)