Replace bad vendor query..

Question

Replace bad vendor query..

Erin Ramsay

SSCrazy Eights

Points: 9728
More actions
September 22, 2014 at 9:38 am

#293515

Is there a mechanism that can do the following:
recognize a query when it comes in and replace it with an optimized one?
Basically I'm looking at a work-around for crappy vendor queries.

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

If this query is wrapped by a stored procedure (which it should...), it's fairly easy: just replace the bad performing code (but you'll violate contracts by doing so...).

If it is submitted as an ad-hoc query, then there's little to noch chance to change it other than change the code in the original app.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

SQLRNNR SSC Guru Points: 281344 More actions · Answer 2

Lutz was quicker on the draw than me.

About the best you can do with that bad query is to get an optimized query in the proc in place of it. Or submit the optimized code to the vendor and see if they will implement it for you so the contract is not violated.

Sometimes, you may be able to implement a plan guide to get an optimized execution for the query. But that would only work if your optimized version is close enough to theirs. So in that case, tread very lightly.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Erin Ramsay SSCrazy Eights Points: 9728 More actions · Answer 3

The majority are adhoc queries and even the ones in stored procedures are vendor owned and we can't change them. That's why I was hoping for something along the lines of a trace-and-replace solution.

SQLRNNR SSC Guru Points: 281344 More actions · Answer 4

Erin Ramsay (9/22/2014)
The majority are adhoc queries and even the ones in stored procedures are vendor owned and we can't change them. That's why I was hoping for something along the lines of a trace-and-replace solution.

Yeah there really isn't a trace and replace option. It could have pretty huge impact for some of these vendor queries.:cool:

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

ScottPletcher SSC Guru Points: 101200 More actions · Answer 5

Of course the only ultimate solution to "replace bad vendor query" is "replace bad vendor." 😀

You can set up a trace with a high threshold(s) to capture really bad queries. Then try to track back to where they came from 🙁 ... won't be easy, but don't know what else you could do.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Erin Ramsay SSCrazy Eights Points: 9728 More actions · Answer 6

Thanks, everyone. That's about what I thought the solution would be but wanted to exhaust my options and make sure I wasn't missing some great tool that was heretofore unknown.

Erin