We have a web app that is connecting to a remote SQL Server DB and is experiencing performance issues. To narrow the culprit down, we ruled out the network from the client to the webserver (webapp) and we did a DB trace of a typical function in the web app where users were seeing delays of up to 1 min -- and saw that the query only took 5 seconds once it was received by SQL Server.
This leaves the negotiation of the SQL Server connection itself. It's almost like it is trying to authenticate with the provided connection string but initially can't -- then it retries and eventually is successful and the transaction completes.
How can we use Wireshark to determine if this is indeed what's happening or if it's another kind of issue with establishing a connection to the DB?
asked 12 Dec '13, 23:51
What kind of negotiation is it that you're referring to? One way to confirm it on the wire would simply be to trace the traffic between the web/app server toward the database, and see how long it takes on the wire for that authentication to complete.
Ideally, you'd like to be able to get both the user to web/app server traffic and the web/app server to DB traffic in the same trace file, to see the whole call flow for a one-minute response to the user so that you can isolate what is taking so long. For example, does the query toward the DB take a minute after the user request to the web/app server (putting the blame on the web/app server in handling the request before talking to the DB), or is there a long one-minute authentication attempt takinig place between the web/app server and the DB (in which case, troubleshoot your authentication mechanism and application).
answered 13 Dec '13, 21:58
I'd start with an unfiltered trace on the DB server and see if there are any side steps like LDAP, reverse DNs lookups, CRL verifications etc ...
answered 14 Dec '13, 00:42