| Author |
Message
|
| pavdaddy72 |
 Posted: Wed May 07, 2014 12:33 pm Post subject: Stored Proc taking 13-mins to respond - SQL Server |
 |
|
Newbie
Joined: 07 May 2014
Posts: 6
|
| I have an interface that calls a SQL Server stored proc. For the past 4 weeks, there has been no issue. Either from local or on the DEV environment, the call to stored proc work from wmb esql worked. As of this morning, something changed. Now it takes 13 minutes to get a response from the stored proc. What could have changed? When I call the stored proc on SQL mgt studio, I get an instant response. No code changes were made. The perplexing thing to me is, both from local broker or DEV, the long response time is the same. |
|
| Back to top |
|
 |
| fjb_saper |
| Posted: Wed May 07, 2014 12:39 pm Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
Check with your network guys to see where the time is being spent.
May be you are returning significantly more rows than expected and processing through these rows is where the time is spent? 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| pavdaddy72 |
| Posted: Wed May 07, 2014 12:53 pm Post subject: |
|
|
Newbie
Joined: 07 May 2014
Posts: 6
|
| Just checked, it's not sending any extra rows. I expect 13 rows, and I'm getting 13 rows, 13 minutes later. Crazy. Gotta be a networking issue, somewhere. |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Wed May 07, 2014 1:08 pm Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
| pavdaddy72 wrote: |
| Just checked, it's not sending any extra rows. I expect 13 rows, and I'm getting 13 rows, 13 minutes later. Crazy. Gotta be a networking issue, somewhere. |
Check with the networking guys and the DB guys... (dropped index?) and find where the time is being spent. Having an execution plan for the query might help too.
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| DirtyHowi |
| Posted: Thu May 08, 2014 5:38 am Post subject: |
|
|
Novice
Joined: 17 Feb 2014
Posts: 22
|
| also have your DBA reset statistics on the DB. things in SQL can get out of whack, especially on a transactional DB, so resetting statistics shoudl be a maintenance plan. |
|
| Back to top |
|
|
| pavdaddy72 |
| Posted: Fri May 09, 2014 10:41 am Post subject: |
|
|
Newbie
Joined: 07 May 2014
Posts: 6
|
| We were able to temporarily solve the problem by deleting and recreating the stored procedure. But the problem came back the next day, for the last two days, around the same time. No batch processes are running. Crazy. |
|
| Back to top |
|
|
| sanupoju |
| Posted: Wed May 14, 2014 1:21 am Post subject: |
|
|
Newbie
Joined: 31 Jul 2012
Posts: 3
|
Check the Database by hitting directly so that we can observe which is consuming much time either Database or Broker.
Is there any complex query written in that procedure?
If yes,
- check the cost of explain plan of that query
- check indexes are there for columns used in where clause, need to create if they are not created already.
- Maintain esql code level standards by reducing cast functions usage in where clause. |
|
| Back to top |
|
|
|
|
