March 14, 2006 at 6:01 am
I've got a standalone development server which queries a remote SQL Server (linked server) and returns the query results in a couple of seconds (57153 rows returned).
I'm trying the exact same query on the production server (active-passive cluster) and it's taking me ages to get results (9 minutes and it's still returning rows).
Linked servers both appear to be configured the same way on the local servers. Both servers are SP4 on Windows 2003 with roughly the same set of security patches and fixes. Both local servers are on the same network segment and firewall policies are the same.
The query being used to test this is something like
SELECT ta1.account_no AS parent_account_no
,ta2.account_no
,ta1.date_inactive
FROM remoteserver.database.dbo.table ta1
, remoteserver.database.dbo.table ta2
WHERE ta1.account_no = ta2.parent_id
AND ta1.account_status = 0
Simple queries like SELECT COUNT (*) FROM remoteserver.database.dbo.table run instantly (on both local servers - clustered or not).
I'm thinking this may be a MSDTC issue. Are there any specific guidelines on where to start looking for potencial problems / ways to begin troubleshooting?
Thanks in advance
March 14, 2006 at 8:42 am
is the fast linked server the same as the one you setup in production?
is the load i production too heavy?
In my opinion you are performing the join locally. I would use openquery instead to perform such action:
select * from Openquery (remoteserver, '
select ta1.account_no AS parent_account_no
,ta2.account_no
,ta1.date_inactive
FROM database.dbo.table ta1 join database.dbo.table ta2 on ta1.account_no = ta2.parent_id
WHERE ta1.account_status = 0') qry
Cheers,
* Noel
March 14, 2006 at 12:05 pm
yes, I'm hitting the same remote linked server in both local servers. no load whatsoever.
however, you're right (and it makes sense, I oughta smack myself in the head for not noticing that): using openquery really boosts the response time of the query.
Anyway, I tried the original query (local join) on a different clustered server and it basically flew. So what can be wrong with this cluster configuration? Where should I begin looking at?
March 15, 2006 at 8:51 am
I think you should compare the execution plans in both scenarios to see if the difference is there. Also check network adapters and make sure they are all running at the same speed.
jg
March 15, 2006 at 10:57 am
Have U made sure that ALL settings are identical, including collation - here is a link that explains how a difference in collation settings between two machines can affect performance: http://qa.sqlservercentral.com/forums/shwmessage.aspx?forumid=9&messageid=265493
An excerpt: "When running distributed queries on a linked server, if the linked server has the same character set and sort order (collation) as the local SQL Server, then you can reduce overhead and boost performance if you set the SP_SERVEROPTION "collation compatible" option to true. What this setting does is tell SQL Server to assume that all columns and character sets on the remote server are compatible with the local server. This same option can also be turned on for a linked server using Enterprise Manager
If this option is not selected, then the distributed query being executed on the remote server must return the entire table to the local server in order for the WHERE clause to be applied. As you can imagine, this could potentially return a lot of unnecessary data over the network, slowing it down.
If the option is selected, (which is always recommended if the collations are the same on both servers), then the WHERE clause is applied on the remote server. This, of course, means that much less data is transmitted over the network, often greatly speeding up the distributed query"
HTH
maddog
March 15, 2006 at 10:59 am
Oops got wrong link - here is correct one:
http://www.sql-server-performance.com/linked_server.asp
maddog
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply