sp execution performance issue

Question

Post reply

sp execution performance issue

bala2

SSCertifiable

Points: 5547
More actions
February 3, 2016 at 1:13 pm

#315619

Hi All,
Today i have heard a strange issue from apps team.
We have store procedure on one of our sql server 2008 server database. When we ran that sp(sp_new)
manually it took only 16 secs to complete and same sp we re-created with different name and run the sp(sp_new_1) ,it took almost 20 mints to complete.
The apps team what why it is taking more time while executing same sp with different name.
any idea please?

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

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

Alexander Suprun SSCertifiable Points: 6253 More actions · Answer 1

In short, different parameter values can generate different execution plans.

There is a very good article on that topic: Slow in the Application, Fast in SSMS?

Alex Suprun

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 2

bala2 (2/3/2016)
Hi All,
Today i have heard a strange issue from apps team.
We have store procedure on one of our sql server 2008 server database. When we ran that sp(sp_new)
manually it took only 16 secs to complete and same sp we re-created with different name and run the sp(sp_new_1) ,it took almost 20 mints to complete.
The apps team what why it is taking more time while executing same sp with different name.
any idea please?

On the same server / database? Are there parameters as Alex suggests? Can you post the two execution plans as .sqlplan attachments?

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

bala2 SSCertifiable Points: 5547 More actions · Answer 3

Both sps we are running manually thorough ssms on Same server and databases .

Orlando Colamatteo SSC Guru Points: 182293 More actions · Answer 4

Please post the actual execution plans for both calls.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 5

Is the problem reproducable?

Have you checked if any blocking was going on during the second execution?

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

sp execution performance issue

Cookies on SQLServerCentral