problem about the query plan

Question

problem about the query plan

fairyuseless

SSC Rookie

Points: 45
More actions
August 24, 2010 at 3:33 am

#226497

Hey you guys, i have a sp which processes certain amount of data, but something strange happens that the execution duration ranges widely according to the way to run it: when run it by the sp name like "exec [someSP]" or invoke it from outside, it turns out to be very slow like an hour or longer while only minutes cost if execute the sql code composing the sp in the query windows, I believe different query plans are created when run them differently, now I have to run the sp from the outside(which costs a lot of time), is anything i can do to the query plan to make it as fast as when I run the sql code?
thanks in advanced and the environment is sqlserver 2008

Viewing 6 posts - 1 through 5 (of 5 total)

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

steveb. SSC-Forever Points: 46733 More actions · Answer 1

You need to find out what the two different queries are, run a server side trace on the database and compare the query that is run externally with the one that you are running locally as there is a possibility that they have different settings. such as ANSI_NULLS or another setting different.

Also is there a possibility that this is a network issue?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Please don't cross post. It just wastes peoples time and fragments replies.

No replies to this thread please. Direct replies to: http://www.sqlservercentral.com/Forums/Topic973949-391-1.aspx

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 3

Steve's right, check the ANSI settings on the connections. There could be something there.

It's also possible that you're seeing parameter sniffing. The way you describe it, you have "outside" connections that are running EXEC sp_whatever (also, naming sp_* causes extra, unnecessary reads, don't do it) and the other connections are running the query in some other fashion. If that means what I think it means, then you're seeing local variables vs. parameters, which is a classic parameter sniffing issue. You should look at the good & bad execution plans to see if there are differences. Also, make sure your statistics are up to date. Don't just rely on the auto update.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

fairyuseless SSC Rookie Points: 45 More actions · Answer 4

thanks for all the replies, and sorry for the cross post.

First the "outside" i said means not only through the internet but anywhere except executing the sql code like call it int the query window with "exec sp_whatever" or call it in a local c# application by call its name, so through the internet is of course a way of "outside".

Grant mentioned the local viable vs parameters, and i think this is point, i tried change the parameter as a local viable and define the value the sql body of the sp then call it use "exec sp_whatever", it turns out to be as fast as run the sql code. So can you give me some hints or useful links to let me know how to deal whit this local viable&parameter thing? Thanks very much

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 5

fairyuseless (8/24/2010)
thanks for all the replies, and sorry for the cross post.
First the "outside" i said means not only through the internet but anywhere except executing the sql code like call it int the query window with "exec sp_whatever" or call it in a local c# application by call its name, so through the internet is of course a way of "outside".
Grant mentioned the local viable vs parameters, and i think this is point, i tried change the parameter as a local viable and define the value the sql body of the sp then call it use "exec sp_whatever", it turns out to be as fast as run the sql code. So can you give me some hints or useful links to let me know how to deal whit this local viable&parameter thing? Thanks very much

Right, that's what's known as parameter sniffing. Basically the optimizer can use values in a local variable when creating the execution plan, but has to make guesses at the value when using a parameter. Normally, this is fine, but sometimes it's not. Either your data is a bit skewed, such that some values generate better plans than others, in which case you might want to look at using a query hint, OPTIMIZE FOR, to ensure those values are used when the plan compiles. Or, your statistics might be out of date on the data or indexes and needs to be updated, possibly with a full scan instead of the standard sampled scan.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning