SQL Server – Can Batches Be Edited on the Fly?

sql server

I am supporting a third-party application which uses a SQL Server 2016 backend. We have no source code access, and the third-party support is slow to respond. Most of the time, I can index and magic my way out of the sometimes terrible queries that it throws at the database, but there have been multiple occasions where there is quite literally nothing I can do (within constraints of time, money, and storage). The query is just wrong.

Is there a way to administratively substitute the queries submitted by the application for other ones? I am imagining some sort of regex match performed against the batch if the application name matches.

Example:

-- Submitted by application
update Table set IsBilled = 0 where ID = '{EFD5F13C-6123-4913-B530-8941FC71B969}'

-- What I want the query to be (to avoid spurious updates to Table)
-- (Table is large, and the application is submitting this query on every page view)
update Table set IsBilled = 0 where ID = '{EFD5F13C-6123-4913-B530-8941FC71B969}' and IsBilled = 1

-- Proposed regex
-- Match
update Table set IsBilled = 0 where ID = '(\{[A-F0-9\-]+\})'
-- Replace
update Table set IsBilled = 0 where ID = '$1' and IsBilled = 1

Some of my conceived uses would be to avoid useless updates, others would be in rephrasing where clauses to maintain sargability. I am sure there are other scenarios that would come to mind.

Best Answer

Is there a way to administratively substitute the queries submitted by the application for other ones?

No, the closest you will get with out of the box functionality is what a plan guide can do.

I am imagining some sort of regex match performed against the batch if the application name matches.

Nothing native inside of SQL Server can do this, currently.

Can I edit the batches received by SQL Server on the fly?

Unfortunately (or fortunately) there is nothing built in to do this. I don't want to give you any ideas but you'd need to have a middle layer between the application and the database server. This intercepting application would need to understand TDS (which is the protocol used with SQL Server) and allow you make changes to (or, again, not) the TDS packets to change the queries on the fly.

If you made this, you should sell it :)