2

Here is the issue overview: Why does my stored procedure run faster when executed localy vs remotely?

Dont jump to any conclusion just yet, let me explain what I mean...

Here is the setup:
A Windows 2008R2 (correction: 2003) application server executes a stored procedure that performs some action, (what its doing is really important at this point in time). This stored procedure is executed on the SQL server over a TCP/IP connection to the database server. The DB server is physicaly located right next to the application server, and they are connected to eachother via 1GB NICs to a 1GB Switch. The DB server is running SQL 2005 SP2 Enterprise Edition, and has 16GB of memory and several vLUNS striped across 48 15k drives in an HP-EVA FC connected SAN. From all indicators thus far, there are no I/O, Mem, or CPU constreints or limits being hit. Trace Falg 1118 is on and TempDB is split across 8 file on their own vLUN. Data, and TLogs also have their own vLUNS too.

So, here is what I am seeing:
Using SQLCMD on the database server, with SQLProfiler running from the same DB server, I can execute the stored procedure and I see that the execution starts immediatly, and compleats with a durration of about 2,100ms with an IO of about 1200.

Using SQLCMD on the application server, with SQLProfiler running from the DB server, I can execute the same exact stored procedure, with the SAME exact parameters, and I see that the execution starts immediatly, and compleats with a durration of about 110,000ms with an IO of about 1200.

The query results in 1 row, with 4 columns [INT, INT, VARCHAR(50), VARCHAR(100)]

ASIDE:(I know the query is a train wreck, this is a regulated system and I cannot change it on a live prodution server, so please dont make any sugestions about doing so. The next version has been rewritten to be better.)

From everything we can see, there is no reason that we should be seeing differances like this, but what is heppening is the .NET application that calls this query from the application server is timing out waiting for the responce.

We have checked locking and blocking, WAIT states, Query plans, IO contention, CPU contention, MEM contention, NETWORK saturation/utilization, performed indexes rebuilds on all indexes, updates all stats, and a hand full of other items, but haven't come up with anything that points to why this is happening.

Please ask more questions if you have any, make recomendations as you come up with them, and depending on the impact (remember this is a production environment) we will try them and respond back.

-Thanks! -Chris

Improve this question
10
  • 1
    Your description would seem to clearly indicate a nic/network problem. Make sure the nics and switches in between are set to the same setting (preferably Auto). If the speeds do not match, the connection to the nic defaults to single duplex rather than half duplex which means it does I or O at any given time, not both.
    – Robert L Davis
    Commented Mar 13, 2013 at 20:05
  • 1
    Check the connections between the two. How long does a ping/traceroute from the app server to the db server take? And reverse. Also check IPSec which can add a lot of overload to the processing of data.
    – Robert L Davis
    Commented Mar 13, 2013 at 20:08
  • 1
    Check to see if the nic is configured correctly for tcp chimney and offloading as well.
    – Robert L Davis
    Commented Mar 13, 2013 at 20:09
  • Adding to what Robert has mentioned - Is the NIC card configured in FULL DUPLEX mode ?
    – Kin Shah
    Commented Mar 13, 2013 at 20:15
  • 1
    If the list of "checked" is as comprehensive as you suggest, I'd be inclined to crack out Wireshark. Alternatively, show us the data you've gathered. Here is a great example question that demonstrates the benefit of sharing the detail of your investigations.
    – Mark Storey-Smith
    Commented Mar 13, 2013 at 23:21

3 Answers 3

Reset to default
1

Sounds like a connection issue or plan issue:

http://www.sommarskog.se/query-plan-mysteries.html

Improve this answer
1

This is not a networking issue (though that is tempting).

This is almost certainly "noise." What I mean here is that you need to run this operation perhaps 100 times on the db server itself and from the app server, preferably over a span of 2 hours or so (or more).

What I strongly expect you will find is that at certain times it runs fast on either/both systems, and at certain times it is slow no matter where it is initiated.

E.g. the SQL workload is what you are actually measuring, not the network.

Improve this answer
2
  • We will give this a run for the money, but something else has raised an idea too, without giving too much information away, this procedure runs fairly often, and it seems that with certian sets of parameters (namely specific account numbers) , its more likely to perform slowly than not, hoever there is no rhym or reason for why the accountnumber matters. In some cases the account is old with lots of entries, and in other its a brand new account.
    – RenoDBA
    Commented Mar 13, 2013 at 22:03
  • @RenoDBA If what you describe above is actually the case, then my answer above is correct: It is not where the command is run from... it is when/with what params... e.g. what you are measuring is SQL load....
    – Jonesome Reinstate Monica
    Commented Mar 14, 2013 at 0:25
0

If you execute them both from SSMS, using the 'Include Client Statistics' option in the Query menu you should be able to compare the bottom row "Wait Time on server replies" to validate. You may also get more insight into the amount of network chatter.

Improve this answer
2
  • only have SSMS on the DB server, using SQLCMD for testing. Have run it with stats io on, but that got really hard to read. What is the SQL command to get the Wait Time on Server Replies from SQLCMD?
    – RenoDBA
    Commented Mar 13, 2013 at 21:36
  • I believe if you add -p to SQLCMD you get some statistics, not sure if they line up with what is in SSMS.
    – Richard Schweiger
    Commented Mar 13, 2013 at 21:45

Not the answer you're looking for? Browse other questions tagged or ask your own question.