Real World Query Plans

Question

Real World Query Plans

Viewing 8 posts - 16 through 22 (of 22 total)

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

Adam Machanic SSCoach Points: 15370 More actions · Answer 1

Was it only running badly with certain parameters, and running correctly with others? Sounds to me like parameter sniffing at work...

--
Adam Machanic
whoisactive

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

That was implied in the article (rogue parameter giving different results than normal queries).

Adam Machanic SSCoach Points: 15370 More actions · Answer 3

Here's an article I wrote that takes a slightly different tactic for dealing with this issue. I'm not sure if it would have helped in this case, but the more tools in your box, the better...

http://www.sqljunkies.com/WebLog/amachanic/articles/StoredProcedureCaching.aspx

--
Adam Machanic
whoisactive

Tonkman SSC Rookie Points: 46 More actions · Answer 4

Steve - fully agree with you in regard to relating your experience in a story rather then a KB or list. I for one certainly retain that sort of information longer then any other type of article, there should be more of them. I also benefit from your story as I see no chance of myself ever coming in contact with a Microsoft Storage Engineer, and then being in a position to plug them with some real life scenario type question!

Dave.

Ian Yates SSCoach Points: 19738 More actions · Answer 5

Definitely the current SET options can impact on your code and the query plan used - things like CONCAT_NULL, ANSI_NULLS, etc make changes to the way the query engine processes the data, thus the plan would be different right?

I've had the same situation where my ADO application did not perform as well as QA. I ended up setting server defaults for the SET options to match those that are required for indexed views so that I can read from indexed views, and update their tables, without getting errors.

sql8081 SSC Enthusiast Points: 133 More actions · Answer 6

Another option may be to create multiple stored procedures (perhaps using the "sprocName;#" syntax) and call a different one for each set of parameters that generate a different plan. For sets of parameters that use an index scan, use spMyProc;1. For sets that use an index seek, or another index, execute spMyProc;2 etc. This implies you know when the execution plans switch.

Steve, thanks for your comments in your article, "I know we all wish there was some great list that we could reference..." I've been under pressure to document rules and decision-trees for newbies in my SQL team. Management wants absolutes. But there are few absolutes when observing the query optimizer. I think I'll just refer them to BOL and these posts.

deden SSChasing Mays Points: 610 More actions · Answer 7

I have also something similar, a complex job that is running in about 1.5 hours (every two hours, every day) that from time to time (may be once every two monthes) starts to take 11-15 hours without apparent reasons...

What I did, instead of adding a recompile to all involved sp, is to run:

-DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS when it happens.

It is not really smart but it has always solved the problem without restarting the server and without the performance impact of the "WITH RECOMPILE" on each execution.

gundapaneni Mr or Mrs. 500 Points: 593 More actions · Answer 8

It is really interest to know about query plans, perticularly about realworld situations. I am also facing similar situations so many times in my production enviorment.

-Madhu.

Madhu