none
Query runs much slower when connect to SQL Server locally.

    Question


  • Hi, I have been experiencing a very weird problem.

    I have a SQL 2008 R2 server which run on an active/active cluster node, and I run a query from its Management studio query windows locally, it takes 25 seconds to complete.

    I then connect to this SQL 2008 R2 server from a remote client (also management studio), and run the same query, it just takes 8 seconds to complete.

    Both connections use TCP/IP protocol.

    I included client statistics while running the query, and when run it locally, I see much more 'client processing time' than 'wait time on server replies'.

    The SQL 2008 R2 hardware is much more power than the client server. The server CPU, Memory, and Disk IO usage is very low. But I see lots of 'Network IO waits' during business hours.

    Anyone knows why this could be happening? I am really confused a lot. Usually run a query locally should be faster than run it remotely, right?

    Any idea would be very appreciated!!!


    Wednesday, February 22, 2012 2:45 PM

Answers

  • Glad I could help Vivian.  An XEvents trace will give you a very precise breakdown of where SQL is spending its time while executing this query.  It's highly likely that you'll see most of the time difference in the ASYNC_NETWORK_IO type, but you won't know the specifics without XEvents.  It's hard to say why the clients perform differently.  There is a slim chance it's the actual network, though this is unlikely with the local client being slower.  It could be memory, page file performance, CPU, something in the network IO stack, etc. 

    Here are a few good links to get you started on XEvents (BOL is very good too):

    http://www.simple-talk.com/sql/performance/investigating-sql-server-2008-wait-events-with-xevents/  - Mario Broodbakker understands troubleshooting and this post inludes a very good overview and a canned collection and analysis script.  This is the quickest way to go from reading my response to looking at wait data.

    http://sqlsaturday.com/viewsession.aspx?sat=91&sessionid=4847 - The download button has scripts and a slide deck I presented last August on performance troubleshooting with XEvents.  It gives a little more background to XEvents and the troubleshooting process, but is a good 1-hour summary.

    http://sqlskills.com/blogs/jonathan/category/XEvent-a-Day-Series.aspx - Jonathan Kehayias did a whole series on XEvents.  It's probably the best source of XEvents I've seen on the net.  Not a quick read by any means, but very much worth reading.

    It sounds like we're on different sides of the globe, but feel free to ping me if you have other questions or need help setting something up.

     
    • Edited by Mark House Thursday, February 23, 2012 4:08 AM Poor late-night grammar
    • Proposed as answer by Naomi N Thursday, February 23, 2012 5:43 AM
    • Marked as answer by Vivian_Vivian Thursday, February 23, 2012 11:58 AM
    Thursday, February 23, 2012 4:07 AM

All replies

  • Hi Vivian,

    It sounds like you're most likely looking at ASYNC_NETWORK_IO waits.  This wait type means that SQL is actually waiting on the client to consume results.  This can mean a slow network connection, but is usually the client.  I'm guessing that the dataset returned is rather large.  If this is the case, you can quickly test this by changing your query so that it returns a minimal amount of data to the client.  Run the same query, but instead of returning the rows to the client, simply return a count.  You have to be a little careful here because the execution plan can change when only a count is needed.  Another way to test this would be to dump the query results into a temp table, returning nothing to the client.  This virtually eliminates the resultset size as a factor.

    Finally, if you can use XEvents to trace the waits and execution stats on the query.  This is very precise, but will take a little more work to setup and analyze. 

    Personally I'd dump the results into a temp table and compare execution times between clients.  My guess is they will be very similar, which means that ASYNC_NETWORK_IO is the most likely culprit.  If you need a breakdown of the specifics, use XEvents.

    I'll keep my eye on this thread today.  If you need help setting anything up just let me know.

    • Proposed as answer by Naomi N Thursday, February 23, 2012 5:42 AM
    Wednesday, February 22, 2012 3:27 PM
  • hi Mark, thank you so much for your reply. sorry I didn't reply it timely coz it was night here:-) Yes, the query I run returned 50000 rows. And I see ASYNC_NETWORK_IO waits from time to time. I will test it again during business hours only using select count and using temporary table.

    It's just weird that when I run same query from SQL Server local management studio, it is slower than running from other SQL Server management studio. Do you think this slow SQL Server's client (management studio) is slower? or maybe because both server activitis and client activites happen on this server, so it become slower?

    Actually when user run query (returns a lot of data as generating report) from application, it is slow but still is a bit faster than running directly on this SQL Server.

    BTW, according to vendor's advise to eliminate some deadlock issue, we have turned on read_commited_snapshot, it had been fine for a while so I think it shouldn't be the cause of sudden performance degrade.

    I will research the XEvents you mentioned, it seems very helpful tool for our situation.

    Thanks again!!

    Wednesday, February 22, 2012 11:56 PM
  • Glad I could help Vivian.  An XEvents trace will give you a very precise breakdown of where SQL is spending its time while executing this query.  It's highly likely that you'll see most of the time difference in the ASYNC_NETWORK_IO type, but you won't know the specifics without XEvents.  It's hard to say why the clients perform differently.  There is a slim chance it's the actual network, though this is unlikely with the local client being slower.  It could be memory, page file performance, CPU, something in the network IO stack, etc. 

    Here are a few good links to get you started on XEvents (BOL is very good too):

    http://www.simple-talk.com/sql/performance/investigating-sql-server-2008-wait-events-with-xevents/  - Mario Broodbakker understands troubleshooting and this post inludes a very good overview and a canned collection and analysis script.  This is the quickest way to go from reading my response to looking at wait data.

    http://sqlsaturday.com/viewsession.aspx?sat=91&sessionid=4847 - The download button has scripts and a slide deck I presented last August on performance troubleshooting with XEvents.  It gives a little more background to XEvents and the troubleshooting process, but is a good 1-hour summary.

    http://sqlskills.com/blogs/jonathan/category/XEvent-a-Day-Series.aspx - Jonathan Kehayias did a whole series on XEvents.  It's probably the best source of XEvents I've seen on the net.  Not a quick read by any means, but very much worth reading.

    It sounds like we're on different sides of the globe, but feel free to ping me if you have other questions or need help setting something up.

     
    • Edited by Mark House Thursday, February 23, 2012 4:08 AM Poor late-night grammar
    • Proposed as answer by Naomi N Thursday, February 23, 2012 5:43 AM
    • Marked as answer by Vivian_Vivian Thursday, February 23, 2012 11:58 AM
    Thursday, February 23, 2012 4:07 AM
  • Hi Mark,  Last night we switch the heavy SQL Server to another cluster node, and today the performance is improved significantly.  The network wait stats is improved a lot either.

    Now on the original slow node, we run another light SQL Server, whose work load is very less. But I am seeing lots of ASYNC_NETWORK_IO  'wait_time_ms' in dm_os_waits_stats. Its cumulative network waits time is more than the heavy SQL Server.

    So now I suspect it could be an issue related with network configurations, and asked our server team guys to help check.

    Thanks for sharing the good XEvents materials with me, I am researching it and found it very helpful with identifying waits in SQL Server. I have set up it today and digging on how to analyze the result! I believe the materials will help me save a lot of time.

    I will post it back if I found the cuase of the slow cluster node.

    Thanks again for your great help, really appreciated !! :-)


    Thursday, February 23, 2012 12:11 PM
  • Glad I could help Vivian.  An XEvents trace will give you a very precise breakdown of where SQL is spending its time while executing this query.  It's highly likely that you'll see most of the time difference in the ASYNC_NETWORK_IO type, but you won't know the specifics without XEvents.  It's hard to say why the clients perform differently.  There is a slim chance it's the actual network, though this is unlikely with the local client being slower.  It could be memory, page file performance, CPU, something in the network IO stack, etc. 

    Here are a few good links to get you started on XEvents (BOL is very good too):

    http://www.simple-talk.com/sql/performance/investigating-sql-server-2008-wait-events-with-xevents/  - Mario Broodbakker understands troubleshooting and this post inludes a very good overview and a canned collection and analysis script.  This is the quickest way to go from reading my response to looking at wait data.

    http://sqlsaturday.com/viewsession.aspx?sat=91&sessionid=4847 - The download button has scripts and a slide deck I presented last August on performance troubleshooting with XEvents.  It gives a little more background to XEvents and the troubleshooting process, but is a good 1-hour summary.

    http://sqlskills.com/blogs/jonathan/category/XEvent-a-Day-Series.aspx - Jonathan Kehayias did a whole series on XEvents.  It's probably the best source of XEvents I've seen on the net.  Not a quick read by any means, but very much worth reading.

    It sounds like we're on different sides of the globe, but feel free to ping me if you have other questions or need help setting something up.

     

    Hi Mark,

    I have been able to use xevents now. I tested the same query from local SSMS(A), and from SSMS on the other cluster node (B). And results as below. It seems run query locally, it spent much network_IO waits. Do you think if it means a slow client? or is it because some network configuration issue with node A?

    On node A (local, sql service is also running on this node), the query took 20 seconds, and its wait state as below:

    wtype                               wcount   total_time    signal_time
    NETWORK_IO                   4418      19398          0

    SOS_SCHEDULER_YIELD   2            2                  0

    on node B (the other node), the query took only 9 seconds, and wait stats as below:

    wtype                                 wcount   total_time    signal_time
    NETWORK_IO                     115         8504            0
    SOS_SCHEDULER_YIELD    145          0                 0

    Monday, February 27, 2012 6:05 AM