MS SQL analysis

(couldn't post this as a comment due to character limitation) Tbaror, it depends on how proficient you are with pkt analysis. But the way I would start is by lookinging for TCP level anomalies. Open the trace in Wireshark and do a 'tcp.analysis.flags' to see what shows up. Things to took for are 1) FAST RETRANSMISSIONS 2) Increment duplicate ACK numbers (Dup Ack #1, Dup Ack #2, Dup Ack #3...etc) 3) Long gaps in between packets. You should add a delta (delta from previous packet) so you can quickly sort on it. Or you can change the default time display if you don't want to add a new column - but I highly recommend it. 4) TCP zero window events

If every other packet is flagged as "out of sequence, out of order, etc, then your capture was done incorrectly and you need to start over.

Once you've ruled out any and all tcp level issues (lack of tcp window size, etc.) then you need to look at what query is causing the server to be slow.

This will show up when you sort by the delta column. Typically, you will see a select/update query followed by a long delay before the server spits out the answer. In this case, it's safe to assume that server processing delay is at fault.

Other things to look for are records that come back one row at a time. One clue that this is happening is by looking at the end of the response. It will be marked by certain words that delimit the rows, or it may have the PSH bit set.

Also, the two most common issues I see with DB slowness (not including inefficient queries) are 1) table scan because index is not there or updated and 2) lack of batch size that causes inefficient SELECT queries.

Depending on your level of expertise, you will know what to do with the above info, or you may need more clarification. Just come back with the parts that don't make sense and I can expand on the topic. Also, if you go back to Wireshark's site, you should see a link for Sharkfest. I did a presentation with slow DB query where the batch size caused quite a bit of pain for the developers. If you can't find it, let me know as well.