Stored Procedure is running very slow

Question

Post reply

Stored Procedure is running very slow

only4mithunc

SSCrazy Eights

Points: 8434
More actions
April 19, 2012 at 12:51 am

#267730

Hi all,
I have two environments where I’m running one SP. But in one of the environment the SP was taking a long time. When I ran it as a script both the environments took same time and I got the same output.
When I check the estimated plan for both of this (by making it as a script) I have seen that it is showing some missing index in the environment where it was taking long time, but when I checked the table it has the indexes already and the other environment I didn’t get any such hints telling that index is missing.
So what could be the reason why it is slow in one environment? May be it is using an old plan?
Thanks & Regards,
MC

Viewing 15 posts - 1 through 15 (of 17 total)

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

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 1

Hi,

This may help you:

1) Fist try update statistics

http://msdn.microsoft.com/en-us/library/ms187348(v=sql.90).aspx

then try your SP.

2) If no improvement, then try rebuild/reorganize indexes and then update statistics.

http://msdn.microsoft.com/en-us/library/ms189858.aspx

Regards

IgorMi

Igor Micev,My blog: www.igormicev.com

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 2

Hi,

This may help you:

1) Fist try update statistics

http://msdn.microsoft.com/en-us/library/ms187348(v=sql.90).aspx

then try your SP.

2) If no improvement, then try rebuild/reorganize indexes and then update statistics.

http://msdn.microsoft.com/en-us/library/ms189858.aspx

Regards

IgorMi

Igor Micev,My blog: www.igormicev.com

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 3

Hi,

This may help you:

1) Fist try update statistics

http://msdn.microsoft.com/en-us/library/ms187348(v=sql.90).aspx

then try your SP.

2) If no improvement, then try rebuild/reorganize indexes and then update statistics.

http://msdn.microsoft.com/en-us/library/ms189858.aspx

Regards

IgorMi

Igor Micev,My blog: www.igormicev.com

only4mithunc SSCrazy Eights Points: 8434 More actions · Answer 4

only4mithunc

SSCrazy Eights

Points: 8434

April 19, 2012 at 1:36 am

#1475877

Thanks IgorMi I will try this.

Thanks & Regards,
MC

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

It might be statistics that are out of date, but from the sounds of it, I'd want to be sure that the structures are identical, that the code in both queries is identical, that the data on both systems is identical and that you're using identical parameters. If there are variations on any of this (in addition to the statistics) you can get different execution plans.

"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

only4mithunc SSCrazy Eights Points: 8434 More actions · Answer 6

Hi Grant Fritchey, thanks for the reply.

I can confirm that both environment same code was ran for same set of parameters and getting same output. So the one thing which I might have to check is the statistic.

Thanks & Regards,
MC

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 7

Also check the server settings, especially default connection settings. If one is using ANSI standard and the other is not, again, you might get different execution plans.

"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

only4mithunc SSCrazy Eights Points: 8434 More actions · Answer 8

Hi Grant Fritchey thanks.

The settings are same, I will try updating the statistics. But then Im wondering if it is the problem with outdated statistics then how both are giving same result within same time when I ran it as a script?

Thanks & Regards,
MC

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 9

If by script you mean you didn't run a stored procedure? It might mean that you're seeing bad parameter sniffing. Which, again, could be caused by differences in statistics.

Remember, I'm not there. I can't see everything you can see. You've given me an extremely limited set of information, a procedure runs differently on two servers. From there, I'm guessing based on things I've seen in the past and some understanding of how SQL Server works.

"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

only4mithunc SSCrazy Eights Points: 8434 More actions · Answer 10

Hi Grant Fritchey,

Sorry if I didn't mention the issue clearly. I can explain the case here:

1. I have two servers( say A and B) , I have one stored procedure which I ran in both A and B for same set of parameters.

2. From server A I got the expected out put with in 1 minute but in server B it took more time ( I have stopped it after 15 minutes)

3. Then I took the code from the SP and tried running it as a script by passing the same set of parameters, then both A and B gave me same output with in 1 minute.

As per the reply which I have got from you as well as from other friends here, I'm thinking to rebuild the statistics of the underlying tables in server B, but then my doubt is if it is the problem with outdated statistics in server B then how I'm getting the expected output with in the expected time when I ran it as a script. All the settings in both the servers are same.

Thanks & Regards,
MC

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

Show us how you ran the stored procs and got different timings and how you ran them as scripts and got the same timings.

Seeing code will help.

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 12

Based on that, you also could just be looking at contention on the second server. However, from what you said, you saw two different execution plans. Two plans are caused by differences in data, code, statistics, ANSI settings, constraints, structures, parameters. Has to be one of those. No other options really.

"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

only4mithunc SSCrazy Eights Points: 8434 More actions · Answer 13

Friends,

When I updated the statistics it resolved the problem thanks to all who helped me.

But the question "how both the severs gave me same result within same time when I ran as a script..?" is still remaining with me.

Unfortunately I cannot post the code here. What I did to run it as a script was I have commented out the SP Name, parameter,Recompile option etc as below and declared the variables and passed the values.

--ALTER procedure [dbo].[sp_test](@id int, @in varchar(30))

--WITH RECOMPILE

--as

begin

declare

@id int = 100,

@in varchar(30) = 'test value'

---

--- body of sp

---

end

Thanks & Regards,
MC

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 14

It's parameter sniffing at work. When you use local variables, you get a scan of the statistics (unless a recompile is involved). When you use parameters, you get a search on the statistics using the parameter value. If one set of stats was radically off from the other, you'll get a different plan because of the parameter sniffing. If the stats are off enough, you can get different plans from the local variables too.

"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