<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2005
»
Administering
»
Want to reduce number of linked servers in...
Want to reduce number of linked servers in consolidation effort
Rate Topic
Display Mode
Topic Options
Author
Message
Marios Philippopoulos
Marios Philippopoulos
Posted Sunday, January 25, 2009 5:18 AM
SSCommitted
Group: General Forum Members
Last Login: Today @ 2:25 PM
Points: 1,825,
Visits: 3,484
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 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
Steve Jones - SSC Editor
Steve Jones - SSC Editor
Posted Sunday, January 25, 2009 9:53 AM
SSC-Dedicated
Group: Administrators
Last Login: Today @ 3:19 PM
Points: 31,526,
Visits: 13,863
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
Marios Philippopoulos
Marios Philippopoulos
Posted Monday, January 26, 2009 8:08 AM
SSCommitted
Group: General Forum Members
Last Login: Today @ 2:25 PM
Points: 1,825,
Visits: 3,484
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 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 »
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
