May 11, 2016 at 10:59 am
In my environment, I've got a third party DB that is set to read-only. I don't have control over the schema or stored procedures in there. Our main application, uses an API to lookup information in this static DB. My problem is that one of these APIs calls a stored procedure that regularly gets a bad query plan due to parameter sniffing.
The proc in question has a query that involves a big table (~500 million rows) and passes a couple of its parameters to that query. The performance gets bad when it compiles the plan with parameters that return a small number of rows from the big table, then uses the same plan for one that returns a large amount of data for the big table. A fairly common plan quality problem. In this case, the proc goes from a few hundred reads to a few hundred thousand and given that this proc runs a lot, the extra reads and CPU really add up.
If I controlled the code there are several things that I could do to the stored procedure to address this problem, however the 3rd party has has been unresponsive in addressing this issue and I can't update the proc or schema myself.
Given that the database is read-only, I can't even manually set the proc to recompile the plan without cutting off all connections to the DB by setting it to read-write. When this gets bad
When this happens, I generally have to either wait it out or set the DB to read-write and recompile the proc with parameters that will generate a plan that will work in all scenarios, then set the DB back to read-only. The downside to this is all connections are severed (twice) and the plan doesn't stay good forever and eventually SQL recompiles it with bad parameters again.
Is there anything that I do to prevent/fix the bad plan given the limitations I pointed out above?
May 11, 2016 at 11:06 am
That's a bit of a rough situation.
One other option would be to grab the plan_handle of the proc when it's performing poorly, and run a DBCC FREEPROCCACHE, passing in that particular plan handle.
If the more common runs are with parameters that return the larger result sets, then there would be a decent chance of having it recompiled with a more appropriate query plan.
That way you also avoid severing connections, and by passing in the particular plan handle to FREEPROCCACHE, you're only flushing that plan out of the cache, not all of them.
Obviously that's not a long-term solution, but it might help minimize the pain for now.
Using a plan guide might be better as a workaround. Using plan guides comes with a whole bunch of caveats, but there might not be a much better option in this case.
Cheers!
May 11, 2016 at 11:21 am
I'm with Jacob. The best approach is to use a plan guide. Either use the guide to pass a hint for the query, or, use plan forcing to pick the plan for the query to use. Whichever one you think will work best in your situation. Just so you know, plan forcing is hard. Plan guides aren't easy. White space, every single carriage return, tab & space, matters.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
May 11, 2016 at 1:46 pm
Thanks for the responses guys. I tried out the DBCC FREEPROCCACHE with the plan handle and it works just fine with a read-only DB, so I'll use that while working on a more permanent solution.
It looks like I have some reading and testing to do with the plan guides to see what will work best for me in this situation. At least it's probably easier and less risky than my plan to infiltrate the 3rd party company and update their code for them. 😀
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply