none
network slowness for sql prod server RRS feed

  • Question

  • Hi Experts,

    Is there a dirty trick or easy way to check network issues or slow network for the database server without using any 3rd party monitoring tool? Can we use continuous ping from some quite non-prod server to prod sql server ? can we do that or is there a better way to do it?

    The reason why I am asking is, today some users complained they aren't able to login and seeing connection timeout expired. We we approached Network team, they said everything is fine. But I personally, experienced disconnects/reconnecting from Remote Desktop tool.

    How can we prove there is a potential issue with the network? and issue is also intermittent? look for easy solution which is light weight in nature.

    If anyone does have a solution then please share it.

    Thanks,
    Sam

    Tuesday, September 10, 2019 6:59 PM

Answers

  • Hi Samantha,

    If both are on the same network, then unless its a cabling issue, latency probably isn't the root cause.  If there was a network level error though then this should show on the switch port logs - maybe CRC errors.  If some do show there, it wont show when the counter started, so you should ask the network team to reset the counter and wait and see how many appear.

    The reconnects you mention in your original message, it this an RDP connection to the app where you use it from or is it a published app with Remote Desktop Service (i.e. accessed over a browser).

    If it is intermittent then you could also check the DNS settings on the NIC of the servers...if they reference the DB by name and the DNS IPs aren't correct or not responding properly then that may cause an intermittent timeout.

    Thanks,

    Matt

    • Marked as answer by Samantha v Thursday, September 12, 2019 11:32 AM
    Wednesday, September 11, 2019 6:49 PM

All replies

  • Hi,

    Is the app server and SQL server on the same network and location? I assume this is on-prem....are they physicalor virtual?  Knowing more about the infrastructure will help give pointers here.

    For the app connection timeout, you could do with verbose logging on the app as you need to catch this as it happens and if it is intermittent then its difficult to replicate.  Check the event logs on the app server too.

    However, I would also look at the hardware on the SQL server.  Check that for available memory (if it is low, do you have a sufficient page file set?).  You can sometimes get useful info from the SQL ERRORLOG too if you know times when this happened?

    Also check for any other resource contention on the SQL server.  I would also look at CPU time and disk load/queue.  You can set perfmon up to log things like %processor time, processor queue (this counter is under system, not processor in perfmon), disk reads/writes per second and also current disk queue length.  As those counters all use different scales, save them to CSV and you can then graph data from the output file to view it properly - thats what I do.

    Thanks,

    Matt

    Tuesday, September 10, 2019 8:20 PM
  • Perfmon also has network counters.

    So does the Windows task manager / resource monitor. 

    They at least tell you if *something* is going through or not.

    Ping and tracert are always good basic tools too.

    Josh

    Tuesday, September 10, 2019 8:33 PM
  • Hi Sam,

    You  can try to follow below steps to capture network package and then analyze it.

    a. Download and install Network Monitor 3.4 on SQL server and SQL client. 

    b. On SQL server, Open a dos command prompt,  run as the administrator: 
       md c:\netmon
       nmcap /network * /capture /CaptureProcesses  /file C:\netmon\SQLServerConnection.chn:50M

    c. On the client server, Open a dos command prompt,  run as the administrator: 
       md c:\netmon
       nmcap /network * /capture /CaptureProcesses  /file C:\netmon\ClientConnection.chn:50M
     
    d. Reproduce the issue , press Ctrl-C on both SQL server and client netmon window.
     
    e. On the SQL server machine that CMD window: 
       ipconfig /all > c:\netmon\SQLServerIPconfig.txt
       netstat -abno > c:\netmon\SQLServerNetstat.txt
     
    f. On the client machine that CMD window: 
       ipconfig /all > c:\netmon\ClientIPconfig.txt
       netstat -abno > c:\netmon\ClientNetstat.txt
     
     Analyze all the files in c:\netmon folder.


    Best regards,
    Cathy 

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, September 11, 2019 7:28 AM
  • Thanks Matt for those pointers. Both are on-prem physical servers and on the same network.

    Wednesday, September 11, 2019 9:47 AM
  • Thanks Josh.
    Wednesday, September 11, 2019 9:48 AM
  • Wednesday, September 11, 2019 9:51 AM
    Answerer
  • Thanks Cathy. I am not an expert in analyzing netmon traces. Basically, I wanted to know a way to determine quickly that there is network problem so that immediatly I can involve the systems team / network team.

    However, thanks for providing detailed steps. will be a learning curve for me.

    Regards,

    Sam

    Wednesday, September 11, 2019 9:52 AM
  • Hi Samantha,

    If both are on the same network, then unless its a cabling issue, latency probably isn't the root cause.  If there was a network level error though then this should show on the switch port logs - maybe CRC errors.  If some do show there, it wont show when the counter started, so you should ask the network team to reset the counter and wait and see how many appear.

    The reconnects you mention in your original message, it this an RDP connection to the app where you use it from or is it a published app with Remote Desktop Service (i.e. accessed over a browser).

    If it is intermittent then you could also check the DNS settings on the NIC of the servers...if they reference the DB by name and the DNS IPs aren't correct or not responding properly then that may cause an intermittent timeout.

    Thanks,

    Matt

    • Marked as answer by Samantha v Thursday, September 12, 2019 11:32 AM
    Wednesday, September 11, 2019 6:49 PM