Hello all:
I have several databases (one per customer) on several different
servers. The servers and databases are alike in structure. However,
one of the databases is consistently coming up with different (and
slower) query plans than the others (which are consistent), even
disregarding the correct indices to use until a table hint is
provided, something the other databases are not needing. I repeat that
the databases all have an identical structure - except, I suppose, for
whatever is causing this behavior.
Can anyone point me in the direction of why this might be happening?
Thanks,
zdrakecStatistics? Radically different data?
http://msdn2.microsoft.com/en-us/library/aa260645(SQL.80).aspx
Just a thought. If that turns out to be it, maybe look into the Auto Update
Statistics option for the database.
//Andrew
> Hello all:
> I have several databases (one per customer) on several different
> servers. The servers and databases are alike in structure. However,
> one of the databases is consistently coming up with different (and
> slower) query plans than the others (which are consistent), even
> disregarding the correct indices to use until a table hint is
> provided, something the other databases are not needing. I repeat that
> the databases all have an identical structure - except, I suppose, for
> whatever is causing this behavior.
> Can anyone point me in the direction of why this might be happening?
> Thanks,
> zdrakec|||On Sep 4, 4:26 pm, Andrew Backer <awbac...@.gmail.com> wrote:
> Statistics? Radically different data?
> http://msdn2.microsoft.com/en-us/library/aa260645(SQL.80).aspx
> Just a thought. If that turns out to be it, maybe look into the Auto Update
> Statistics option for the database.
> //Andrew
>
> > Hello all:
> > I have several databases (one per customer) on several different
> > servers. The servers and databases are alike in structure. However,
> > one of the databases is consistently coming up with different (and
> > slower) query plans than the others (which are consistent), even
> > disregarding the correct indices to use until a table hint is
> > provided, something the other databases are not needing. I repeat that
> > the databases all have an identical structure - except, I suppose, for
> > whatever is causing this behavior.
> > Can anyone point me in the direction of why this might be happening?
> > Thanks,
> > zdrakec- Hide quoted text -
> - Show quoted text -
Thank you Andrew:
Well, the data is of course different, but only in details, not in
form, so not radically so. We have an app I wrote that updates the
statistics on all the databases on a regular basis, so I don't think
that is it either. However, I do believe that it has something to do
with the setup.
Thanks for you input,
zdrakec
Showing posts with label servers. Show all posts
Showing posts with label servers. Show all posts
Friday, March 23, 2012
Query plan
I have 2 SQL Servers with same hardware, configuration and database layout,
but the query plans are different on each server. I tried to look on BOL and
websites and have not find an aswer to why they are different even though
they have same data set. If you have experience this situation before or
know the answer, please help. Thanks!If the data is really the same, as well as the rest of the things you
mention, then I would look at updating statistics on both systems and
see if that brings them closer to using the same plans.
Roy Harvey
Beacon Falls, CT
On Wed, 5 Jul 2006 17:08:02 -0700, KTN <KTN@.discussions.microsoft.com>
wrote:
>I have 2 SQL Servers with same hardware, configuration and database layout,
>but the query plans are different on each server. I tried to look on BOL and
>websites and have not find an aswer to why they are different even though
>they have same data set. If you have experience this situation before or
>know the answer, please help. Thanks!
but the query plans are different on each server. I tried to look on BOL and
websites and have not find an aswer to why they are different even though
they have same data set. If you have experience this situation before or
know the answer, please help. Thanks!If the data is really the same, as well as the rest of the things you
mention, then I would look at updating statistics on both systems and
see if that brings them closer to using the same plans.
Roy Harvey
Beacon Falls, CT
On Wed, 5 Jul 2006 17:08:02 -0700, KTN <KTN@.discussions.microsoft.com>
wrote:
>I have 2 SQL Servers with same hardware, configuration and database layout,
>but the query plans are different on each server. I tried to look on BOL and
>websites and have not find an aswer to why they are different even though
>they have same data set. If you have experience this situation before or
>know the answer, please help. Thanks!
Query plan
I have 2 SQL Servers with same hardware, configuration and database layout,
but the query plans are different on each server. I tried to look on BOL an
d
websites and have not find an aswer to why they are different even though
they have same data set. If you have experience this situation before or
know the answer, please help. Thanks!If the data is really the same, as well as the rest of the things you
mention, then I would look at updating statistics on both systems and
see if that brings them closer to using the same plans.
Roy Harvey
Beacon Falls, CT
On Wed, 5 Jul 2006 17:08:02 -0700, KTN <KTN@.discussions.microsoft.com>
wrote:
>I have 2 SQL Servers with same hardware, configuration and database layout,
>but the query plans are different on each server. I tried to look on BOL a
nd
>websites and have not find an aswer to why they are different even though
>they have same data set. If you have experience this situation before or
>know the answer, please help. Thanks!
but the query plans are different on each server. I tried to look on BOL an
d
websites and have not find an aswer to why they are different even though
they have same data set. If you have experience this situation before or
know the answer, please help. Thanks!If the data is really the same, as well as the rest of the things you
mention, then I would look at updating statistics on both systems and
see if that brings them closer to using the same plans.
Roy Harvey
Beacon Falls, CT
On Wed, 5 Jul 2006 17:08:02 -0700, KTN <KTN@.discussions.microsoft.com>
wrote:
>I have 2 SQL Servers with same hardware, configuration and database layout,
>but the query plans are different on each server. I tried to look on BOL a
nd
>websites and have not find an aswer to why they are different even though
>they have same data set. If you have experience this situation before or
>know the answer, please help. Thanks!
Monday, February 20, 2012
query multiple databases on different servers
I want to query from a SQL Server 2000 database and put the results into a
SQL Server 2005 database located on another server. I thought that I had
used the following syntax in the past (without added any linked servers):
INSERT INTO houston.mytestdb.dbo.memo
SELECT * FROM tennessee.mytestdb.dbo.memo
But I get an error: Could not find server 'tennessee' in sysservers. Execute
sp_addlinkedserver...
If I do add this server using the stored procedure I get the following
errors when trying to execute the sql:
Server: Msg 18452, Level 14, State 1, Line 0
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
Server: Msg 10054, Level 16, State 1, Line 0
TCP Provider: An existing connection was forcibly closed by the remote host.
OLE DB provider "SQLNCLI" for linked server "tennessee" returned message
"Communication link failure".
What's that all about?
Anyway I can avoid adding linked servers in the first place?
Thanks!
-j
The error you are getting is that the link is trying to use integrated
security and there is no context being passed. Could be a lot of reasons
- firewall, no trust between domains, ...
Did you set you set up the linked server to use integrated security?
You will also probably need to make sure that dtc is started on both
m/c's.
Do you really want to own a remote table from the local server? It's
usually better to populate from the machine where the table resides
calling a stored procedure to extract.
*** Sent via Developersdex http://www.codecomments.com ***
SQL Server 2005 database located on another server. I thought that I had
used the following syntax in the past (without added any linked servers):
INSERT INTO houston.mytestdb.dbo.memo
SELECT * FROM tennessee.mytestdb.dbo.memo
But I get an error: Could not find server 'tennessee' in sysservers. Execute
sp_addlinkedserver...
If I do add this server using the stored procedure I get the following
errors when trying to execute the sql:
Server: Msg 18452, Level 14, State 1, Line 0
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
Server: Msg 10054, Level 16, State 1, Line 0
TCP Provider: An existing connection was forcibly closed by the remote host.
OLE DB provider "SQLNCLI" for linked server "tennessee" returned message
"Communication link failure".
What's that all about?
Anyway I can avoid adding linked servers in the first place?
Thanks!
-j
The error you are getting is that the link is trying to use integrated
security and there is no context being passed. Could be a lot of reasons
- firewall, no trust between domains, ...
Did you set you set up the linked server to use integrated security?
You will also probably need to make sure that dtc is started on both
m/c's.
Do you really want to own a remote table from the local server? It's
usually better to populate from the machine where the table resides
calling a stored procedure to extract.
*** Sent via Developersdex http://www.codecomments.com ***
query multiple databases on different servers
I want to query from a SQL Server 2000 database and put the results into a
SQL Server 2005 database located on another server. I thought that I had
used the following syntax in the past (without added any linked servers):
INSERT INTO houston.mytestdb.dbo.memo
SELECT * FROM tennessee.mytestdb.dbo.memo
But I get an error: Could not find server 'tennessee' in sysservers. Execute
sp_addlinkedserver...
If I do add this server using the stored procedure I get the following
errors when trying to execute the sql:
Server: Msg 18452, Level 14, State 1, Line 0
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
Server: Msg 10054, Level 16, State 1, Line 0
TCP Provider: An existing connection was forcibly closed by the remote host.
OLE DB provider "SQLNCLI" for linked server "tennessee" returned message
"Communication link failure".
What's that all about?
Anyway I can avoid adding linked servers in the first place?
Thanks!
-jThe error you are getting is that the link is trying to use integrated
security and there is no context being passed. Could be a lot of reasons
- firewall, no trust between domains, ...
Did you set you set up the linked server to use integrated security?
You will also probably need to make sure that dtc is started on both
m/c's.
Do you really want to own a remote table from the local server? It's
usually better to populate from the machine where the table resides
calling a stored procedure to extract.
*** Sent via Developersdex http://www.codecomments.com ***
SQL Server 2005 database located on another server. I thought that I had
used the following syntax in the past (without added any linked servers):
INSERT INTO houston.mytestdb.dbo.memo
SELECT * FROM tennessee.mytestdb.dbo.memo
But I get an error: Could not find server 'tennessee' in sysservers. Execute
sp_addlinkedserver...
If I do add this server using the stored procedure I get the following
errors when trying to execute the sql:
Server: Msg 18452, Level 14, State 1, Line 0
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
Server: Msg 10054, Level 16, State 1, Line 0
TCP Provider: An existing connection was forcibly closed by the remote host.
OLE DB provider "SQLNCLI" for linked server "tennessee" returned message
"Communication link failure".
What's that all about?
Anyway I can avoid adding linked servers in the first place?
Thanks!
-jThe error you are getting is that the link is trying to use integrated
security and there is no context being passed. Could be a lot of reasons
- firewall, no trust between domains, ...
Did you set you set up the linked server to use integrated security?
You will also probably need to make sure that dtc is started on both
m/c's.
Do you really want to own a remote table from the local server? It's
usually better to populate from the machine where the table resides
calling a stored procedure to extract.
*** Sent via Developersdex http://www.codecomments.com ***
query multiple databases on different servers
I want to query from a SQL Server 2000 database and put the results into a
SQL Server 2005 database located on another server. I thought that I had
used the following syntax in the past (without added any linked servers):
INSERT INTO houston.mytestdb.dbo.memo
SELECT * FROM tennessee.mytestdb.dbo.memo
But I get an error: Could not find server 'tennessee' in sysservers. Execute
sp_addlinkedserver...
If I do add this server using the stored procedure I get the following
errors when trying to execute the sql:
Server: Msg 18452, Level 14, State 1, Line 0
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
Server: Msg 10054, Level 16, State 1, Line 0
TCP Provider: An existing connection was forcibly closed by the remote host.
OLE DB provider "SQLNCLI" for linked server "tennessee" returned message
"Communication link failure".
What's that all about?
Anyway I can avoid adding linked servers in the first place?
Thanks!
-jThe error you are getting is that the link is trying to use integrated
security and there is no context being passed. Could be a lot of reasons
- firewall, no trust between domains, ...
Did you set you set up the linked server to use integrated security?
You will also probably need to make sure that dtc is started on both
m/c's.
Do you really want to own a remote table from the local server? It's
usually better to populate from the machine where the table resides
calling a stored procedure to extract.
*** Sent via Developersdex http://www.developersdex.com ***
SQL Server 2005 database located on another server. I thought that I had
used the following syntax in the past (without added any linked servers):
INSERT INTO houston.mytestdb.dbo.memo
SELECT * FROM tennessee.mytestdb.dbo.memo
But I get an error: Could not find server 'tennessee' in sysservers. Execute
sp_addlinkedserver...
If I do add this server using the stored procedure I get the following
errors when trying to execute the sql:
Server: Msg 18452, Level 14, State 1, Line 0
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
Server: Msg 10054, Level 16, State 1, Line 0
TCP Provider: An existing connection was forcibly closed by the remote host.
OLE DB provider "SQLNCLI" for linked server "tennessee" returned message
"Communication link failure".
What's that all about?
Anyway I can avoid adding linked servers in the first place?
Thanks!
-jThe error you are getting is that the link is trying to use integrated
security and there is no context being passed. Could be a lot of reasons
- firewall, no trust between domains, ...
Did you set you set up the linked server to use integrated security?
You will also probably need to make sure that dtc is started on both
m/c's.
Do you really want to own a remote table from the local server? It's
usually better to populate from the machine where the table resides
calling a stored procedure to extract.
*** Sent via Developersdex http://www.developersdex.com ***
Subscribe to:
Posts (Atom)