Cross Server Query Performance;

Question

Cross Server Query Performance;

jr81

SSC-Addicted

Points: 448
More actions
March 4, 2010 at 8:13 pm

#217240

Hi all, I have two servers serverA and serverB. Business users are running adhoc queries from client machines, they are joining two tables between these servers. The table they are joining from serverA is about 85GB and the tables in ServerB are small. They have been complaining of poor performance. Both the tables are properly indexed (as far as I know). I wanted an opinion on how to improve the performance of cross server joins.
Both servers are sql 2000. They are both dell 2950, 4 cpus and 8 Gb of memory(i know its not much) and database and tempdb is on RAID 10.
Any help is appreciated.
Thanks

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

mike 84768 Right there with Babe Points: 792 More actions · Answer 1

I have no experience with this but here are my thoughts..not in any particular order...

First...network bandwidth. You have not stated whether the servers are in the same physical location or what the NIC/network speed is. Hopefully the internet is not involved. If you are running SQL2000 you may also be on 100MB NICs/network. If the machines are in the same physical proximity consider installing 1GB NICs and a crossover cable between them to construct a private LAN.

Second...I would guess that it would be faster if the clients connect to the server with the larger table then construct the query to pull the smallest set of rows possible across the network, possibly into a temp table if SQL isn't already doing something similar, then execute the join locally. If you can take the disk Ops out of the equation it will be that much faster.

Third...consider replicating the small tables on the server with the larger DB.

Leo Leong SSCommitted Points: 1615 More actions · Answer 2

normally, I will pull the smaller dataset (in your example, ServerB) into a temp table (please remember to create the structure of the temp table first) in ServerA.

then only I join the data on ServerA with this temp table. of course, your client should be connecting ServerA to get the final result.

mike 84768 Right there with Babe Points: 792 More actions · Answer 3

It just occurred to me that you should use a CTE...select the rows from tha small tables. This might keep everything in memory..not sure.

jr81 SSC-Addicted Points: 448 More actions · Answer 4

I thought CTE's are not a feature in sql 2000?.The servers are in the same physical location. I will tell the LAN administrator about 1 GB NIC's.

Thanks

Lowell SSC Guru Points: 323518 More actions · Answer 5

jayanth linked servers can be notoriously slow, and there's a couple of reasons.

if you are joining two tables, i.e.

SELECT *

FROM Server1.Db.dbo.Customers X

INNER JOIN Server2.Db.dbo.Customers Y--85 million rows

ON X.CustomerId = Y.CustomerID

in that case, all 85 million rows get copied over to server1's tempdb, and then the data is merged. 85M rows takes time to transfer to disk, no matter the quality of the subsystem.

instead, you want to always limit the records before the join, which might duplicate the data in your WHERE statement:

SELECT *

FROM Server1.Db.dbo.Customers X

INNER JOIN (SELECT * FROM Server2.Db.dbo.Customers WHERE CUSTOMERID IN(101,102,115) Y

--fewer rows million rows

ON X.CustomerId = Y.CustomerID

WHERE CUSTOMERID IN(101,102,115)

sometimes openquery is another option as well:

openquery against a linked server is faster for a linked server vs a query on your server using 4 partnaming conventions;

for example,

select * from LinkedServer.Databasename.dbo.MillionRowTable where something=somethingelse

vs

SELECT *

FROM OPENQUERY(LinkedServer, 'select * from MillionRowTable where something=somethingelse');

the reason: the first query gets the million rows locally into temp, then does the filtering, where the openquery does the work on the linkedserver, and returns just the results.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

jr81 SSC-Addicted Points: 448 More actions · Answer 6

Ok , I Will try the open query but if thats slow is there an option in SQL 2008/R2 where I can put the database(with big talbe) on a shared disk between two or more servers and query the data?

Thanks

Lowell SSC Guru Points: 323518 More actions · Answer 7

how up to date does the data need to be between servers? does it really need to be up-to-the-second as a linked server?

if it can be a day or so difference between "live" 85 M server, you could set up a daily MERGE with SSIS or a job to grab the differences to the other server once a day or something;

with a local copy of the data, your performance would be better.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 8

jayanth-463157 (3/4/2010)
Hi all, I have two servers serverA and serverB. Business users are running adhoc queries from client machines, they are joining two tables between these servers. The table they are joining from serverA is about 85GB and the tables in ServerB are small. They have been complaining of poor performance. Both the tables are properly indexed (as far as I know). I wanted an opinion on how to improve the performance of cross server joins.
Both servers are sql 2000. They are both dell 2950, 4 cpus and 8 Gb of memory(i know its not much) and database and tempdb is on RAID 10.
Any help is appreciated.
Thanks

Step 1... post the query.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)