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?
我们有一个基于SQL Server 2005构建的应用程序,我们无法控制。我们最近发现这个应用程序正在向SQL发送一些非常低效的SELECT查询,导致数据库出现严重的容量问题。我知道正确的解决方案是破解应用程序的代码并更改查询,但由于原因我不会进入这需要很长时间。有没有什么方法可以拦截SQL服务器上的这个特定查询,并有选择地重新写入更优化的东西?
4 个解决方案
#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].
而不是试图拦截和修改源自应用程序的SQL调用,也许您可以在不更改应用程序的SQL的情况下实现抽象层。例如,如果您可以修改应用程序的DSN或登录连接字符串,请对以下内容进行说明。我们假设当前数据库是[A]。创建一个包含视图和函数(但不是表)的新数据库[B],其名称与[A]中的名称相同,然后修改它们以引用[A]中的表。添加任何其他连接,过滤等,以实现您(我假设)基于行的安全性。然后,修改应用程序DSN以使用数据库[B]而不是[A]。
#2
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... ;-)
这也可能有助于使查询真正像跛狗一样运行,以便开发人员来寻求你的帮助...... ;-)
#3
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.
这取决于他们在做什么以及查询是什么。当然,如果他们使用的是sprocs或UDF,你可以在不更改应用程序的情况下替换它们。您还可以考虑添加一些针对其错误SQL进行“优化”的索引(尽管这可能会影响数据库的合法用户)。您也可以检查他们正在进行的查询,看看是否可以用更高效的视图替换他们正在使用的表,但是您只是为了处理坏苹果而弄乱您的DDL。你最好的选择可能是将合法的应用程序迁移到特定的服务器上,让犯罪者独自腐烂。
#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.
您是否已将资源编辑器/反射器用于可执行文件?如果您很幸运并且SQL语句是静态的,您可以更改它们。
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.
如果没有关于应用程序的更多信息,很难确定这是否可行。如果SQL是动态生成的,那么这不是一个选项。
#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].
而不是试图拦截和修改源自应用程序的SQL调用,也许您可以在不更改应用程序的SQL的情况下实现抽象层。例如,如果您可以修改应用程序的DSN或登录连接字符串,请对以下内容进行说明。我们假设当前数据库是[A]。创建一个包含视图和函数(但不是表)的新数据库[B],其名称与[A]中的名称相同,然后修改它们以引用[A]中的表。添加任何其他连接,过滤等,以实现您(我假设)基于行的安全性。然后,修改应用程序DSN以使用数据库[B]而不是[A]。
#2
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... ;-)
这也可能有助于使查询真正像跛狗一样运行,以便开发人员来寻求你的帮助...... ;-)
#3
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.
这取决于他们在做什么以及查询是什么。当然,如果他们使用的是sprocs或UDF,你可以在不更改应用程序的情况下替换它们。您还可以考虑添加一些针对其错误SQL进行“优化”的索引(尽管这可能会影响数据库的合法用户)。您也可以检查他们正在进行的查询,看看是否可以用更高效的视图替换他们正在使用的表,但是您只是为了处理坏苹果而弄乱您的DDL。你最好的选择可能是将合法的应用程序迁移到特定的服务器上,让犯罪者独自腐烂。
#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.
您是否已将资源编辑器/反射器用于可执行文件?如果您很幸运并且SQL语句是静态的,您可以更改它们。
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.
如果没有关于应用程序的更多信息,很难确定这是否可行。如果SQL是动态生成的,那么这不是一个选项。