In the article, “Working with Estimated Query Execution Plans,” December 2007, I discussed how execution plans can be used to estimate both the complexity of relational queries and the size of query result sets. I also built a sample .NET application that demonstrated how to use execution plans from a client application. In this article, I’ll demonstrate how a data-processing extension that wraps around the default SQL Server Reporting Services (SSRS) 2005 data-processing extension can prevent complex queries or queries with large estimated result sets from running. To accomplish this task, we will build a custom data-processing extension.
SSRS 2005’s Extensibility
One of the great things about SSRS 2005 is its extensibility. Everything from the data source to the final delivery format of a report can be customized. I’m going to show you how to create a custom data source, which is referred to as a “data-processing extension.” Specifically, I’ll show you how to create a data-processing extension to wrap around the default data-processing extension that ships with SSRS 2005. This wrapper will provide us with the flexibility to easily (and transparently) enforce custom thresholds such as the maximum estimated number of rows a report will return or the maximum query cost.
Data-Processing Extension Overview
A SSRS data-processing extension lets you connect to a data source and retrieve data. The extension is a set of .NET interfaces that you develop in a language such as C# or Visual Basic .NET and compile into a DLL. The interfaces are modeled after a subset of the interfaces used to implement a Microsoft .NET Framework data provider. (For more information about .NET Framework data providers, go to msdn2.microsoft.com/en-us/library/4ksaf9z5(vs.71).aspx.) In fact, the “Introducing Data Processing Extensions” section of SQL Server 2005 Books Online (BOL) includes a note that states “Reporting Services has built-in support for data providers in the .NET Framework. If you have already implemented a full data provider, you do not need to implement a Reporting Services data processing extension.”
So you might be thinking that it would make more sense to implement a .NET Framework data provider rather than a data-processing extension. After all, a data provider is more flexible because it can be used both inside and outside of SSRS (e.g., you can also use it in a .NET application). However, implementing a .NET Framework data provider involves quite a bit more work. For the scenario we are tackling (wrapping around the default SQL Server data-processing extension), a data-processing extension is the easiest way to go.
Getting Started
To develop a data-processing extension for SSRS 2005, you’ll need a system that has Report Designer or Report Server installed on it. You’ll also need either Microsoft Visual Studio 2005 or the .NET Framework Software Development Kit (SDK). My example is written in Visual Basic (VB).
As I’ve stated, a data-processing extension is implemented as a set of .NET interfaces. There are 11 .NET interfaces—up to seven of which you’re required to implement. See the article “Preparing to Implement a Data Processing Extension” (msdn2.microsoft.com/en-us/library/ms153967.aspx) for a description of each interface. Although you might be intimidated by the thought of implementing as many as 11 interfaces, you’ll see that there are certain scenarios in which you’ll need to implement only one or two of the interfaces.
Figure 1 shows the simplified process flow of a
|
Why Build a Data-Processing Extension? When developing a report using Report Designer, you have a great degree of flexibility in terms of the SQL statements that a report will execute. For example, you can insert custom code directly into a report to estimate its complexity or row count. Using this approach, you wouldn’t need to build a data-processing extension. However, if you needed to duplicate this process across tens or even hundreds of reports, you’d have quite a bit of work to do (not to mention a maintenance challenge if the custom code needed to be changed in the future). A data-processing extension lets you centralize this logic in a way that’s transparent to the report developer. In addition, you don’t always have control over a report’s SQL statements. For example, SSRS 2005’s Report Builder generates a report with the help of a report model. At runtime, the Report Server builds a SQL statement based on this model, so there’s no way for you to interject additional SQL commands into the statement. In this scenario, a data-processing extension is the only viable approach to enforce custom thresholds. |
data-processing extension called by the Report Server. Each blue box represents an object implementing a specific interface. The Connection object, which implements the IDbConnection interface, represents a connection to the data source. In Figure 1, the Report Server creates a Connection object and passes in the ConnectionString and security Credentials. Next, the report command text (i.e., the query) is passed to the Connection object, and then the .CreateCommand method is called to create a Command object (the Command object implements the IDbCommand interface). If the query contains any parameters, the Command object will then create Parameter objects (i.e., the IDataParameter interface). Then the Command object’s .ExecuteReader method is called to generate a result set, which is represented by the IDataReader interface. The Report Server uses the data in this result set to build the report according to the report’s layout. Let’s jump into the code to get a better feel for the sample implementation.
Exploring the Sample Project
If you haven’t already done so, download the SQLWrapperDP.vbproj project. Then, open the SQLWrapperDP.vbproj project file in Visual Studio 2005. I have documented the code fairly thoroughly, but let’s explore some of the key points. I implemented only the IDbConnectionExtension interface, which is basically the IDbConnection interface as well as some additional authentication methods, and the IDbCommand interface rather than implementing a full-blown extension because I have simply wrapped around the built-in SqlConnectionWrapper that shipped with SSRS 2005. If you refer back to Figure 1, you can see that the Report Server communicates with a data-processing extension exclusively through the Connection interface. In my Connection implementation, I instantiate a private instance of SSRS 2005’s built-in SqlConnectionWrapper, which I have named m_innerDBConnExt, and I often defer to this object for most of the required interface methods. The following code is used to implement the ConnectionTimeout property:
Public ReadOnly Property ConnectionTimeout() As Integer _ Implements Microsoft.ReportingServices.DataProcessing.IDbConnection.ConnectionTimeout Get Try Return m_innerDBConnExt.ConnectionTimeout Catch ex As Exception Throw ex End Try End Get End Property
Note that the code returns the timeout value from the built-in wrapper (i.e., m_innerDBConnExt). I also instantiated a private instance of SSRS 2005’s built-in Command object within my IDbCommand implementation.
The bulk of the custom logic is located in the ExecuteReader method of my IDbCommand implementation, which is shown in Listing 1. Within this method, I call the GetEstimatedCostAndRows private function (as shown in Callout A in Listing 1), which uses the same logic I explored in “Working with Estimated Query Execution Plans.” Specifically, I use the showplan_xml statement to generate an estimated execution plan for the query. Then I use XML Path Language (XPath) to parse the execution plan to calculate the query’s estimated cost and number of rows that will be returned. If the estimated cost and/or row count exceeds the configurable maximum values, the query isn’t executed and an error is thrown.
There is also a bit of custom logic in the ConnectionString property of my IDbConnectionExtension implementation. Configurable maximum values are specified in the ConnectionString value, so we need to parse out the extension-specific values before setting the Connection string of the built-in SqlConnectionWrapper, as shown in Listing 2.
Deployment and Configuration
I have provided deployment instructions in the CommentsAndDeploymentInstructions.txt file, which is part of my SQLWrapperDP.vbproj project. After the extension has been deployed, you can test it by creating a new Connection object in either Report Manager or Report Designer. You can then point reports (or even a Report Builder model) to the Connection object.
Figure 2 shows an example Connection (created in Report Manager) that uses our custom data-processing extension. Custom values (e.g., maxestrows, maxestcost) are simply appended to the standard SQL Server Connection string values as name/value pairs.
Debugging a Data-Processing Extension
The easiest way to debug a data-processing extension is to use the extension in the Report Designer. The SQL Server 2005 Books Online (BOL) article “Debugging Data Processing Extension Code” provides good instructions for debugging data-processing extension code.
Caveats and Warnings
By wrapping around the built-in SqlConnectionWrapper, I was able to save quite a bit of time. However, I also ended up with code that’s a bit less than optimal. For example, to implement the GetEstimatedCostAndRows private function, I ended up having to create two commands rather than one.
Also, I discovered SSRS doesn’t properly display custom errors from a data-processing extension. In the Report Designer environment, when my data-processing extension throws a custom error (e.g., if the query’s Estimated Rows value exceeds the maxestrows value), you get the full error, which is shown in Figure 3.
However, at runtime in Report Builder, Report Manager, or using the Report Server Web service you get only a generic error (shown in Figure 4) about the report being aborted, which means end users won’t know why the report didn’t run. Perhaps in a future build of SSRS, custom errors will be visible through the various end-user tools and interfaces.
Finally, it’s important to note that using a custom data-processing extension for a Report Model isn’t supported by Microsoft, although my testing didn’t reveal any problems. If you need to involve Microsoft Support to solve a problem, I recommend making sure you can reproduce the error using the built-in SqlConnectionWrapper.
Addressing Problematic Queries
Caveats and warnings aside, this data-processing extension identifies and stops queries with a high estimated cost and/or result set size. In environments in which reporting and OLTP transactions coexist, this type of “proactive avoidance” can help address problematic queries before they cause performance problems.
I would like to thank Bob Meyers, senior development lead on the Reporting Services team at Microsoft, for his data-process extension example on the MSDN blogs at http://blogs.msdn.com/bobmeyers/archive/2006/10/31/enforcing-timeouts-on-report-builder-queries.aspx.
