In my sample project, EstimatedExecutionPlan_
WinApp.vbproj, I have built a .NET Windows application
that passes a query batch to a SQL Server 2005
database, which you can see in Figure 4. Let’s briefly
walk through the application logic. In the top part of the
main window, there’s a text box where you can enter a
connection string for a SQL Server 2005 database. The
large text box in the middle is used to specify a query
or a query batch. There are also two text boxes
near the bottom left side where you can enter
values to represent the maximum desired cost
and estimated rows for a query batch. When you
click Run, the code shown in Web Listing 1 is executed.
This code establishes a connection with the database,
then executes the SET SHOWPLAN_XML ON
command. Next, the code passes the query batch to
SQL Server. SQL Server returns an XML version of
the estimated execution plan. The plan is returned in
a SQLDataReader object with one column of data,
so the code iterates through the SQLDataReader object
to concatenate the plan back into a single string. After
executing the SET SHOWPLAN_XML OFF command,
the code uses XML Path Language (XPath) to parse the
string and calculate the total cost and total estimated rows
for the query batch. (Note that a user must be granted the
SHOWPLAN permission, which is a database-level permission,
to generate an XML showplan.) These values are
displayed in the two text boxes near the bottom right side
of the form and are compared to the maximum desired
values. The status text box at the very bottom of the form
indicates when one of the estimated values exceeds the
corresponding maximum desired value. Although all of
the code resides within the client application, you could
alternatively use a SQL Server CLR stored procedure to
house this logic in an instance of SQL Server.
The sample application simply lets you examine the
estimated cost and row count of a query batch, and
compare those values to a maximum desired set of values.
However, in your application, you could use this logic to
examine and potentially prevent complex queries from
running.
A Custom Data Processing
Extension for SSRS
The customer I mentioned at the beginning of this article
was using SQL Server 2005 Reporting Services (SSRS)
and rolled out Report Builder for ad hoc reporting. (For
more information about Report Builder, see my article
“No-Nonsense Reporting Tool,” June 2006, InstantDoc
ID 50032.) To leverage estimated plans in SSRS, a custom
data processing extension must be built. In an upcoming
article, I discuss how I built this extension.
Prevent Problematic
Queries from Running
Execution plans are a great tool for developers and DBAs
alike. Typically, they’re used to better understand, troubleshoot,
and tune queries. However, I also think execution
plans have the potential to help prevent problematic queries
from ever running. This kind of “proactive avoidance”
can be especially useful in ad hoc reporting environments.
End of Article
Prev. page
1
[2]
next page -->
