<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2005
»
SQL Server 2005 Performance Tuning
»
Sp performence.
Sp performence.
Rate Topic
Display Mode
Topic Options
Author
Message
nitin.varshney
nitin.varshney
Posted Sunday, December 09, 2012 9:38 AM
Grasshopper
Group: General Forum Members
Last Login: Today @ 3:29 AM
Points: 23,
Visits: 104
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
e4d4
e4d4
Posted Sunday, December 09, 2012 10:14 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Today @ 5:58 AM
Points: 150,
Visits: 1,027
If stats are accurate, then most probably you encounter problem with
http://sqlinthewild.co.za/index.php/2007/11/27/parameter-sniffing/
Post #1394403
GilaMonster
GilaMonster
Posted Sunday, December 09, 2012 10:58 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 3:07 PM
Points: 37,687,
Visits: 29,946
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
nitin.varshney
nitin.varshney
Posted Sunday, December 09, 2012 11:15 AM
Grasshopper
Group: General Forum Members
Last Login: Today @ 3:29 AM
Points: 23,
Visits: 104
is the sp execution plan will be changed for the grouping of small and large data set in the sp ?
Post #1394406
nitin.varshney
nitin.varshney
Posted Sunday, December 09, 2012 11:17 AM
Grasshopper
Group: General Forum Members
Last Login: Today @ 3:29 AM
Points: 23,
Visits: 104
@Gila: How can we know the wait type and locking objects ? there is no any blockage in sp_who2 active command.
Post #1394407
GilaMonster
GilaMonster
Posted Sunday, December 09, 2012 12:21 PM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 3:07 PM
Points: 37,687,
Visits: 29,946
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
GilaMonster
GilaMonster
Posted Sunday, December 09, 2012 12:21 PM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 3:07 PM
Points: 37,687,
Visits: 29,946
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
sqlnaive
sqlnaive
Posted Monday, December 24, 2012 5:06 AM
SSCrazy
Group: General Forum Members
Last Login: Today @ 8:22 AM
Points: 2,653,
Visits: 1,655
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 »
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
