Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Want to reduce number of linked servers in consolidation effort


Want to reduce number of linked servers in consolidation effort

Author
Message
Marios Philippopoulos
Marios Philippopoulos
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1954 Visits: 3740
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?

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables
Persisting SQL Server Index-Usage Statistics with MERGE
Turbocharge Your Database Maintenance With Service Broker: Part 2
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36040 Visits: 18734
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
My Blog: www.voiceofthedba.com
Marios Philippopoulos
Marios Philippopoulos
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1954 Visits: 3740
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.

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables
Persisting SQL Server Index-Usage Statistics with MERGE
Turbocharge Your Database Maintenance With Service Broker: Part 2
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search