Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Want to reduce number of linked servers in consolidation effort Expand / Collapse
Author
Message
Posted Sunday, January 25, 2009 5:18 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, July 10, 2014 4:13 AM
Points: 1,860, Visits: 3,597
I want to measure the volume of traffic for all linked servers in our environment over a period of time, say a month.
This is as part of an effort to consolidate the amount of SQL instances currently in the environment.

For this I think the best way is to use a server-side trace, ie. script a Profiler trace and run on each server instance.

Linked servers with the highest amount of data (in MBytes) transferred between instances will point to likely instances/databases as best candidates for merging together into one instance.

Any suggestions for the events/columns I should use?

Has anyone attempted something like this before?


__________________________________________________________________________________

Turbocharge Your Database Maintenance With Service Broker: Part 2
Turbocharge Your Database Maintenance With Service Broker: Part 1
Real-Time Tracking of Tempdb Utilization Through Reporting Services
Monitoring Database Blocking Through SCOM 2007 Custom Rules and Alerts
Preparing for the Unthinkable - a Disaster/Recovery Implementation
Post #643146
Posted Sunday, January 25, 2009 9:53 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 5:53 PM
Points: 33,063, Visits: 15,179
I think you're on the right track here. I've never used linked servers much in environments so I'm not sure if this makes the best sense for consolidation. Things I've typically looked at for consolidation are load (CPU/Memory). Often I have low loads I've tried to put together.

I don't see anything in Profiler to help here. I might engage the network teams and set up network traces looking for volumes of TDS traffic between specific servers. That might help you track down the linked servers.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #643175
Posted Monday, January 26, 2009 8:08 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, July 10, 2014 4:13 AM
Points: 1,860, Visits: 3,597
Steve Jones - Editor (1/25/2009)
I think you're on the right track here. I've never used linked servers much in environments so I'm not sure if this makes the best sense for consolidation. Things I've typically looked at for consolidation are load (CPU/Memory). Often I have low loads I've tried to put together.

I don't see anything in Profiler to help here. I might engage the network teams and set up network traces looking for volumes of TDS traffic between specific servers. That might help you track down the linked servers.


Thank you for responding.

Yes, linked servers is only one of several aspects to consider when consolidating. It just makes sense to target those linked servers that constitute the largest bottlenecks in a system and try to eliminate them in a consolidation effort.

There was an article on this site some time ago that talks about monitoring linked-server performance with SQL Profiler.

Linked Server Optimization
http://www.sqlservercentral.com/articles/Linked+Servers/62246/

Network traces is a good idea, but it is probably not specific enough for my situation: I have multiple linked servers per SQL instance, and I want to know which ones are being used the most for transferring data.



__________________________________________________________________________________

Turbocharge Your Database Maintenance With Service Broker: Part 2
Turbocharge Your Database Maintenance With Service Broker: Part 1
Real-Time Tracking of Tempdb Utilization Through Reporting Services
Monitoring Database Blocking Through SCOM 2007 Custom Rules and Alerts
Preparing for the Unthinkable - a Disaster/Recovery Implementation
Post #643446
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse