Remote SQL server fast access

Hi gurus,
I have sql server 2000 located in Europe which I am
accessing from Mumbai, through wan n/w but due to long
distance it's performance is very poor which tends the
user to wait for execution to complete ..I just come to
know that SQL client software (may be from Microsoft or
third party) will solve my problem. Please guide me in
this regard & let me know the software products available
for the same purpose, may be other than SQL client.So, I
can buy it.
Best Regards,
MANISH JADHAVTry using Remote Desktop or Terminal Server session instead.
This way only the keystrokes go across.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Remote Source and Destination Performance

Given the following scenario, what kind of performance should be expected in transferring about half a million rows? We are seeing about a 9 minute execution time. Is this reasonable for about 460,000 records moving from source to target, with 3 inner joins from the source?

Source: Server A.OLTPDB

Target: ServerA.DataMartDB

Server A is running SQL Server 2000. SSIS is running on a different machine, Server B.

The reason for this is that we are distributing the SSIS package for use with a BI product built on SSAS 2005 and the requirements are such that the client could very well have the source OLTP database on a different physical machine than the data mart.

My understanding is therefore that:

1. SSIS will do all of the heavy lifting on Server B.

2. Even though OLTPDB and DataMartDB are on the same server, it is expensive for Server B to pull the records from Server A and then send them back to Server B, but with SSIS being on a different machine, this is inevitable.

3. In the OLE DB Source Adapter, specifying table or view has the effect of an OPEN QUERY command, whereas a SQL command with straight SQL will be executed on Server B and the former would be somewhat more performant.

Can you guys validate/dispel these assumptions?

TIA,

Rick

First, only you can determine performance stats... You'll have to test within your environment.

With that said though, OPEN QUERY will almost always be slower than defining your own SQL in the OLE DB source. Even better will be to explicitly define the columns you want returned in that SQL. (Instead of doing a "select * from...").

Phil|||

Phil, thanks. I understand your point about baselining and benchmarking and all environments and scenarios being unique, but would you agree that pulling half a million records from a remote SQL box is not trivial?

I am just, at a high level, trying to assert that working with that large a dataset between two remote SQL boxes is expensive and that the performance is what it is. If on the other hand, folks have done something like this and see mere seconds response- then I know I am doing something very wrong.

Thanks,

Rick

|||

Assert.True wrote:

Given the following scenario, what kind of performance should be expected in transferring about half a million rows? We are seeing about a 9 minute execution time. Is this reasonable for about 460,000 records moving from source to target, with 3 inner joins from the source?

Impossible to say. There are so many variables in play here. 9 minutes seems like a very long time but it COULD be the query, it COULD be network latency, it COULD be any number of things. If you want some tips on performance tuning then follow this link:

Donald Farmer's Technet webcast
(http://blogs.conchango.com/jamiethomson/archive/2006/06/14/4076.aspx)

Assert.True wrote:

Source: Server A.OLTPDB

Target: ServerA.DataMartDB

Server A is running SQL Server 2000. SSIS is running on a different machine, Server B.

The reason for this is that we are distributing the SSIS package for use with a BI product built on SSAS 2005 and the requirements are such that the client could very well have the source OLTP database on a different physical machine than the data mart.

My understanding is therefore that:

1. SSIS will do all of the heavy lifting on Server B.

Explain "heavy lifting". The query to get the data gets executed on serverA. if you are doing any manipulation of the data in the pipeline then that will occur on serverB. The insertion of the data will occur on serverA. Any of these things COULD be bottlenecks - you need to find which of them IS the bottleneck.

Assert.True wrote:

2. Even though OLTPDB and DataMartDB are on the same server, it is expensive for Server B to pull the records from Server A and then send them back to Server B, but with SSIS being on a different machine, this is inevitable.

It seems that way. Your network becomes a variable here.

Assert.True wrote:

3. In the OLE DB Source Adapter, specifying table or view has the effect of an OPEN QUERY command, whereas a SQL command with straight SQL will be executed on Server B and the former would be somewhat more performant.

Some info here that may help:

SELECT *... or select from a dropdown in an OLE DB Source component?
(http://blogs.conchango.com/jamiethomson/archive/2006/02/21/SSIS_3A00_-SELECT-_2A002E002E002E00_-or-select-from-a-dropdown-in-an-OLE-DB-Source-component_3F00_.aspx)

Assert.True wrote:

Can you guys validate/dispel these assumptions?

Hope that has helped somewhat!

-Jamie

|||

Jamie/Phil- thanks for your feedback. It does help and I am confident that the bottleneck has been isolated to a single query for which I can reproduce the timing by running from local Query Analyzer (which repros the effect of having to pull all those records from "Server A").

Having changed all Merge Joins to Lookups (which did help), there is no question that pulling half a million rows (to perform transformation) is really expensive and I guess it makes sense- would you try to do that in a web browser, for instance? No way (unless you want to crash it).

So, I think the options include limiting the result set or just dealing with the 10 minute +/- duration for the ETL to run. I will also look into source indexes and I will watch Farmers webcast and if anything changes, I will report back.

Thanks again for the responses- especially on a weekend!

Rick

|||~500,000 rows (depending on row size) should be extremely trivial to process. The timings of which should be under one minute, easily. I'm always amazed at the thought that 1,000,000 rows is a large amount of data. I'll put it into perspective: First Data Resources (one of the major credit card processing companies in the world) processes so many transactions per minute, that if 500,000 rows took over 10 minutes to run, they'd be out of business -- they'd never be able to catch up.

With that said, the slow times are usually a factor of (and Jamie listed most of them already): network, local and remote hardware, row size, unique processing constraints (converting non-standard date formats to database date/time fields), whether or not you are logging each transaction, referential integrity checks, etc...

Have you looked at the execution plan for your query to see if adding indexes or something might help?|||

I'd like to add my 5 cents to this discussion. When you are trying to improve performance or find botlenecks you wnat to isolate and mesurre every piece involved. This paper offer a couple of very simple but helpful sugestions in the troubleshooting section. I hope you find it helpful.

|||Hi Rafael, there was no link provided (to the paper).|||

Assert.True wrote:

Hi Rafael, there was no link provided (to the paper).

There you go:

http://www.microsoft.com/technet/prodtechnol/sql/2005/ssisperf.mspx

Remote Query Performance

I have a query that when executed against the host server, it completes in 1
1
seconds. When I execute the same query against a linked server it takes 100
seconds to run. I have checked the execution plan for the linked server
query and it shows that 100% of the cost is the Remote Query. The resultset
is only 260 rows and 5 columns of data. Both servers are SQL2000 and Window
s
2000 OS.
Since both queries ran from query analyzer on my workstation, why is there
such a drastic difference in speed? Also, how do I fix the performance issu
e
for the remote query so that it runs in 15 seconds or less?Brandon Lunt wrote:
> I have a query that when executed against the host server, it completes in
11
> seconds. When I execute the same query against a linked server it takes 1
00
> seconds to run. I have checked the execution plan for the linked server
> query and it shows that 100% of the cost is the Remote Query. The results
et
> is only 260 rows and 5 columns of data. Both servers are SQL2000 and Wind
ows
> 2000 OS.
> Since both queries ran from query analyzer on my workstation, why is there
> such a drastic difference in speed? Also, how do I fix the performance is
sue
> for the remote query so that it runs in 15 seconds or less?
>
The resultset is 260 rows, but how many rows are in the base table?
What indexes are available? Such a query is going to be slower by
default simply because of the network, but often the query engine can't
determine an "optimal" execution plan for a remote query, and will end
up pulling an entire table across the network, and then filtering the
results on the local side.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||The base table has aprox 3 Million records. The servers are communicating o
n
a Gigabit network. I am connected to the network with a 100 Megabit
connection. Why would SQL pull the table over the network if the query plan
shows that it was executed remotely?
"Tracy McKibben" wrote:

> Brandon Lunt wrote:
> The resultset is 260 rows, but how many rows are in the base table?
> What indexes are available? Such a query is going to be slower by
> default simply because of the network, but often the query engine can't
> determine an "optimal" execution plan for a remote query, and will end
> up pulling an entire table across the network, and then filtering the
> results on the local side.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||"Brandon Lunt" <BrandonLunt@.discussions.microsoft.com> wrote in message
news:5FE2384F-B2B0-407B-91F7-7A39ADFBC690@.microsoft.com...
>I have a query that when executed against the host server, it completes in
>11
> seconds. When I execute the same query against a linked server it takes
> 100
> seconds to run. I have checked the execution plan for the linked server
> query and it shows that 100% of the cost is the Remote Query. The
> resultset
> is only 260 rows and 5 columns of data. Both servers are SQL2000 and
> Windows
> 2000 OS.
> Since both queries ran from query analyzer on my workstation, why is there
> such a drastic difference in speed? Also, how do I fix the performance
> issue
> for the remote query so that it runs in 15 seconds or less?
>
Can you run it through OPENQUERY? This would pass the query text to the
linked server, and just return you the results.
David|||Brandon Lunt wrote:
> The base table has aprox 3 Million records. The servers are communicating
on
> a Gigabit network. I am connected to the network with a 100 Megabit
> connection. Why would SQL pull the table over the network if the query pl
an
> shows that it was executed remotely?
>
The remote "query" is simply indicating that "something" was done on the
remote side. As David suggested, try using OPENQUERY, that will
guarantee that the query is executed on the remote side.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I used the following syntax to run the openquery
select * from openquery([linkedserver], 'query string')
same results, 1:37 elapsed time.
"David Browne" wrote:

> "Brandon Lunt" <BrandonLunt@.discussions.microsoft.com> wrote in message
> news:5FE2384F-B2B0-407B-91F7-7A39ADFBC690@.microsoft.com...
> Can you run it through OPENQUERY? This would pass the query text to the
> linked server, and just return you the results.
> David
>
>

Remote Query Performance

I have a query that when executed against the host server, it completes in 11
seconds. When I execute the same query against a linked server it takes 100
seconds to run. I have checked the execution plan for the linked server
query and it shows that 100% of the cost is the Remote Query. The resultset
is only 260 rows and 5 columns of data. Both servers are SQL2000 and Windows
2000 OS.
Since both queries ran from query analyzer on my workstation, why is there
such a drastic difference in speed? Also, how do I fix the performance issue
for the remote query so that it runs in 15 seconds or less?First, why are you using linked servers. In most cases this is not necessary
with RS. You can have multiple datasets against multiple data sources.
SQL 2000 can be very very bad with linked servers. SQL 2005 is much better.
I learned this while working with loading a datamart. If you must use linked
servers then you need to be using Openquery to use it. Do not use the 4 part
naming.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Brandon Lunt" <BrandonLunt@.discussions.microsoft.com> wrote in message
news:D9838B81-C5D7-4DA9-85AE-3E2AF317F391@.microsoft.com...
>I have a query that when executed against the host server, it completes in
>11
> seconds. When I execute the same query against a linked server it takes
> 100
> seconds to run. I have checked the execution plan for the linked server
> query and it shows that 100% of the cost is the Remote Query. The
> resultset
> is only 260 rows and 5 columns of data. Both servers are SQL2000 and
> Windows
> 2000 OS.
> Since both queries ran from query analyzer on my workstation, why is there
> such a drastic difference in speed? Also, how do I fix the performance
> issue
> for the remote query so that it runs in 15 seconds or less?|||The report I am producing combines data from 3 different servers. I
typically use linked servers so I don't have to put usernames and passwords
into my query strings (not all users have access to all databases so I have
to use a different account). I have usually had good results with Linked
servers (slight performance hit but never this bad). The other linked query
(to the 3rd server) runs in about 2 seconds and returns approximately the
same number or results.
My confusion I guess is why does it not run the same as a query analyzer
client connecting to the server?
"Bruce L-C [MVP]" wrote:
> First, why are you using linked servers. In most cases this is not necessary
> with RS. You can have multiple datasets against multiple data sources.
> SQL 2000 can be very very bad with linked servers. SQL 2005 is much better.
> I learned this while working with loading a datamart. If you must use linked
> servers then you need to be using Openquery to use it. Do not use the 4 part
> naming.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Brandon Lunt" <BrandonLunt@.discussions.microsoft.com> wrote in message
> news:D9838B81-C5D7-4DA9-85AE-3E2AF317F391@.microsoft.com...
> >I have a query that when executed against the host server, it completes in
> >11
> > seconds. When I execute the same query against a linked server it takes
> > 100
> > seconds to run. I have checked the execution plan for the linked server
> > query and it shows that 100% of the cost is the Remote Query. The
> > resultset
> > is only 260 rows and 5 columns of data. Both servers are SQL2000 and
> > Windows
> > 2000 OS.
> >
> > Since both queries ran from query analyzer on my workstation, why is there
> > such a drastic difference in speed? Also, how do I fix the performance
> > issue
> > for the remote query so that it runs in 15 seconds or less?
>
>|||Are you doing this in a stored procedure?
If not, I have seen people post about issues where the query plan created
when a query is executed from RS is different than from query analyzer. As I
said, I haven't seen this but I have seen posts about that.
If you are using a stored procedure then that would not be an issue. If not,
try moving this to a stored procedure and see if that helps.
I have thought of one other thing. Depending on the parameterization, I have
seen in 2000 based on the parameters and the where clause where SQL Server
will decide to bring over all the data from the remote table and process it
locally on the server rather than having the query executed remotely and
bringing back the result. Based on 11 seconds when hitting the server
directly going to 100 seconds, I bet that is what is happening. If you use
openquery this will not occur. With 4 part naming you have to really be
careful.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Brandon Lunt" <BrandonLunt@.discussions.microsoft.com> wrote in message
news:4CCA53E6-1A11-48C0-A1F8-E60B95C0CC03@.microsoft.com...
> The report I am producing combines data from 3 different servers. I
> typically use linked servers so I don't have to put usernames and
> passwords
> into my query strings (not all users have access to all databases so I
> have
> to use a different account). I have usually had good results with Linked
> servers (slight performance hit but never this bad). The other linked
> query
> (to the 3rd server) runs in about 2 seconds and returns approximately the
> same number or results.
> My confusion I guess is why does it not run the same as a query analyzer
> client connecting to the server?
> "Bruce L-C [MVP]" wrote:
>> First, why are you using linked servers. In most cases this is not
>> necessary
>> with RS. You can have multiple datasets against multiple data sources.
>> SQL 2000 can be very very bad with linked servers. SQL 2005 is much
>> better.
>> I learned this while working with loading a datamart. If you must use
>> linked
>> servers then you need to be using Openquery to use it. Do not use the 4
>> part
>> naming.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Brandon Lunt" <BrandonLunt@.discussions.microsoft.com> wrote in message
>> news:D9838B81-C5D7-4DA9-85AE-3E2AF317F391@.microsoft.com...
>> >I have a query that when executed against the host server, it completes
>> >in
>> >11
>> > seconds. When I execute the same query against a linked server it
>> > takes
>> > 100
>> > seconds to run. I have checked the execution plan for the linked
>> > server
>> > query and it shows that 100% of the cost is the Remote Query. The
>> > resultset
>> > is only 260 rows and 5 columns of data. Both servers are SQL2000 and
>> > Windows
>> > 2000 OS.
>> >
>> > Since both queries ran from query analyzer on my workstation, why is
>> > there
>> > such a drastic difference in speed? Also, how do I fix the performance
>> > issue
>> > for the remote query so that it runs in 15 seconds or less?
>>|||Currently this is in query form. I tried the openquery and got the same
results as the 4 part linked query. I also tried the openrowset and the time
came down to 57 seconds, but nowhere near the 10-15 seconds I would expect.
Also, the problem with openquery is that I need to pass parameters used by
the query. I didn't see a way to get those dynamically into the query.
I went ahead and created the sp and executed that against the linked server
and it ran in 50 seconds. Better, but still not what I was expecting.
"Bruce L-C [MVP]" wrote:
> Are you doing this in a stored procedure?
> If not, I have seen people post about issues where the query plan created
> when a query is executed from RS is different than from query analyzer. As I
> said, I haven't seen this but I have seen posts about that.
> If you are using a stored procedure then that would not be an issue. If not,
> try moving this to a stored procedure and see if that helps.
> I have thought of one other thing. Depending on the parameterization, I have
> seen in 2000 based on the parameters and the where clause where SQL Server
> will decide to bring over all the data from the remote table and process it
> locally on the server rather than having the query executed remotely and
> bringing back the result. Based on 11 seconds when hitting the server
> directly going to 100 seconds, I bet that is what is happening. If you use
> openquery this will not occur. With 4 part naming you have to really be
> careful.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Brandon Lunt" <BrandonLunt@.discussions.microsoft.com> wrote in message
> news:4CCA53E6-1A11-48C0-A1F8-E60B95C0CC03@.microsoft.com...
> > The report I am producing combines data from 3 different servers. I
> > typically use linked servers so I don't have to put usernames and
> > passwords
> > into my query strings (not all users have access to all databases so I
> > have
> > to use a different account). I have usually had good results with Linked
> > servers (slight performance hit but never this bad). The other linked
> > query
> > (to the 3rd server) runs in about 2 seconds and returns approximately the
> > same number or results.
> >
> > My confusion I guess is why does it not run the same as a query analyzer
> > client connecting to the server?
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> First, why are you using linked servers. In most cases this is not
> >> necessary
> >> with RS. You can have multiple datasets against multiple data sources.
> >>
> >> SQL 2000 can be very very bad with linked servers. SQL 2005 is much
> >> better.
> >> I learned this while working with loading a datamart. If you must use
> >> linked
> >> servers then you need to be using Openquery to use it. Do not use the 4
> >> part
> >> naming.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "Brandon Lunt" <BrandonLunt@.discussions.microsoft.com> wrote in message
> >> news:D9838B81-C5D7-4DA9-85AE-3E2AF317F391@.microsoft.com...
> >> >I have a query that when executed against the host server, it completes
> >> >in
> >> >11
> >> > seconds. When I execute the same query against a linked server it
> >> > takes
> >> > 100
> >> > seconds to run. I have checked the execution plan for the linked
> >> > server
> >> > query and it shows that 100% of the cost is the Remote Query. The
> >> > resultset
> >> > is only 260 rows and 5 columns of data. Both servers are SQL2000 and
> >> > Windows
> >> > 2000 OS.
> >> >
> >> > Since both queries ran from query analyzer on my workstation, why is
> >> > there
> >> > such a drastic difference in speed? Also, how do I fix the performance
> >> > issue
> >> > for the remote query so that it runs in 15 seconds or less?
> >>
> >>
> >>
>
>|||The only way to get a parameter to openquery (that I know of) is to
dynamically create the sql string. You end up having to do lots of messing
with single quotes.
In your stored procedure, are you able to insert into a temp table the
results from each individual query and then join the temp tables?
Also, just so you can see how it is done, here is a an example of
dynamically
select @.SQL = 'insert ' + @.TABLENAME + ' select * from
openquery(linkedservername,''' + 'SELECT * from ' + @.TABLENAME + ' where ' +
@.SYNCDATEFIELD + '> '' + convert(varchar(30),@.STARTDATE,9) + '' and '
+ @.SYNCDATEFIELD +' < '' + convert(varchar(30),@.ENDDATE,9) + '')'
execute (@.SQL)
Note all the single quotes mess. Not too friendly but it is the fastest way
to work with linked tables. In the above I am inserting into a real table
but you could easily have a temp table created that you insert into.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Brandon Lunt" <BrandonLunt@.discussions.microsoft.com> wrote in message
news:EDE9A4A4-0992-4B9B-ACA3-2E6A0FB1645B@.microsoft.com...
> Currently this is in query form. I tried the openquery and got the same
> results as the 4 part linked query. I also tried the openrowset and the
> time
> came down to 57 seconds, but nowhere near the 10-15 seconds I would
> expect.
> Also, the problem with openquery is that I need to pass parameters used by
> the query. I didn't see a way to get those dynamically into the query.
> I went ahead and created the sp and executed that against the linked
> server
> and it ran in 50 seconds. Better, but still not what I was expecting.
> "Bruce L-C [MVP]" wrote:
>> Are you doing this in a stored procedure?
>> If not, I have seen people post about issues where the query plan created
>> when a query is executed from RS is different than from query analyzer.
>> As I
>> said, I haven't seen this but I have seen posts about that.
>> If you are using a stored procedure then that would not be an issue. If
>> not,
>> try moving this to a stored procedure and see if that helps.
>> I have thought of one other thing. Depending on the parameterization, I
>> have
>> seen in 2000 based on the parameters and the where clause where SQL
>> Server
>> will decide to bring over all the data from the remote table and process
>> it
>> locally on the server rather than having the query executed remotely and
>> bringing back the result. Based on 11 seconds when hitting the server
>> directly going to 100 seconds, I bet that is what is happening. If you
>> use
>> openquery this will not occur. With 4 part naming you have to really be
>> careful.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Brandon Lunt" <BrandonLunt@.discussions.microsoft.com> wrote in message
>> news:4CCA53E6-1A11-48C0-A1F8-E60B95C0CC03@.microsoft.com...
>> > The report I am producing combines data from 3 different servers. I
>> > typically use linked servers so I don't have to put usernames and
>> > passwords
>> > into my query strings (not all users have access to all databases so I
>> > have
>> > to use a different account). I have usually had good results with
>> > Linked
>> > servers (slight performance hit but never this bad). The other linked
>> > query
>> > (to the 3rd server) runs in about 2 seconds and returns approximately
>> > the
>> > same number or results.
>> >
>> > My confusion I guess is why does it not run the same as a query
>> > analyzer
>> > client connecting to the server?
>> >
>> > "Bruce L-C [MVP]" wrote:
>> >
>> >> First, why are you using linked servers. In most cases this is not
>> >> necessary
>> >> with RS. You can have multiple datasets against multiple data sources.
>> >>
>> >> SQL 2000 can be very very bad with linked servers. SQL 2005 is much
>> >> better.
>> >> I learned this while working with loading a datamart. If you must use
>> >> linked
>> >> servers then you need to be using Openquery to use it. Do not use the
>> >> 4
>> >> part
>> >> naming.
>> >>
>> >>
>> >> --
>> >> Bruce Loehle-Conger
>> >> MVP SQL Server Reporting Services
>> >>
>> >> "Brandon Lunt" <BrandonLunt@.discussions.microsoft.com> wrote in
>> >> message
>> >> news:D9838B81-C5D7-4DA9-85AE-3E2AF317F391@.microsoft.com...
>> >> >I have a query that when executed against the host server, it
>> >> >completes
>> >> >in
>> >> >11
>> >> > seconds. When I execute the same query against a linked server it
>> >> > takes
>> >> > 100
>> >> > seconds to run. I have checked the execution plan for the linked
>> >> > server
>> >> > query and it shows that 100% of the cost is the Remote Query. The
>> >> > resultset
>> >> > is only 260 rows and 5 columns of data. Both servers are SQL2000
>> >> > and
>> >> > Windows
>> >> > 2000 OS.
>> >> >
>> >> > Since both queries ran from query analyzer on my workstation, why is
>> >> > there
>> >> > such a drastic difference in speed? Also, how do I fix the
>> >> > performance
>> >> > issue
>> >> > for the remote query so that it runs in 15 seconds or less?
>> >>
>> >>
>> >>
>>

Remote Query Performance

I have a query that when executed against the host server, it completes in 11
seconds. When I execute the same query against a linked server it takes 100
seconds to run. I have checked the execution plan for the linked server
query and it shows that 100% of the cost is the Remote Query. The resultset
is only 260 rows and 5 columns of data. Both servers are SQL2000 and Windows
2000 OS.
Since both queries ran from query analyzer on my workstation, why is there
such a drastic difference in speed? Also, how do I fix the performance issue
for the remote query so that it runs in 15 seconds or less?Brandon Lunt wrote:
> I have a query that when executed against the host server, it completes in 11
> seconds. When I execute the same query against a linked server it takes 100
> seconds to run. I have checked the execution plan for the linked server
> query and it shows that 100% of the cost is the Remote Query. The resultset
> is only 260 rows and 5 columns of data. Both servers are SQL2000 and Windows
> 2000 OS.
> Since both queries ran from query analyzer on my workstation, why is there
> such a drastic difference in speed? Also, how do I fix the performance issue
> for the remote query so that it runs in 15 seconds or less?
>
The resultset is 260 rows, but how many rows are in the base table?
What indexes are available? Such a query is going to be slower by
default simply because of the network, but often the query engine can't
determine an "optimal" execution plan for a remote query, and will end
up pulling an entire table across the network, and then filtering the
results on the local side.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||The base table has aprox 3 Million records. The servers are communicating on
a Gigabit network. I am connected to the network with a 100 Megabit
connection. Why would SQL pull the table over the network if the query plan
shows that it was executed remotely?
"Tracy McKibben" wrote:
> Brandon Lunt wrote:
> > I have a query that when executed against the host server, it completes in 11
> > seconds. When I execute the same query against a linked server it takes 100
> > seconds to run. I have checked the execution plan for the linked server
> > query and it shows that 100% of the cost is the Remote Query. The resultset
> > is only 260 rows and 5 columns of data. Both servers are SQL2000 and Windows
> > 2000 OS.
> >
> > Since both queries ran from query analyzer on my workstation, why is there
> > such a drastic difference in speed? Also, how do I fix the performance issue
> > for the remote query so that it runs in 15 seconds or less?
> >
> The resultset is 260 rows, but how many rows are in the base table?
> What indexes are available? Such a query is going to be slower by
> default simply because of the network, but often the query engine can't
> determine an "optimal" execution plan for a remote query, and will end
> up pulling an entire table across the network, and then filtering the
> results on the local side.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||"Brandon Lunt" <BrandonLunt@.discussions.microsoft.com> wrote in message
news:5FE2384F-B2B0-407B-91F7-7A39ADFBC690@.microsoft.com...
>I have a query that when executed against the host server, it completes in
>11
> seconds. When I execute the same query against a linked server it takes
> 100
> seconds to run. I have checked the execution plan for the linked server
> query and it shows that 100% of the cost is the Remote Query. The
> resultset
> is only 260 rows and 5 columns of data. Both servers are SQL2000 and
> Windows
> 2000 OS.
> Since both queries ran from query analyzer on my workstation, why is there
> such a drastic difference in speed? Also, how do I fix the performance
> issue
> for the remote query so that it runs in 15 seconds or less?
>
Can you run it through OPENQUERY? This would pass the query text to the
linked server, and just return you the results.
David|||Brandon Lunt wrote:
> The base table has aprox 3 Million records. The servers are communicating on
> a Gigabit network. I am connected to the network with a 100 Megabit
> connection. Why would SQL pull the table over the network if the query plan
> shows that it was executed remotely?
>
The remote "query" is simply indicating that "something" was done on the
remote side. As David suggested, try using OPENQUERY, that will
guarantee that the query is executed on the remote side.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I used the following syntax to run the openquery
select * from openquery([linkedserver], 'query string')
same results, 1:37 elapsed time.
"David Browne" wrote:
> "Brandon Lunt" <BrandonLunt@.discussions.microsoft.com> wrote in message
> news:5FE2384F-B2B0-407B-91F7-7A39ADFBC690@.microsoft.com...
> >I have a query that when executed against the host server, it completes in
> >11
> > seconds. When I execute the same query against a linked server it takes
> > 100
> > seconds to run. I have checked the execution plan for the linked server
> > query and it shows that 100% of the cost is the Remote Query. The
> > resultset
> > is only 260 rows and 5 columns of data. Both servers are SQL2000 and
> > Windows
> > 2000 OS.
> >
> > Since both queries ran from query analyzer on my workstation, why is there
> > such a drastic difference in speed? Also, how do I fix the performance
> > issue
> > for the remote query so that it runs in 15 seconds or less?
> >
> Can you run it through OPENQUERY? This would pass the query text to the
> linked server, and just return you the results.
> David
>
>

remote perfmon

Hi,
How do i use the performance monitor of my pc to remote performance a
server which is not available in a domain? The server i try to monitor
also has sql server installed on it and i want to log the counters for it.
Is this possible?I haven't tried this but I think all you need to do is establish security
credentials that are recognized by the remote computer before connecting
with perfmon. The easiest way to do this is to map a drive to the remote
machine using the remote machine's local administrator account.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jason" <jasonlewis@.hotmail.com> wrote in message
news:uyo2$ffPGHA.3164@.TK2MSFTNGP11.phx.gbl...
> Hi,
> How do i use the performance monitor of my pc to remote performance a
> server which is not available in a domain? The server i try to monitor
> also has sql server installed on it and i want to log the counters for it.
> Is this possible?

remote perfmon

Hi,
How do i use the performance monitor of my pc to remote performance a
server which is not available in a domain? The server i try to monitor
also has sql server installed on it and i want to log the counters for it.
Is this possible?I haven't tried this but I think all you need to do is establish security
credentials that are recognized by the remote computer before connecting
with perfmon. The easiest way to do this is to map a drive to the remote
machine using the remote machine's local administrator account.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Jason" <jasonlewis@.hotmail.com> wrote in message
news:uyo2$ffPGHA.3164@.TK2MSFTNGP11.phx.gbl...
> Hi,
> How do i use the performance monitor of my pc to remote performance a
> server which is not available in a domain? The server i try to monitor
> also has sql server installed on it and i want to log the counters for it.
> Is this possible?

remote perfmon

Hi,
How do i use the performance monitor of my pc to remote performance a
server which is not available in a domain? The server i try to monitor
also has sql server installed on it and i want to log the counters for it.
Is this possible?
I haven't tried this but I think all you need to do is establish security
credentials that are recognized by the remote computer before connecting
with perfmon. The easiest way to do this is to map a drive to the remote
machine using the remote machine's local administrator account.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jason" <jasonlewis@.hotmail.com> wrote in message
news:uyo2$ffPGHA.3164@.TK2MSFTNGP11.phx.gbl...
> Hi,
> How do i use the performance monitor of my pc to remote performance a
> server which is not available in a domain? The server i try to monitor
> also has sql server installed on it and i want to log the counters for it.
> Is this possible?

Remote non-administrator monitoring of sql stats

How can I get my sql dba's to access sql counters in performance monitor
without making them computer administrators? Q158438 has aided them in
busting them into all the standard counters, but still, only my computer
administrator account seems to be able to find the sql counters?hmm... didn't know about that KB. Interesting...
In the past I've told DBA's they can look at SQL specific counters by
reading master..sysperfinfo directly. That's where PerfMon grabs all it's
SQL data from...
you could always query the table directly...
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Robert Hindla" <rhindla@.panix.com> wrote in message
news:BB31AFED.5B2B%rhindla@.panix.com...
> How can I get my sql dba's to access sql counters in performance monitor
> without making them computer administrators? Q158438 has aided them in
> busting them into all the standard counters, but still, only my computer
> administrator account seems to be able to find the sql counters?
>

Remote insert performance

It seems that remote insert takes much more time than "local" insert.
My table MyServer.MyDb.dbo.MyTable has hudreds of milions of rows and
hudreds of GB. It has 3 indexes: clustered (not unique), nonclustered unique
and nonclustered (not unique).
An insert of tens or hundreds is imediate in MyServer. However it takes
minutes (cca. 1 minute per each 100 rows) when inserted with remote insert
--executed at OtherServer
insert MyServer.MyDb.dbo.MyTable select * from #TmpTable
(It does not make diffrence wether the select is from temporary or ordinary
table)
What is the reason? Can I influence it?
Thank you for commentsHi
The Network IO and RPC traffic involved in doing a remote call carry a lot
of overhead. This really slows the process down and is expected.
If you want ot compare it, create a linked server on a server to itself and
run a simular process.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"karuzo" wrote:

> It seems that remote insert takes much more time than "local" insert.
> My table MyServer.MyDb.dbo.MyTable has hudreds of milions of rows and
> hudreds of GB. It has 3 indexes: clustered (not unique), nonclustered uniq
ue
> and nonclustered (not unique).
> An insert of tens or hundreds is imediate in MyServer. However it takes
> minutes (cca. 1 minute per each 100 rows) when inserted with remote insert
> --executed at OtherServer
> insert MyServer.MyDb.dbo.MyTable select * from #TmpTable
> (It does not make diffrence wether the select is from temporary or ordinar
y
> table)
> What is the reason? Can I influence it?
> Thank you for comments

Remote Connections Refused when server under load

Hi All,
We have a .NET 2.0 web application. Occasionally, we get the following
error below when we are doing performance testing. It is not consistent and
only happens every once in awhile. We are using TCP/IP and only have one
instance of SQL running. We do specify connection pooling in the connection
string. There is virtually nothing happening on the database server - it
only contains a few rows of data to control navigation for the site.
System.Data.SqlClient.SqlException: An error has occurred while establishing
a connection to the server. When connecting to SQL Server 2005, this failure
may be caused by the fact that under the default settings SQL Server does not
allow remote connections. (provider: Named Pipes Provider, error: 40 - Could
not open a connection to SQL Server
This is a different error and not quite what you describe but it might be
involved in your problem. This is from the Books Online topic "Server
Network Configuration."
Connections May Be Forcibly Closed When Running on Windows Server 2003 SP1
When testing scalability with a large number of client connection attempts
to an instance of the SQL Server Database Engine running on Windows Server
2003 Service Pack 1, Windows may drop connections if the requests arrive
faster than SQL Server can service them. This is a security feature of
Windows Server 2003 Service Pack 1, which implements a finite queue for
incoming TCP connection requests. It results in the following error:
ProviderNum: 7, Error: 10054, ErrorMessage: "TCP Provider: An existing
connection was forcibly closed by the remote host ...
To resolve this issue, use the regedit.exe utility to add the following
registry key:
Key Type Name Value
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Servic es\Tcpip\Parameters\
DWORD
SynAttackProtect
00000000
Security Note:
Setting this registry key may expose the server to a SYN flood,
denial-of-service attack. Add this registry value only if necessary and with
an understanding of the security risks. Remove this registry value when
testing is complete.
Rick Byham (MSFT)
This posting is provided "AS IS" with no warranties, and confers no rights.
"Bill P" <Bill P@.discussions.microsoft.com> wrote in message
news:2D3D349D-EE1C-4945-82FE-D3717AD02E2F@.microsoft.com...
> Hi All,
> We have a .NET 2.0 web application. Occasionally, we get the following
> error below when we are doing performance testing. It is not consistent
> and
> only happens every once in awhile. We are using TCP/IP and only have one
> instance of SQL running. We do specify connection pooling in the
> connection
> string. There is virtually nothing happening on the database server - it
> only contains a few rows of data to control navigation for the site.
> System.Data.SqlClient.SqlException: An error has occurred while
> establishing
> a connection to the server. When connecting to SQL Server 2005, this
> failure
> may be caused by the fact that under the default settings SQL Server does
> not
> allow remote connections. (provider: Named Pipes Provider, error: 40 -
> Could
> not open a connection to SQL Server
|||Rick,
Thank you for the reply. It very well could have something to do with this.
I am going to give it a try. It may be a few days before I find anything
out since this happens only once in a while.
Bill Portman
"Rick Byham, (MSFT)" wrote:

> This is a different error and not quite what you describe but it might be
> involved in your problem. This is from the Books Online topic "Server
> Network Configuration."
> Connections May Be Forcibly Closed When Running on Windows Server 2003 SP1
> When testing scalability with a large number of client connection attempts
> to an instance of the SQL Server Database Engine running on Windows Server
> 2003 Service Pack 1, Windows may drop connections if the requests arrive
> faster than SQL Server can service them. This is a security feature of
> Windows Server 2003 Service Pack 1, which implements a finite queue for
> incoming TCP connection requests. It results in the following error:
> ProviderNum: 7, Error: 10054, ErrorMessage: "TCP Provider: An existing
> connection was forcibly closed by the remote host ...
> To resolve this issue, use the regedit.exe utility to add the following
> registry key:
> Key Type Name Value
> HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Servic es\Tcpip\Parameters\
> DWORD
> SynAttackProtect
> 00000000
> Security Note:
> Setting this registry key may expose the server to a SYN flood,
> denial-of-service attack. Add this registry value only if necessary and with
> an understanding of the security risks. Remove this registry value when
> testing is complete.
> --
> Rick Byham (MSFT)
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Bill P" <Bill P@.discussions.microsoft.com> wrote in message
> news:2D3D349D-EE1C-4945-82FE-D3717AD02E2F@.microsoft.com...
>

Remote Connections Refused when server under load

Hi All,
We have a .NET 2.0 web application. Occasionally, we get the following
error below when we are doing performance testing. It is not consistent and
only happens every once in awhile. We are using TCP/IP and only have one
instance of SQL running. We do specify connection pooling in the connection
string. There is virtually nothing happening on the database server - it
only contains a few rows of data to control navigation for the site.
System.Data.SqlClient.SqlException: An error has occurred while establishing
a connection to the server. When connecting to SQL Server 2005, this failur
e
may be caused by the fact that under the default settings SQL Server does no
t
allow remote connections. (provider: Named Pipes Provider, error: 40 - Could
not open a connection to SQL ServerThis is a different error and not quite what you describe but it might be
involved in your problem. This is from the Books Online topic "Server
Network Configuration."
Connections May Be Forcibly Closed When Running on Windows Server 2003 SP1
When testing scalability with a large number of client connection attempts
to an instance of the SQL Server Database Engine running on Windows Server
2003 Service Pack 1, Windows may drop connections if the requests arrive
faster than SQL Server can service them. This is a security feature of
Windows Server 2003 Service Pack 1, which implements a finite queue for
incoming TCP connection requests. It results in the following error:
ProviderNum: 7, Error: 10054, ErrorMessage: "TCP Provider: An existing
connection was forcibly closed by the remote host ...
To resolve this issue, use the regedit.exe utility to add the following
registry key:
Key Type Name Value
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControl
Set\Services\Tcpip\Parameters\
DWORD
SynAttackProtect
00000000
Security Note:
Setting this registry key may expose the server to a SYN flood,
denial-of-service attack. Add this registry value only if necessary and with
an understanding of the security risks. Remove this registry value when
testing is complete.
Rick Byham (MSFT)
This posting is provided "AS IS" with no warranties, and confers no rights.
"Bill P" <Bill P@.discussions.microsoft.com> wrote in message
news:2D3D349D-EE1C-4945-82FE-D3717AD02E2F@.microsoft.com...
> Hi All,
> We have a .NET 2.0 web application. Occasionally, we get the following
> error below when we are doing performance testing. It is not consistent
> and
> only happens every once in awhile. We are using TCP/IP and only have one
> instance of SQL running. We do specify connection pooling in the
> connection
> string. There is virtually nothing happening on the database server - it
> only contains a few rows of data to control navigation for the site.
> System.Data.SqlClient.SqlException: An error has occurred while
> establishing
> a connection to the server. When connecting to SQL Server 2005, this
> failure
> may be caused by the fact that under the default settings SQL Server does
> not
> allow remote connections. (provider: Named Pipes Provider, error: 40 -
> Could
> not open a connection to SQL Server|||Rick,
Thank you for the reply. It very well could have something to do with this.
I am going to give it a try. It may be a few days before I find anything
out since this happens only once in a while.
Bill Portman
"Rick Byham, (MSFT)" wrote:

> This is a different error and not quite what you describe but it might be
> involved in your problem. This is from the Books Online topic "Server
> Network Configuration."
> Connections May Be Forcibly Closed When Running on Windows Server 2003 SP1
> When testing scalability with a large number of client connection attempts
> to an instance of the SQL Server Database Engine running on Windows Server
> 2003 Service Pack 1, Windows may drop connections if the requests arrive
> faster than SQL Server can service them. This is a security feature of
> Windows Server 2003 Service Pack 1, which implements a finite queue for
> incoming TCP connection requests. It results in the following error:
> ProviderNum: 7, Error: 10054, ErrorMessage: "TCP Provider: An existing
> connection was forcibly closed by the remote host ...
> To resolve this issue, use the regedit.exe utility to add the following
> registry key:
> Key Type Name Value
> HKEY_LOCAL_MACHINE\SYSTEM\CurrentControl
Set\Services\Tcpip\Parameters\
> DWORD
> SynAttackProtect
> 00000000
> Security Note:
> Setting this registry key may expose the server to a SYN flood,
> denial-of-service attack. Add this registry value only if necessary and wi
th
> an understanding of the security risks. Remove this registry value when
> testing is complete.
> --
> Rick Byham (MSFT)
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> "Bill P" <Bill P@.discussions.microsoft.com> wrote in message
> news:2D3D349D-EE1C-4945-82FE-D3717AD02E2F@.microsoft.com...
>