Using SQLCLR in SQL Server 2005 to process XML Showplan

Build an application to extract the estimated execution cost of the query from its XML Showplan. Users can only submit queries that cost less than a predetermined threshold to a server running SQL Server 2005, ensuring that the server is not overloaded with costly, long-running queries.

First, Introduction

Microsoft SQL Server 2005 makes its query execution plan (also known as Showplan) available in XML format. You can also use any XML technology (such as XPath, XQuery, or XSLT) to handle XML Showplan. This article describes an application that extracts the estimated execution cost of a query from its XML Showplan. The costs extracted by Transact-SQL are available for Transact-SQL Windows. With this technology, users can only submit queries that cost less than a predetermined threshold to a server running SQL Server. This ensures that the server is not overloaded by costly, long-running queries.

II. Objectives and audiences

The goal of this article is SQL Server developer and database Administrator (DBA). It briefly introduces the SQLCLR (Common language Runtime) to the database administrator. The application used to extract Showplan uses two Microsoft Visual C #. NET applet, and this article details how SQL Server invokes DLLs generated by compiling these programs. The application also uses XPath and XQuery technologies that can be used to query and extract information in XML data. SQL Server 2005 provides built-in support for both of these query languages. This article demonstrates how these two languages interoperate seamlessly with Transact-SQL.

Iii. Statement of issues

SQL Server DBA sometimes encounters a situation in which a user submits a long-running query to the server during peak hours, thereby reducing the server's response speed. There are two ways to prevent this from happening:

1.

The DBA can use sp_configure to set the query governor cost limit option to a specific threshold. (This is an advanced option.) This threshold value is valid throughout the server.

2.

To affect the threshold of a connection, the DBA can use the SET query_governor_cost_limit statement.