How to improve performance with a join between 2 table from 2 SQL Servers

I am making a ASP.NET web application that involves 2 SQL Server(A & B).

I created a view in SQL server A pointing to the table in SQL Server B. I found out my application will run REALLY slow when accessing such a view. so I try to avoid using them. But in the case of 2 table joining from 2 different SQL Servers, I have no choice.

Can anyone help me with this?

Thanks!

No you don't need a View which is a query rewrite but you need the OpenRowset function. Try the link below for more.

http://msdn2.microsoft.com/en-us/library/ms190312.aspx

|||that is exactly how I create my view(I used openrowset to create it). So what you suggest is just same thing as what I am doing. It is so slow, I don't know how to improve it.|||

Can anybody have any suggestion except using "OpenRowset" function? Because it is slow and it is what I am using.

I found out that the Collate Character set of the 2 SQL Server is different, will this affect the performance? If so, how could I change it?

Thanks!

|||

When you join between 2 seperate servers, it is possible that all of the data from one servers table is moving to the other server. Possibly even data that is not ultimately required by your join. This is what i would suspect as the cause of the performance issue.

Ive seen similar issue with Access applications ive supported when the Access db was joining a local Access table to a linked sql table. It usually worked ok if one of the tables was very small, but if both tables were large, the application would virtually grind to a halt.

My solution to speed it up was to prefetch exactly the data i needed from the remote server into a temp table in my local server. Then i would join to the temp table.

If i couldnt construct a query to get exactly what i needed, i would added as many constraints as possible to try to eliminate as much unnecessary data as possible from being pulled from the remote server.

how to improve data transfert througput?

Hi,
How can I improve the communication and the data transfert betwee n 2
servers across a 1ghz LAN?
I have a data warehouse on a server and my OLAP cubes on a second server.
the data transfert never takes more then 5% of the 1gbps bandwidth
My SQL query is optimized but I have 3 tables linked
my fact table contains 40 millions of rows and its a partitioned table.
my database server has 4 proc / 4Gb ram
during the execution of the select statement my server suffer CXPACKET wait
types. I know I have an hard drive issue on the server (its a SAN).
only my tempdb database has been moved to a local non raid drive to share
the performance.
my source database is in readonly mode.
I know that I can reach more then 30% of usage of the bandwidth between the
2 servers.
but there is anything to do?
any option to change? (in sql server and/or the olap server) to improve
this.
thanks for your comments.
jerome."Jéjé" <willgart@.AAAhotmailBBB.com> wrote in message
news:uK0Bb2BoFHA.3960@.TK2MSFTNGP12.phx.gbl...
> Hi,
> How can I improve the communication and the data transfert betwee n 2
> servers across a 1ghz LAN?
> I have a data warehouse on a server and my OLAP cubes on a second server.
> the data transfert never takes more then 5% of the 1gbps bandwidth
> My SQL query is optimized but I have 3 tables linked
> my fact table contains 40 millions of rows and its a partitioned table.
> my database server has 4 proc / 4Gb ram
> during the execution of the select statement my server suffer CXPACKET
> wait types. I know I have an hard drive issue on the server (its a SAN).
> only my tempdb database has been moved to a local non raid drive to share
> the performance.
> my source database is in readonly mode.
> I know that I can reach more then 30% of usage of the bandwidth between
> the 2 servers.
> but there is anything to do?
> any option to change? (in sql server and/or the olap server) to improve
> this.
>
Which server is performing the join? If it's the OLAP server, consider
pushing the join to the source server with OPENQUERY or a remote view.
David|||the join is a simple select * from table1, table2 where table1.key =table2.key
so the query is executed in the SQL Server database on the database server.
OLAP Server = Analysis Services
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:ub1yxHCoFHA.2152@.TK2MSFTNGP14.phx.gbl...
> "Jéjé" <willgart@.AAAhotmailBBB.com> wrote in message
> news:uK0Bb2BoFHA.3960@.TK2MSFTNGP12.phx.gbl...
>> Hi,
>> How can I improve the communication and the data transfert betwee n 2
>> servers across a 1ghz LAN?
>> I have a data warehouse on a server and my OLAP cubes on a second server.
>> the data transfert never takes more then 5% of the 1gbps bandwidth
>> My SQL query is optimized but I have 3 tables linked
>> my fact table contains 40 millions of rows and its a partitioned table.
>> my database server has 4 proc / 4Gb ram
>> during the execution of the select statement my server suffer CXPACKET
>> wait types. I know I have an hard drive issue on the server (its a SAN).
>> only my tempdb database has been moved to a local non raid drive to share
>> the performance.
>> my source database is in readonly mode.
>> I know that I can reach more then 30% of usage of the bandwidth between
>> the 2 servers.
>> but there is anything to do?
>> any option to change? (in sql server and/or the olap server) to improve
>> this.
> Which server is performing the join? If it's the OLAP server, consider
> pushing the join to the source server with OPENQUERY or a remote view.
> David
>|||You might also take a look at the IO throughput which is likely the
performance bottleneck...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jéjé" <willgart@.AAAhotmailBBB.com> wrote in message
news:uK0Bb2BoFHA.3960@.TK2MSFTNGP12.phx.gbl...
> Hi,
> How can I improve the communication and the data transfert betwee n 2
> servers across a 1ghz LAN?
> I have a data warehouse on a server and my OLAP cubes on a second server.
> the data transfert never takes more then 5% of the 1gbps bandwidth
> My SQL query is optimized but I have 3 tables linked
> my fact table contains 40 millions of rows and its a partitioned table.
> my database server has 4 proc / 4Gb ram
> during the execution of the select statement my server suffer CXPACKET
> wait types. I know I have an hard drive issue on the server (its a SAN).
> only my tempdb database has been moved to a local non raid drive to share
> the performance.
> my source database is in readonly mode.
> I know that I can reach more then 30% of usage of the bandwidth between
> the 2 servers.
> but there is anything to do?
> any option to change? (in sql server and/or the olap server) to improve
> this.
> thanks for your comments.
> jerome.
>|||I know I have issue here, and I can't do anything.
I'm on a SAN.
the IT Team in charge of the SAN has not configured the system like we have
recommanded, and also there is performance issue not solved.
And we don't know if the IT team can change anything in the configuration.
I think our server is connected to the SAN through a shared system
instead-of having a dedicated communication, set of disks and controllers.
The problem appear when I read & write simultaneously. an older server from
another client perform better than this new server, but simple requests are
better on the new server (like create index or select statement)
Due to the lack of support and knowledge from this team, I have to find
other solutions.
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:O2zCCIDoFHA.3960@.TK2MSFTNGP12.phx.gbl...
> You might also take a look at the IO throughput which is likely the
> performance bottleneck...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Jéjé" <willgart@.AAAhotmailBBB.com> wrote in message
> news:uK0Bb2BoFHA.3960@.TK2MSFTNGP12.phx.gbl...
>> Hi,
>> How can I improve the communication and the data transfert betwee n 2
>> servers across a 1ghz LAN?
>> I have a data warehouse on a server and my OLAP cubes on a second server.
>> the data transfert never takes more then 5% of the 1gbps bandwidth
>> My SQL query is optimized but I have 3 tables linked
>> my fact table contains 40 millions of rows and its a partitioned table.
>> my database server has 4 proc / 4Gb ram
>> during the execution of the select statement my server suffer CXPACKET
>> wait types. I know I have an hard drive issue on the server (its a SAN).
>> only my tempdb database has been moved to a local non raid drive to share
>> the performance.
>> my source database is in readonly mode.
>> I know that I can reach more then 30% of usage of the bandwidth between
>> the 2 servers.
>> but there is anything to do?
>> any option to change? (in sql server and/or the olap server) to improve
>> this.
>> thanks for your comments.
>> jerome.
>>
>

how to import the database data from backup file ?

Hello,All:

I have two sql 2000 servers,one for production and the other is for backup server,I used the sql agent to create bakup daily in Sql server Enterprise Manager.

Now I want to import the backup data(generated by production server),I don't know how to do it.

Is it possible to do it ? anyone can give me a soluation ?

thanks in advanced!

->Open Enterprise manager of SQL Server 2000

-> Select Your Server

-> right click on your database

-> All Tasks -> Restore Database

-> Select from Device

-> click the button Select Devices

-> in the new window select Disk option

-> click add button

-> select your backup file in file name option

-> ok

->ok

->ok

How to identify unused databases

Hi Folks,
client company has around 25 sql servers .
Each server has got 5 to 10 databases.
Some of the databases are old.
We don't know whether any applications using old databases or not.
Is there any way we can find the unused databases in past 3 months or past 1
year
using some Lastupdatedatetime or last action on sql server system tables
using T-SQL scripts.
If no application is accessing the database and no actions performed on
that database from past 1 year then we need to delete that databases.
How to identify unused databases?
Any kind of help is greatly appreciated.
Thanks
KumarKumar wrote:
> Hi Folks,
> client company has around 25 sql servers .
> Each server has got 5 to 10 databases.
> Some of the databases are old.
> We don't know whether any applications using old databases or not.
> Is there any way we can find the unused databases in past 3 months or
> past 1 year
> using some Lastupdatedatetime or last action on sql server system
> tables using T-SQL scripts.
> If no application is accessing the database and no actions performed
> on that database from past 1 year then we need to delete that
> databases.
> How to identify unused databases?
> Any kind of help is greatly appreciated.
> Thanks
> Kumar
You can create a trace on the server. Include only the SQL:StmtStarting
and RPC:Starting events. Include only the minimum number of columns:
EventClass, SPID, DatabaseID. You could exclude system databases like
master, msdb, and tempdb.
To cut down the rows a little, you could use SQL:BatchStarting, but if a
batch has "USE" statements and accesses more than one database, the
DatabaseID will only refer to the database that was used at execution
time.
If you let the trace run for a couple of hours (or a day or two), you
should have a pretty good picture of what databases were accessed on
each server.
Best to do this using a server-side trace which you can script from
Profiler using the File - Script Trace menu option and stopping manually
using sp_trace_setstatus.
David Gugick
Quest Software
www.imceda.com
www.quest.com