January 25, 2009 at 5:18 am
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[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
January 25, 2009 at 9:53 am
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.
January 26, 2009 at 8:08 am
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]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply