Different Query perfromance on different machines

underprocessableHi
> The query plan is almost same on both machines.
That's the problem I think. Why does not the table have an index/s?
How big is your table?
"Pushkar" <pushkartiwari@.gmail.com> wrote in message
news:%23h$zzJxgGHA.1260@.TK2MSFTNGP05.phx.gbl...
> Hi,
> I am strange behavior in queries on two instances of SQL Server on two
> different machines. On one machine query gakes almost 2 minutes and on
> another machine query timeout after 10 minutes.
> The query plan is almost same on both machines.
> Table on which I am doing select does not have any indexes on both
> machines.
> I am not able to figure out the exact cause of the problem. My query is
> very simple, without any join, then too it is behaving differently,
> strange.
> Configurations of machine with slow performance:
> Win2k3 enterprise edition,P4 3.39 GHz, 1 GB Ram,SQL Server 2000, Developer
> edition.
> Configurations of machine with fast performance:
> Win2k SP4,P4 2.8 GHz, 1.5 GB Ram,SQL Server 2000, Enterprise edition.
> I have attached query plan on both machines.
> Any help is appreciated.
> Thanks
> Pushkar
>|||Thanks Uri for your promt reply.
I can not understand why the same query Plan on both the machine is the
problem.
>Why does not the table have an index/s?
We do not have any index/s because we are performing bulk insert operation
on the table which becomes very slow for indexed table.
Anyway if index will help then it will boost the query performance on both
machines. I just want that I should approx. same performance on both
machines first, then I can think of other optimizations also.
> How big is your table?
Table contains 1 crores of rows.
Thanks,
Pushkar
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23I6gDZxgGHA.1520@.TK2MSFTNGP03.phx.gbl...
> Hi
> That's the problem I think. Why does not the table have an index/s?
> How big is your table?
>
>
>
>
> "Pushkar" <pushkartiwari@.gmail.com> wrote in message
> news:%23h$zzJxgGHA.1260@.TK2MSFTNGP05.phx.gbl...
>|||> I can not understand why the same query Plan on both the machine is the
> problem.
No, you said ALMOST the same
"Pushkar" <pushkartiwari@.gmail.com> wrote in message
news:%23selJ5xgGHA.3756@.TK2MSFTNGP02.phx.gbl...
> Thanks Uri for your promt reply.
> I can not understand why the same query Plan on both the machine is the
> problem.
> We do not have any index/s because we are performing bulk insert operation
> on the table which becomes very slow for indexed table.
> Anyway if index will help then it will boost the query performance on both
> machines. I just want that I should approx. same performance on both
> machines first, then I can think of other optimizations also.
> Table contains 1 crores of rows.
> Thanks,
> Pushkar
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23I6gDZxgGHA.1520@.TK2MSFTNGP03.phx.gbl...
>|||If query plan is exactly the same on both machine can you suggest me, where
should dig upon to find out the cause of the problem?
Thanks,
Pushkar
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23NjQ3BygGHA.2456@.TK2MSFTNGP04.phx.gbl...
> No, you said ALMOST the same
>
> "Pushkar" <pushkartiwari@.gmail.com> wrote in message
> news:%23selJ5xgGHA.3756@.TK2MSFTNGP02.phx.gbl...
>|||Adding more;
I have taken reading for "SQLServer: I/O - Page Reads/sec" on both machines
which are as follows:
Slow Server : 250(Avg)
Fast Server: 1200(Avg)
How can I increase this parameter on slow server to match up to the fast
one.
Thanks
Pushkar
"Pushkar" <pushkartiwari@.gmail.com> wrote in message
news:u6HN2EygGHA.4404@.TK2MSFTNGP05.phx.gbl...
> If query plan is exactly the same on both machine can you suggest me,
> where should dig upon to find out the cause of the problem?
> Thanks,
> Pushkar
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23NjQ3BygGHA.2456@.TK2MSFTNGP04.phx.gbl...
>|||I don't have these counters on my SQL 2000 instance but it stands to reason
that the main difference in performance may be related to disk performance
if a lot of data is scanned. I would expect physical disk: disk bytes /sec
to reflect the performance difference if this is the case.

> How can I increase this parameter on slow server to match up to the fast
> one.
Without hardware changes, about all you can do is move files around.
Sequential scan performance is mostly dictated by the I/O subsystem.
Hope this helps.
Dan Guzman
SQL Server MVP
"Pushkar" <pushkartiwari@.gmail.com> wrote in message
news:%23I6hPWygGHA.1272@.TK2MSFTNGP03.phx.gbl...
> Adding more;
> I have taken reading for "SQLServer: I/O - Page Reads/sec" on both
> machines which are as follows:
> Slow Server : 250(Avg)
> Fast Server: 1200(Avg)
> How can I increase this parameter on slow server to match up to the fast
> one.
> Thanks
> Pushkar
>
> "Pushkar" <pushkartiwari@.gmail.com> wrote in message
> news:u6HN2EygGHA.4404@.TK2MSFTNGP05.phx.gbl...
>

Different performance in different machines,,,

Hi All,
I am looking if somebody know how can this happen,,,
I have a couple of stored procedures that I run, one SP call the other.
There are cursor, memory table, and recursive query.
When I run this against a local database in my computer it takes arount 15
secs to return the result and it is just fine
considering the table is about 1 + million records. My PC is P4 512 kb RAM
Now, when I create this SP in the server machine and test them against the
database and run it, it takes more than 10 minutes.
Amazing!! considering is the same table, same indexes, but this server has 4
processors and 1 gig RAM.
What can that be? Can it be a MS SQL configuration?
Thanks in advance
RobertoAre statistics up to date on the server?
"Roberto Martinez" <roberto@.ccubetech.com> wrote in message
news:ecFiunkJEHA.3436@.tk2msftngp13.phx.gbl...
> Hi All,
> I am looking if somebody know how can this happen,,,
> I have a couple of stored procedures that I run, one SP call the other.
> There are cursor, memory table, and recursive query.
> When I run this against a local database in my computer it takes arount 15
> secs to return the result and it is just fine
> considering the table is about 1 + million records. My PC is P4 512 kb RAM
> Now, when I create this SP in the server machine and test them against the
> database and run it, it takes more than 10 minutes.
> Amazing!! considering is the same table, same indexes, but this server has
4
> processors and 1 gig RAM.
> What can that be? Can it be a MS SQL configuration?
> Thanks in advance
> Roberto
>|||What does you mean with statistic are up to date on the server?
"chris" <chris@.noemail.com> wrote in message
news:%23q$6gDlJEHA.3712@.TK2MSFTNGP09.phx.gbl...
> Are statistics up to date on the server?
>
> "Roberto Martinez" <roberto@.ccubetech.com> wrote in message
> news:ecFiunkJEHA.3436@.tk2msftngp13.phx.gbl...
> > Hi All,
> >
> > I am looking if somebody know how can this happen,,,
> >
> > I have a couple of stored procedures that I run, one SP call the other.
> > There are cursor, memory table, and recursive query.
> > When I run this against a local database in my computer it takes arount
15
> > secs to return the result and it is just fine
> > considering the table is about 1 + million records. My PC is P4 512 kb
RAM
> >
> > Now, when I create this SP in the server machine and test them against
the
> > database and run it, it takes more than 10 minutes.
> > Amazing!! considering is the same table, same indexes, but this server
has
> 4
> > processors and 1 gig RAM.
> >
> > What can that be? Can it be a MS SQL configuration?
> >
> > Thanks in advance
> >
> > Roberto
> >
> >
>|||Check the query plan for the queries on both machines to ensure the same
indexes are used both places.
If they differ, try doing
update statistics <tablename>
on each table used in the query, and look at the Query plan, and compare
execution times. again..
Lastly ( perhaps) try setting maxdop to 1 for the query on the multi proc
machine and see if performance improves...
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.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
"Roberto Martinez" <roberto@.ccubetech.com> wrote in message
news:ecFiunkJEHA.3436@.tk2msftngp13.phx.gbl...
> Hi All,
> I am looking if somebody know how can this happen,,,
> I have a couple of stored procedures that I run, one SP call the other.
> There are cursor, memory table, and recursive query.
> When I run this against a local database in my computer it takes arount 15
> secs to return the result and it is just fine
> considering the table is about 1 + million records. My PC is P4 512 kb RAM
> Now, when I create this SP in the server machine and test them against the
> database and run it, it takes more than 10 minutes.
> Amazing!! considering is the same table, same indexes, but this server has
4
> processors and 1 gig RAM.
> What can that be? Can it be a MS SQL configuration?
> Thanks in advance
> Roberto
>

Different performance in different machines,,,

Hi All,
I am looking if somebody know how can this happen,,,
I have a couple of stored procedures that I run, one SP call the other.
There are cursor, memory table, and recursive query.
When I run this against a local database in my computer it takes arount 15
secs to return the result and it is just fine
considering the table is about 1 + million records. My PC is P4 512 kb RAM
Now, when I create this SP in the server machine and test them against the
database and run it, it takes more than 10 minutes.
Amazing!! considering is the same table, same indexes, but this server has 4
processors and 1 gig RAM.
What can that be? Can it be a MS SQL configuration?
Thanks in advance
RobertoAre statistics up to date on the server?
"Roberto Martinez" <roberto@.ccubetech.com> wrote in message
news:ecFiunkJEHA.3436@.tk2msftngp13.phx.gbl...
> Hi All,
> I am looking if somebody know how can this happen,,,
> I have a couple of stored procedures that I run, one SP call the other.
> There are cursor, memory table, and recursive query.
> When I run this against a local database in my computer it takes arount 15
> secs to return the result and it is just fine
> considering the table is about 1 + million records. My PC is P4 512 kb RAM
> Now, when I create this SP in the server machine and test them against the
> database and run it, it takes more than 10 minutes.
> Amazing!! considering is the same table, same indexes, but this server has
4
> processors and 1 gig RAM.
> What can that be? Can it be a MS SQL configuration?
> Thanks in advance
> Roberto
>|||What does you mean with statistic are up to date on the server?
"chris" <chris@.noemail.com> wrote in message
news:%23q$6gDlJEHA.3712@.TK2MSFTNGP09.phx.gbl...
> Are statistics up to date on the server?
>
> "Roberto Martinez" <roberto@.ccubetech.com> wrote in message
> news:ecFiunkJEHA.3436@.tk2msftngp13.phx.gbl...
15[vbcol=seagreen]
RAM[vbcol=seagreen]
the[vbcol=seagreen]
has[vbcol=seagreen]
> 4
>|||Check the query plan for the queries on both machines to ensure the same
indexes are used both places.
If they differ, try doing
update statistics <tablename>
on each table used in the query, and look at the Query plan, and compare
execution times. again..
Lastly ( perhaps) try setting maxdop to 1 for the query on the multi proc
machine and see if performance improves...
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.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
"Roberto Martinez" <roberto@.ccubetech.com> wrote in message
news:ecFiunkJEHA.3436@.tk2msftngp13.phx.gbl...
> Hi All,
> I am looking if somebody know how can this happen,,,
> I have a couple of stored procedures that I run, one SP call the other.
> There are cursor, memory table, and recursive query.
> When I run this against a local database in my computer it takes arount 15
> secs to return the result and it is just fine
> considering the table is about 1 + million records. My PC is P4 512 kb RAM
> Now, when I create this SP in the server machine and test them against the
> database and run it, it takes more than 10 minutes.
> Amazing!! considering is the same table, same indexes, but this server has
4
> processors and 1 gig RAM.
> What can that be? Can it be a MS SQL configuration?
> Thanks in advance
> Roberto
>

Different performance in different machines,,,

Hi All,
I am looking if somebody know how can this happen,,,
I have a couple of stored procedures that I run, one SP call the other.
There are cursor, memory table, and recursive query.
When I run this against a local database in my computer it takes arount 15
secs to return the result and it is just fine
considering the table is about 1 + million records. My PC is P4 512 kb RAM
Now, when I create this SP in the server machine and test them against the
database and run it, it takes more than 10 minutes.
Amazing!! considering is the same table, same indexes, but this server has 4
processors and 1 gig RAM.
What can that be? Can it be a MS SQL configuration?
Thanks in advance
Roberto
Are statistics up to date on the server?
"Roberto Martinez" <roberto@.ccubetech.com> wrote in message
news:ecFiunkJEHA.3436@.tk2msftngp13.phx.gbl...
> Hi All,
> I am looking if somebody know how can this happen,,,
> I have a couple of stored procedures that I run, one SP call the other.
> There are cursor, memory table, and recursive query.
> When I run this against a local database in my computer it takes arount 15
> secs to return the result and it is just fine
> considering the table is about 1 + million records. My PC is P4 512 kb RAM
> Now, when I create this SP in the server machine and test them against the
> database and run it, it takes more than 10 minutes.
> Amazing!! considering is the same table, same indexes, but this server has
4
> processors and 1 gig RAM.
> What can that be? Can it be a MS SQL configuration?
> Thanks in advance
> Roberto
>
|||What does you mean with statistic are up to date on the server?
"chris" <chris@.noemail.com> wrote in message
news:%23q$6gDlJEHA.3712@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Are statistics up to date on the server?
>
> "Roberto Martinez" <roberto@.ccubetech.com> wrote in message
> news:ecFiunkJEHA.3436@.tk2msftngp13.phx.gbl...
15[vbcol=seagreen]
RAM[vbcol=seagreen]
the[vbcol=seagreen]
has
> 4
>
|||Check the query plan for the queries on both machines to ensure the same
indexes are used both places.
If they differ, try doing
update statistics <tablename>
on each table used in the query, and look at the Query plan, and compare
execution times. again..
Lastly ( perhaps) try setting maxdop to 1 for the query on the multi proc
machine and see if performance improves...
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.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
"Roberto Martinez" <roberto@.ccubetech.com> wrote in message
news:ecFiunkJEHA.3436@.tk2msftngp13.phx.gbl...
> Hi All,
> I am looking if somebody know how can this happen,,,
> I have a couple of stored procedures that I run, one SP call the other.
> There are cursor, memory table, and recursive query.
> When I run this against a local database in my computer it takes arount 15
> secs to return the result and it is just fine
> considering the table is about 1 + million records. My PC is P4 512 kb RAM
> Now, when I create this SP in the server machine and test them against the
> database and run it, it takes more than 10 minutes.
> Amazing!! considering is the same table, same indexes, but this server has
4
> processors and 1 gig RAM.
> What can that be? Can it be a MS SQL configuration?
> Thanks in advance
> Roberto
>

Different @@version

I have 2 machines, both that I _thought_ were loaded with the same
version, but when I do select @.@.version on the two I get different
results:
1.
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
May 3 2005 23:18:38
Cop
yright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows
NT 5.2 (Build 3790: )
2.
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
May 3 2005 23:18:38
Cop
yright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows
NT 5.2 (Build 3790: Service Pack 1)
Notice how 1 doesn't say Service Pack 1. Both should have SP4 on them.
WHat is the difference?
Darin
*** Sent via Developersdex http://www.codecomments.com ***> Notice how 1 doesn't say Service Pack 1.
That is the service pack of the operating system, not the SQL Server instanc
e. The interesting part
is:
> Microsoft SQL Server 2000 - 8.00.2039
You can now map the build number to the service pack (Aaron has an article o
n this at
www.aspfaq.com). You can also use the SERVERPROPERTY() function to get the s
ervice pack from your
SQL Server.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Darin" <darin_nospam@.nospamever> wrote in message news:OV95wYneGHA.4840@.TK2MSFTNGP03.phx.gb
l...
>I have 2 machines, both that I _thought_ were loaded with the same
> version, but when I do select @.@.version on the two I get different
> results:
> 1.
> Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
> May 3 2005 23:18:38
> Cop
> yright (c) 1988-2003 Microsoft Corporation
> Standard Edition on Windows
> NT 5.2 (Build 3790: )
> 2.
> Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
> May 3 2005 23:18:38
> Cop
> yright (c) 1988-2003 Microsoft Corporation
> Standard Edition on Windows
> NT 5.2 (Build 3790: Service Pack 1)
> Notice how 1 doesn't say Service Pack 1. Both should have SP4 on them.
> WHat is the difference?
> Darin
> *** Sent via Developersdex http://www.codecomments.com ***

Different @@version

I have 2 machines, both that I _thought_ were loaded with the same
version, but when I do select @.@.version on the two I get different
results:
1.
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
May 3 2005 23:18:38
Cop
yright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows
NT 5.2 (Build 3790: )
2.
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
May 3 2005 23:18:38
Cop
yright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows
NT 5.2 (Build 3790: Service Pack 1)
Notice how 1 doesn't say Service Pack 1. Both should have SP4 on them.
WHat is the difference?
Darin
*** Sent via Developersdex http://www.developersdex.com ***> Notice how 1 doesn't say Service Pack 1.
That is the service pack of the operating system, not the SQL Server instance. The interesting part
is:
> Microsoft SQL Server 2000 - 8.00.2039
You can now map the build number to the service pack (Aaron has an article on this at
www.aspfaq.com). You can also use the SERVERPROPERTY() function to get the service pack from your
SQL Server.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Darin" <darin_nospam@.nospamever> wrote in message news:OV95wYneGHA.4840@.TK2MSFTNGP03.phx.gbl...
>I have 2 machines, both that I _thought_ were loaded with the same
> version, but when I do select @.@.version on the two I get different
> results:
> 1.
> Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
> May 3 2005 23:18:38
> Cop
> yright (c) 1988-2003 Microsoft Corporation
> Standard Edition on Windows
> NT 5.2 (Build 3790: )
> 2.
> Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
> May 3 2005 23:18:38
> Cop
> yright (c) 1988-2003 Microsoft Corporation
> Standard Edition on Windows
> NT 5.2 (Build 3790: Service Pack 1)
> Notice how 1 doesn't say Service Pack 1. Both should have SP4 on them.
> WHat is the difference?
> Darin
> *** Sent via Developersdex http://www.developersdex.com ***