Sp performence.

Question

Sp performence.

nitin.varshney

SSC Eights!

Points: 948
More actions
December 9, 2012 at 9:38 am

#271400

Hi,
I am facing one issue in SP at prod. which is running at QA and staging within 1-2 mins. But at prod some time it is running in 1-2 min and some time it is not running even in 45 mins.
What can be the reason for that.Even all indexs are fine and no any Backup/maintainance activity running at prod.
Thanks
Nitin Varshney

Viewing 8 posts - 1 through 7 (of 7 total)

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

e4d4 SSCertifiable Points: 5777 More actions · Answer 1

If stats are accurate, then most probably you encounter problem with http://sqlinthewild.co.za/index.php/2007/11/27/parameter-sniffing/[/url]

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

Not enough information.

Could be locks or waits for other resources

Could be bad exec plans

Could be several other things.

When it runs slow, what's the wait type?

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

nitin.varshney SSC Eights! Points: 948 More actions · Answer 3

is the sp execution plan will be changed for the grouping of small and large data set in the sp ?

nitin.varshney SSC Eights! Points: 948 More actions · Answer 4

@Gila: How can we know the wait type and locking objects ? there is no any blockage in sp_who2 active command.

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

nitin.varshney (12/9/2012)
is the sp execution plan will be changed for the grouping of small and large data set in the sp ?

Not unless something invalidates or removes the one plan and regenerates. A stored procedure has a single execution plan cached for it.

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

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

nitin.varshney (12/9/2012)
@Gila: How can we know the wait type and locking objects ? there is no any blockage in sp_who2 active command.

Query sys.dm_exec_requests. There are several columns relating to the waits.

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

sqlnaive SSCoach Points: 17437 More actions · Answer 7

e4d4 (12/9/2012)
If stats are accurate, then most probably you encounter problem with http://sqlinthewild.co.za/index.php/2007/11/27/parameter-sniffing/[/url]

+1. Excellent link.

Nitin, I've faced such situations in real time scenarios. I tried a lot, moved from here to there, tested every possibility. Finally found this parameter sniffing stuff. For me, changing the procedure with recompile option worked well.