Sudden slowdown in communication between two serve

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Sudden slowdown in communication between two serve

Author

Topic

molap
Starting Member

8 Posts

Posted - 2010-04-06 : 13:54:43

Our company has 8 SQL servers. All of them are communicating with each other via linked server settings.

All of a sudden the remote querries from ServerA to ServerB are taking 12 minutes instead of 6 seconds.

I have done a few tests and tweaks but I can't figure out how to troubleshoot further. Here are a few factoids:

On ServerA
select * from serverB.database1.dbo.tablename1 takes 12 minutes

On Server B
select * from database1.dbo.tablename1 takes 6 seconds

On Server C
select * from serverB.database1.dbo.tablename1 takes 6 seconds

I have checked linked servers, sysservers and I have all of the machines in there. I ran sp_setnetname to have entry for ServerB, I have looked at network protocols to have TCPIP on top.

At this point I don't know how to troubleshoot further to actuall know why is the statement jumping through so many hoops to display 6000 rows in 12 minutes.

I would be grateful for any pointers. Thank You.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-06 : 14:55:01

Check the execution plan. If it is different than what you expect, recompile the stored procedure or clear the procedure cache.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-06 : 14:59:43

What happens if, on Server A, you do:
select * from serverC.database1.dbo.tablename1

and then do the same on Server B and Server C? If Server A is much slower than the other 2, then it looks like a problem with Server A. If so, then I'd have a look at perfmon on server A while the query is running, see if anything spikes.

There are 10 types of people in the world, those that understand binary, and those that don't.

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-06 : 15:04:10

Sorry, forgot to add, if Server A is only slow when Accessing the other servers, make sure you look at Network Interfaces/Output Queue Length in Perfmon. This should be very low. A number > 0 here indicates the number of packets waiting to be passed through the network interface.

There are 10 types of people in the world, those that understand binary, and those that don't.

molap
Starting Member

8 Posts

Posted - 2010-04-07 : 08:20:42

ServerA is only slow when accessing ServerB

Any other combination is fast.

molap
Starting Member

8 Posts

Posted - 2010-04-07 : 08:48:14

I used tried looking at Network Interfaces Output Queue Length and it did not spike a bit. I was at 0 the whole 12 minutes that the query ran.

molap
Starting Member

8 Posts

Posted - 2010-04-07 : 09:08:14

I just want to add that even when using SQL Server Management Studio and connecting ServerB for management and browsing of tables the whole interface becomes sluggish.

molap
Starting Member

8 Posts

Posted - 2010-04-07 : 09:15:16

I have a question. When accessing ServerB in SQL management studio while logged onto ServerA what account is being used for this? Is it the service account in SQL Server or the service account in SQL Server Agent or it is the login and password in the connection dialog box of the SQL Server?

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-07 : 09:22:40

quote:
Originally posted by molap

ServerA is only slow when accessing ServerB

How about when ServerB accesses ServerA?

There are 10 types of people in the world, those that understand binary, and those that don't.

molap
Starting Member

8 Posts

Posted - 2010-04-07 : 09:33:44

Lightning fast. a 27000 records table was retrieved in a second. This table is larger and wider than table on ServerB.

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-07 : 09:49:27

quote:
Originally posted by molap

I have a question. When accessing ServerB in SQL management studio while logged onto ServerA what account is being used for this? Is it the service account in SQL Server or the service account in SQL Server Agent or it is the login and password in the connection dialog box of the SQL Server?

It depends on the configuration options for the linked server.

If you open SSMS on ServerA, connect it to ServerB and run a query, how does that perform compared to opening SSMS on ServerB, connecting it to ServerA and running a similar query? You said it was "sluggish". Are you noticing a similar level of "sluggishness"?

I'm trying to determine if it's the connection from A to B, or the linked server setup.

Also, On serverA, does the link to B and C use the same settings?

There are 10 types of people in the world, those that understand binary, and those that don't.

molap
Starting Member

8 Posts

Posted - 2010-04-07 : 09:56:00

quote:
If you open SSMS on ServerA, connect it to ServerB and run a query, how does that perform

5 seconds

quote:

compared to opening SSMS on ServerB, connecting it to ServerA and running a similar query?

1 second

quote:
You said it was "sluggish". Are you noticing a similar level of "sluggishness"?

On ServerA bringing up the query window to ServerB took longer than usual but the actual query was fast.

quote:
I'm trying to determine if it's the connection from A to B, or the linked server setup.

The linked server is setup with Starndard security logins which have DBA rights to all the databases on the server. The loginname and password are the same on both servers.

quote:

Also, On serverA, does the link to B and C use the same settings?

Yes all the servers use the same loginname and password in their linked server settings.

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-07 : 10:18:52

Do the linked servers use the same providers?

There are 10 types of people in the world, those that understand binary, and those that don't.

molap
Starting Member

8 Posts

Posted - 2010-04-07 : 11:14:15

They all use : Microsoft OLE DB Provider for SQL Server

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2010-04-08 : 03:26:00

"all of a sudden"....who changed something? Things don't go bad themselves.

Call all heads together including the network and cable technicans.
Can you trace the path network messages via "ping" or a more serious debug tool?

Have you tried re-setting ServerA? Have you checked DNS names/IP address's? Is there a possible network conflict? Have the connection speeds of the NIC cards changed?

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-08 : 05:50:05

quote:
Originally posted by AndrewMurphy

Things don't go bad themselves.

They do sometimes. I've had a dodgy SAN NIC cause a similar problem to this. Although in that case, it cause all operations on the server to perform at about 5% of the norm. The point being, it happened due to a hardware failure, not because someone changed something.

There are 10 types of people in the world, those that understand binary, and those that don't.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources