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[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]