Exec Vs Sp_Executesql

Question

Exec Vs Sp_Executesql

apat

SSCertifiable

Points: 5929
More actions
October 22, 2009 at 7:51 am

#227293

Hi,
I am finding weared behaviour on different servers I work with. All of them have same SP being called from front end to fetch data. SP is using sp_exeutesql and it takes only few seconds to be executed on two of the servers and takes 15 minutes on one server. If I change sp_executesql to exec on that server (where it takes 15 min) then it is faster. What could be the possible reasons and solutions for this?
appriciate any help...

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

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

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 1

My initial thought is there is a bad query plan that is cached. The sp_* allows for better query plan re-use, the EXEC less so.

You might look at DBCC FREEPROCCACHE and if you can find the plan handle that is causing the problem you can clear it. If not you might have to kill the whole proc cache. Which isn't as terrible as it sounds. All stored procedures that run after the proc cache is whacked will have to regenerate query plans, but only the first time. It adds a little time to each. But sometimes it is worth it..

CEWII

apat SSCertifiable Points: 5929 More actions · Answer 2

well I already did dbccfreeproccache and it is still taking same time..

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 3

Was that server re-indexed/stats updated at the same time as the other servers, can you check? It could be that its stats and such are out of what and need a re-index..

CEWII

apat SSCertifiable Points: 5929 More actions · Answer 4

I updated statistics as well as rebuilt the indexes for this table.

natasha.gupta SSC Enthusiast Points: 156 More actions · Answer 5

I am seeing the EXACT same issue.

I ran a trace on our prod env and got the sql from there.

it is using EXEC sp_executesql - I took the sql part out and ran it through mgmt studio.

runs in 13 seconds.

But if i run what I capture in the trace....AS IS - it just sits there for more than an hour.

I did capture the execution plans..and the one from mgmt studio has hash match. The one through sp_executesql has only nestedloops.

this guy had the same problem

https://connect.microsoft.com/SQLServer/feedback/details/295646/performance-problem-with-paramenterized-select-statements

but microsoft closed the bug saying there was a problem with cardinality of case statement - I dont have case statements where this gets stuck...

Any ideas?!!

natasha