Protean

Ever been stuck trying to figure out what people are doing on your server, but unable to tell from DBCC InputBuffer what statements the users are running? This is one of the most useful scripts I’ve come accross and helps enormously when trying to analyse what is happening on your SQL Server.

It is often the case that users will query your database with tools like MS Access or Business Objects which are quite difficult to track. Well this useful tool helps to solve that problem… 

Take for example an Access select query, when you trace the activity with the profiler what you will see is many individual parameterized sp_prepare, sp_exec…() statements running against the box. Even when you execute the ubiquitous sp_who2, you still do not get the info you really want. Sure we know somebody is executing something against database x, but then we hit a wall… What are they selecting?; what are the predicates? how are they joining? and most importantly, how is my server dealing with it?

Step in the function “statement_level_query_plan()”. This function allows you to see any currently executing queries on your box and whats more gives you an XMLShowplan schema so that you can see how the box is handling the request.

This is a truly magnificent function for any DBA who wants complete visibility of what users are running against their server…

Firstly you’ll need to install the function on your box.

This is the code courtesy of Microsoft.

Once you have created your new function, you are ready to run the query.

Get XML execution plans for currently running queries.

So - what to do with this resultset? Well as you scroll along the resultset you will see a column [statement_plan] and in it there will be hyperlinks to the execution plan in its XML format.

1. Click on this link and a new XML document will be opened up.
2. Save this as a new document with the extension “.sqlplan”
3. Close and then re-open. A familiar execution plan will be visible with the SELECT statement at the top.

Once you have this tool you can harrass those people using their 3rd party tools to interrogate your database, and impress upon them what their poorly written query is doing to your system…

Happy days.

This entry was posted on Monday, January 22nd, 2007 at 10:27 pm and is filed under Nuggets, SQL Server DBA, SQL Server 2005, Performance, T-SQL. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.