February 22, 2012 at 7:00 am
Hello, 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!!!
February 22, 2012 at 8:15 am
It depends on so many factors, including where the burden of the processing is occurring.
Grab an execution plan both from the local server and the remote box and compare them side-by-side. They should be the same. If they aren't, you know there's an issue.
Did you run the queries only once on each or multiple times? It could be a caching issue.
You should also test both local and remote while running Profiler and PerfMon to see what other things might be interfering.
February 22, 2012 at 5:05 pm
Hi Brandie,
Thanks a lot for your reply. yes I have been running them several times, excution plan is same, and difference I see is locally it consumes more 'client processing time' than remotely.
I have not used profiler yet, but I monitored cpu, memory, disk using system monitor and don't see them any big change, as this SQL server has 96gb memory and cpu load is always under 20%.
The query I run returns a lot of data (50000), do you think it could because both server activites and client activites happen on this SQL Server so it become slower than the remote one?
thanks again!
February 22, 2012 at 5:14 pm
What's the server set to consume for memory? The reason I ask is the client for SSMS may barely have enough room to run via RDC but your local system it has a lot more breathing room to display result sets. The query itself will almost always run in the server's cache, but the results have to return in the client's cache.
A good way to test that is to run the query in a window with discard resultset turned on from the options menu. Compare those values and you can confirm it's the client, not the network or anything else getting involved.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 22, 2012 at 5:14 pm
*vivian* (2/22/2012)
Hi Brandie,The query I run returns a lot of data (50000), do you think it could because both server activites and client activites happen on this SQL Server so it become slower than the remote one?
Oh yes, definately. Watch Perfmon for Page Faults.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 23, 2012 at 10:34 am
What Craig said, both times. @=)
February 24, 2012 at 6:21 am
Also check the user SET options under which each connection executed their query. That can have an effect. Outside of that, and assuming you didn't get the execution times backwards (i.e. the remote was 25 sec and local 8) then I am with others on memory issue on server itself.
Speaking of which, you DO have the max memory set to something appropriate, right??
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 26, 2012 at 10:16 pm
Hello, thanks all for replying! we have set max memory for this SQL. And it seems not a sql server configuration issue.
The environment is a cluster. Only this node is having weird problem. Once move the SQL instance to the other node, run the query locally it is very quick.
But move back to this weird node, it needs much longer time to complete.
We compare the settings for the 2 nodes, they are exactly same, only difference is there is a network driver release date difference. But since I run the query from server locally, the network shouldn't be a factor, is it right?
I am out of mind what could be the cause, and appreciate for any comment!! thanks!
February 26, 2012 at 10:21 pm
Evil Kraig F (2/22/2012)
*vivian* (2/22/2012)
Hi Brandie,The query I run returns a lot of data (50000), do you think it could because both server activites and client activites happen on this SQL Server so it become slower than the remote one?
Oh yes, definately. Watch Perfmon for Page Faults.
thank you! actually we have been monitored the server performance counters, all the cpu, memroy, Disk performance seems very good..
page life expectancy shows seems all data stays in the memory. Since the server has 96GB RAM and database is 60GB. we set max memory at 70GB.
February 26, 2012 at 11:14 pm
Hi All, thanks for all your comments! I just find something new:
I uses xevents to trace the query execution. I tested the same query from local SSMS(node 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
February 27, 2012 at 6:16 am
Which node has the newer network driver? The slow one or the fast one?
And what are your network card configurations for both servers?
February 27, 2012 at 12:44 pm
*vivian* (2/26/2012)
The environment is a cluster. Only this node is having weird problem. Once move the SQL instance to the other node, run the query locally it is very quick.
This is indicative of a hardware or configuration failure. Yes, I'd check the network card. However, network_io might be it's just taking forever to talk to your PC RDC'd SSMS too, since the only thing the server knows is that it's waiting to talk to the SSMS client. I'd be more inclined to get the RAM tested. My personal guess is you have a bad chip.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 27, 2012 at 6:44 pm
Evil Kraig F (2/27/2012)
*vivian* (2/26/2012)
The environment is a cluster. Only this node is having weird problem. Once move the SQL instance to the other node, run the query locally it is very quick.This is indicative of a hardware or configuration failure. Yes, I'd check the network card. However, network_io might be it's just taking forever to talk to your PC RDC'd SSMS too, since the only thing the server knows is that it's waiting to talk to the SSMS client. I'd be more inclined to get the RAM tested. My personal guess is you have a bad chip.
Thank you and Yes, when I include query client statistics, I can see most of the time is spent on 'client processing'. I think that indicates hardware issue as well, right? our server guys are checking the network configurations. and I am going to ask them help check cpu and RAM as well.
it's weird the cpu and ram usage both show very low all the time.
February 27, 2012 at 6:48 pm
Brandie Tarvin (2/27/2012)
Which node has the newer network driver? The slow one or the fast one?And what are your network card configurations for both servers?
Thank you! The slow node's driver release date is one day newer.
I am not sure of the network card configurations as am not farmilar with server configurations:hehe: however we have asked server vendor to help investigate the 2 drivers.
I'll post back if we find anything new. thanks again!
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply