InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 13:58:19 GMT20http://www.sqlservercentral.com/Forums/Topic1394401-360-1.aspx[quote][b]e4d4 (12/9/2012)[/b][hr]If stats are accurate, then most probably you encounter problem with [url=http://sqlinthewild.co.za/index.php/2007/11/27/parameter-sniffing/]http://sqlinthewild.co.za/index.php/2007/11/27/parameter-sniffing/[/url][/quote]+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.Mon, 24 Dec 2012 05:06:40 GMTsqlnaivehttp://www.sqlservercentral.com/Forums/Topic1394401-360-1.aspx[quote][b]nitin.varshney (12/9/2012)[/b][hr]@Gila: How can we know the wait type and locking objects ? there is no any blockage in sp_who2 active command.[/quote]Query sys.dm_exec_requests. There are several columns relating to the waits.Sun, 09 Dec 2012 12:21:31 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1394401-360-1.aspx[quote][b]nitin.varshney (12/9/2012)[/b][hr]is the sp execution plan will be changed for the grouping of small and large data set in the sp ?[/quote]Not unless something invalidates or removes the one plan and regenerates. A stored procedure has a single execution plan cached for it.Sun, 09 Dec 2012 12:21:02 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1394401-360-1.aspx@Gila: How can we know the wait type and locking objects ? there is no any blockage in sp_who2 active command.Sun, 09 Dec 2012 11:17:44 GMTnitin.varshneyhttp://www.sqlservercentral.com/Forums/Topic1394401-360-1.aspxis the sp execution plan will be changed for the grouping of small and large data set in the sp ?Sun, 09 Dec 2012 11:15:35 GMTnitin.varshneyhttp://www.sqlservercentral.com/Forums/Topic1394401-360-1.aspxNot enough information.Could be locks or waits for other resourcesCould be bad exec plansCould be several other things.When it runs slow, what's the wait type?Sun, 09 Dec 2012 10:58:59 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1394401-360-1.aspxIf stats are accurate, then most probably you encounter problem with [url=http://sqlinthewild.co.za/index.php/2007/11/27/parameter-sniffing/]http://sqlinthewild.co.za/index.php/2007/11/27/parameter-sniffing/[/url]Sun, 09 Dec 2012 10:14:44 GMTe4d4http://www.sqlservercentral.com/Forums/Topic1394401-360-1.aspxHi,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.ThanksNitin VarshneySun, 09 Dec 2012 09:38:35 GMTnitin.varshney