Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Sp performence. Expand / Collapse
Author
Message
Posted Sunday, December 9, 2012 9:38 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, June 9, 2014 1:01 AM
Points: 40, Visits: 169
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
Post #1394401
Posted Sunday, December 9, 2012 10:14 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 2:26 PM
Points: 223, Visits: 1,719
If stats are accurate, then most probably you encounter problem with http://sqlinthewild.co.za/index.php/2007/11/27/parameter-sniffing/
Post #1394403
Posted Sunday, December 9, 2012 10:58 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:51 PM
Points: 42,756, Visits: 35,850
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 2008, MVP
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

Post #1394404
Posted Sunday, December 9, 2012 11:15 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, June 9, 2014 1:01 AM
Points: 40, Visits: 169
is the sp execution plan will be changed for the grouping of small and large data set in the sp ?
Post #1394406
Posted Sunday, December 9, 2012 11:17 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, June 9, 2014 1:01 AM
Points: 40, Visits: 169
@Gila: How can we know the wait type and locking objects ? there is no any blockage in sp_who2 active command.
Post #1394407
Posted Sunday, December 9, 2012 12:21 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:51 PM
Points: 42,756, Visits: 35,850
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 2008, MVP
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

Post #1394409
Posted Sunday, December 9, 2012 12:21 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:51 PM
Points: 42,756, Visits: 35,850
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 2008, MVP
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

Post #1394410
Posted Monday, December 24, 2012 5:06 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 5:20 AM
Points: 3,546, Visits: 2,651
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/


+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.
Post #1399862
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse