Intercepting Queries and Rewriting in SQL Server 2005

Question

We have an application built on top of SQL Server 2005 that we do not control. We recently discovered that this application sends some very inefficient SELECT queries to SQL, which causes serious database throughput problems. I know that the correct solution is to crack the code for the application and modify the requests, but for reasons I will not go into something that will take a very long time. Is there a way to intercept this particular query on the SQL server and selectively rewrite it to something more optimal?

Answer 1

You can use this approach. It works like a charm to me :)

Instead of trying to intercept and modify the SQL calls coming from the application, perhaps you can instead implement an abstraction layer without changing the SQL. For example, if you can change the DSN or login string for an application, then the following. Suppose the current database is [A]. Create a new database [B], which contains views and functions (but not tables) with the same name as that in [A], then change them to refer to the tables in [A]. Add any additional pooling, filtering, etc. are needed for (what I assume) string-based security. Then change the DSN application to use database [B] instead of [A].

link

Answer 2

You can try planjists . This can allow you to tweak / optimize 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 request text directly. A leadership plan can be useful when a subset of the queries in a database application deployed from a third-party provider does not execute as expected. The influence of guide bars optimizes queries by attaching prompts to them.

It can also be useful in order to make the request really launched, like a lame dog, so that developers come and ask for your help ...; -)

Answer 3

It depends on what they do and what the requests are. Of course, if they use sprocs or UDF, you can replace them without changing the application. You may also consider adding some indexes that are “optimized” for their poor SQL (although this may affect legitimate database users). You can also check the queries that they execute and see if it is possible to replace the tables they click on with a more efficient view, but then you mess with your DDL to deal with a bad apple. It is best, most likely, to transfer legitimate applications from this particular server and leave the offender alone.

Answer 4

Have you taken the resource editor / reflector into executables? If you're lucky and the SQL statements are static, you can change them.

Without additional information about this application, it is difficult to determine if this is possible. If SQL is dynamically generated, this is not an option.

Answer 5

A similar question (for another purpose) was asked on reverse engineering of the Q & A site: intercepting requests on the server side using MS SQL Server .