Intercept and Rewrite Queries in SQL Server 2005

Question

We have an application built on top of SQL server 2005 that we have no control over. We've recently discovered that this application is sending some very inefficient SELECT queries to SQL that is causing serious capacity issues on the database. I know the right solution is to crack open the code for the application and change the queries, but for reasons I won't go into that's going to take a very long time. Is there some way we can intercept this specific query on SQL server and selectively re-write it to something more optimal?

Answer 1

You could use this approach. It works like a charm for me:)

Rather than attempting to intercept and modify SQL calls originating from the application, perhaps you can instead implement an abstraction layer without changing the application's SQL. For example, if you can modify the DSN or login connection string for the application, then connsider the following. Let's assume the current database is [A]. Create a new database [B] that contains views and functions (but not tables) with the same name as what is in [A], then modify them to reference the tables in [A]. Add whatever additional joins, filtering, etc. are needed to implement your (what I'm assuming) row based security. Then, modify the application DSN to use database [B] instead of [A].

link

Answer 2

It depends on what they are doing and what the queries are. Of course, if they are using sprocs or UDF's you can replace those without changing the application. You can also consider adding some indexes that are "optimized" for their bad SQL (though that may affect legitimate users of the database). You might also check the queries they are doing and see if you can replace the tables they are hitting with a more efficient view, but then you are messing with your DDL just to deal with a bad apple. Your best bet is probably to migrate legitimate applications off that particular server and leave the offender alone to rot.

Answer 3

You could try plan guides. This may allow you to tune/optimise the queries without changing the actual call.

From "Understanding Plan Guides"

This procedure can be used when you cannot or do not want to change the text of the query directly. Plan guides can be useful when a small subset of queries in a database application deployed from a third-party vendor are not performing as expected. Plan guides influence optimization of queries by attaching query hints to them.

This could also be useful to make the query really run like a lame dog, so that the developers come and ask for your help... ;-)

Answer 4

Have you taken a resource editor/reflector to the executable files? If you're lucky and the SQL Statements are static you may be able to change them.

Without more info about the app it's difficult to determine if this is possible. If the SQL is dynamically generated then this isn't an option.