SQL 2000 vs SQL 7.0 Performance

SQL 2000 vs SQL 7.0 Performance

Post by Vasilis Mazaraki » Fri, 23 Nov 2001 23:14:30



Hi all, I have the following problem:

I have the same Database ( inc. Indexes, data, permissions
etc) on one machine which has SQL Server 2000 SP1 and in
one with SQL Server 7.0 SP2. My problem is that executing
queries to the sqlserver 2000 database they are very
slower than the same queries on SQL 7.0 Database. I am
using a query like " Select * from table where column
like '%case%' " The Execution time on the first server is
30 seconds and on the second is 5. Can anyone help me with
that? can anyone describe me why 's that delay?

What is going wrong. The machines was clean before the SQL
Server Installations and they followed the same
Installation Plan.

Also the machine with the SQLServer 2000 has More RAM ,
Disk Space and a better Proccessor

Regards

 
 
 

SQL 2000 vs SQL 7.0 Performance

Post by Andrew J. Kell » Sat, 24 Nov 2001 00:18:30


Vasilis,

Using LIKE with a wildcard at the beginning (  '%xxx') will always force a
table scan no matter how many idexes you have.  So if it's slower on one
machine than the other it is most likely due to the hardware setup.  The
second machine probably has slower disks and or less ram..  If you run it
several times you should get the table into cache (assuming it all fits) and
then it will be mostly be how fast can the processor filter out all the bad
rows from the good ones.

--
Andrew J. Kelly,   SQL Server MVP


Quote:> Hi all, I have the following problem:

> I have the same Database ( inc. Indexes, data, permissions
> etc) on one machine which has SQL Server 2000 SP1 and in
> one with SQL Server 7.0 SP2. My problem is that executing
> queries to the sqlserver 2000 database they are very
> slower than the same queries on SQL 7.0 Database. I am
> using a query like " Select * from table where column
> like '%case%' " The Execution time on the first server is
> 30 seconds and on the second is 5. Can anyone help me with
> that? can anyone describe me why 's that delay?

> What is going wrong. The machines was clean before the SQL
> Server Installations and they followed the same
> Installation Plan.

> Also the machine with the SQLServer 2000 has More RAM ,
> Disk Space and a better Proccessor

> Regards


 
 
 

SQL 2000 vs SQL 7.0 Performance

Post by Vasilis Mazaraki » Sat, 24 Nov 2001 00:36:57


It is not a Hardware problem. The SQL Server 2000 Machine
which is slower is configured to 512 MB RAM 866 MHZ
Processor (Intel) and has an 16 GB HDD. Also I want to
mention that this server is not dedicated.

After all We resetup the machine but the queries running
slower more than ever. At this time we use SQL 7.0 Because
it is more fast so our work is going on.

After that we will probably stay on this solution because
there is nothing else to do.

Thank you for replying
Vasilis

Quote:>-----Original Message-----
>Vasilis,

>Using LIKE with a wildcard at the beginning (  '%xxx')
will always force a
>table scan no matter how many idexes you have.  So if
it's slower on one
>machine than the other it is most likely due to the

hardware setup.  The
Quote:>second machine probably has slower disks and or less

ram..  If you run it
Quote:>several times you should get the table into cache

(assuming it all fits) and
Quote:>then it will be mostly be how fast can the processor

filter out all the bad
>rows from the good ones.

>--
>Andrew J. Kelly,   SQL Server MVP



>> Hi all, I have the following problem:

>> I have the same Database ( inc. Indexes, data,
permissions
>> etc) on one machine which has SQL Server 2000 SP1 and in
>> one with SQL Server 7.0 SP2. My problem is that
executing
>> queries to the sqlserver 2000 database they are very
>> slower than the same queries on SQL 7.0 Database. I am
>> using a query like " Select * from table where column
>> like '%case%' " The Execution time on the first server
is
>> 30 seconds and on the second is 5. Can anyone help me
with
>> that? can anyone describe me why 's that delay?

>> What is going wrong. The machines was clean before the
SQL
>> Server Installations and they followed the same
>> Installation Plan.

>> Also the machine with the SQLServer 2000 has More RAM ,
>> Disk Space and a better Proccessor

>> Regards

>.

 
 
 

SQL 2000 vs SQL 7.0 Performance

Post by Vasilis Mazaraki » Sat, 24 Nov 2001 00:36:47


It is not a Hardware problem. The SQL Server 2000 Machine
which is slower is configured to 512 MB RAM 866 MHZ
Processor (Intel) and has an 16 GB HDD. Also I want to
mention that this server is not dedicated.

After all We resetup the machine but the queries running
slower more than ever. At this time we use SQL 7.0 Because
it is more fast so our work is going on.

After that we will probably stay on this solution because
there is nothing else to do.

Thank you for replying
Vasilis

Quote:>-----Original Message-----
>Vasilis,

>Using LIKE with a wildcard at the beginning (  '%xxx')
will always force a
>table scan no matter how many idexes you have.  So if
it's slower on one
>machine than the other it is most likely due to the

hardware setup.  The
Quote:>second machine probably has slower disks and or less

ram..  If you run it
Quote:>several times you should get the table into cache

(assuming it all fits) and
Quote:>then it will be mostly be how fast can the processor

filter out all the bad
>rows from the good ones.

>--
>Andrew J. Kelly,   SQL Server MVP



>> Hi all, I have the following problem:

>> I have the same Database ( inc. Indexes, data,
permissions
>> etc) on one machine which has SQL Server 2000 SP1 and in
>> one with SQL Server 7.0 SP2. My problem is that
executing
>> queries to the sqlserver 2000 database they are very
>> slower than the same queries on SQL 7.0 Database. I am
>> using a query like " Select * from table where column
>> like '%case%' " The Execution time on the first server
is
>> 30 seconds and on the second is 5. Can anyone help me
with
>> that? can anyone describe me why 's that delay?

>> What is going wrong. The machines was clean before the
SQL
>> Server Installations and they followed the same
>> Installation Plan.

>> Also the machine with the SQLServer 2000 has More RAM ,
>> Disk Space and a better Proccessor

>> Regards

>.

 
 
 

SQL 2000 vs SQL 7.0 Performance

Post by Ric » Sat, 24 Nov 2001 01:25:13


Quote:>-----Original Message-----
>Hi all, I have the following problem:

>I have the same Database ( inc. Indexes, data,
permissions
>etc) on one machine which has SQL Server 2000 SP1 and in
>one with SQL Server 7.0 SP2. My problem is that executing
>queries to the sqlserver 2000 database they are very
>slower than the same queries on SQL 7.0 Database. I am
>using a query like " Select * from table where column
>like '%case%' " The Execution time on the first server is
>30 seconds and on the second is 5. Can anyone help me
with
>that? can anyone describe me why 's that delay?

>What is going wrong. The machines was clean before the
SQL
>Server Installations and they followed the same
>Installation Plan.

>Also the machine with the SQLServer 2000 has More RAM ,
>Disk Space and a better Proccessor

>Regards

>.
>Hi, did you view the execution plan on both servers to

verify that they are/are not doing the same thing?  You
can also use statistics (check "statistics I/O" in BOL)
options to get additional info.  You can also use
performance monitor to see what the query is doing on each
machine and then compare.  I think this is where you would
get the best info from.

Ric

 
 
 

SQL 2000 vs SQL 7.0 Performance

Post by Andrew J. Kell » Sat, 24 Nov 2001 01:38:07


If the server is not dedicated then anything goes. How do you know that
something else isn't using the CPU, Ram and or Disks while your measuring
this?

--
Andrew J. Kelly,   SQL Server MVP


> It is not a Hardware problem. The SQL Server 2000 Machine
> which is slower is configured to 512 MB RAM 866 MHZ
> Processor (Intel) and has an 16 GB HDD. Also I want to
> mention that this server is not dedicated.

> After all We resetup the machine but the queries running
> slower more than ever. At this time we use SQL 7.0 Because
> it is more fast so our work is going on.

> After that we will probably stay on this solution because
> there is nothing else to do.

> Thank you for replying
> Vasilis

> >-----Original Message-----
> >Vasilis,

> >Using LIKE with a wildcard at the beginning (  '%xxx')
> will always force a
> >table scan no matter how many idexes you have.  So if
> it's slower on one
> >machine than the other it is most likely due to the
> hardware setup.  The
> >second machine probably has slower disks and or less
> ram..  If you run it
> >several times you should get the table into cache
> (assuming it all fits) and
> >then it will be mostly be how fast can the processor
> filter out all the bad
> >rows from the good ones.

> >--
> >Andrew J. Kelly,   SQL Server MVP



> >> Hi all, I have the following problem:

> >> I have the same Database ( inc. Indexes, data,
> permissions
> >> etc) on one machine which has SQL Server 2000 SP1 and in
> >> one with SQL Server 7.0 SP2. My problem is that
> executing
> >> queries to the sqlserver 2000 database they are very
> >> slower than the same queries on SQL 7.0 Database. I am
> >> using a query like " Select * from table where column
> >> like '%case%' " The Execution time on the first server
> is
> >> 30 seconds and on the second is 5. Can anyone help me
> with
> >> that? can anyone describe me why 's that delay?

> >> What is going wrong. The machines was clean before the
> SQL
> >> Server Installations and they followed the same
> >> Installation Plan.

> >> Also the machine with the SQLServer 2000 has More RAM ,
> >> Disk Space and a better Proccessor

> >> Regards

> >.

 
 
 

SQL 2000 vs SQL 7.0 Performance

Post by TFlee555 » Sat, 24 Nov 2001 10:54:19


We are experiencing a similar problem with SQL2000 and a diffence between WIN2k
and NT40 clients.

My sympton has to do with packet size. When doing a query using WIN2K, about
8000 packets come accross on one particular query. It is using TCPIP for a
connection.

On NT40, we are using PIPES, it only brings over 300 packets. Thus.. at bit
more traffic.

I have seen references to Q308016 which might help, we have not tried that
solution yet. It says the client needs to set the packet size.

At any rate, I do not know if this is similar to your problem or if it will
apply. I thought SQL7 and SQL2000 both had the problem.

But, maybe you are experiencing the increased number of packet traffic, we
certainly are.

 
 
 

SQL 2000 vs SQL 7.0 Performance

Post by Vasilis Mazaraki » Sun, 25 Nov 2001 00:41:17


I know that because I made a clean setup posting only my
database on the new SQL Server and the problem remained.
That was the reason I was setup again this server (Clean
Windows 2000 Server Installation and SQL Server 2000 SP1)
We got here 2 Machines with about the same config having
the same problem. Two SQL 2000 Servers , 2 Win2000 Server
Machines The same problem exactly. BUT on an SQL Server
7.0 that we have now the database until we find a solution
and on the old database (The database we use now came from
an 6.5 SQL Server machine) things are perfect!!!!

My thought is to replace all SQL Server 2000 I have with
SQL Server 7.0.
P.S. After executing this type of Query the Memory exceeds
the limit of 512 MB my server has and never returns back
to the normal limit. This is not happpening on SQL 7.0.

I must confess that the database contains a huge amount of
data

 
 
 

SQL 2000 vs SQL 7.0 Performance

Post by Andrew J. Kell » Sun, 25 Nov 2001 07:06:51


When you load the db on the new SQL 2000 machine make sure you update the
statistics (sp_updatestatistics).  Also sql server will grab all the ram it
can get if it thinks it can use it and it won't release it unless the OS
calls for it. It will then grab it again as soon as the OS is done.  This is
by design.  If you only have 512MB and it is grabbing all of it you may want
to set an upper limit on the sql memory slightly less than the full amount.

--
Andrew J. Kelly,   SQL Server MVP


Quote:> I know that because I made a clean setup posting only my
> database on the new SQL Server and the problem remained.
> That was the reason I was setup again this server (Clean
> Windows 2000 Server Installation and SQL Server 2000 SP1)
> We got here 2 Machines with about the same config having
> the same problem. Two SQL 2000 Servers , 2 Win2000 Server
> Machines The same problem exactly. BUT on an SQL Server
> 7.0 that we have now the database until we find a solution
> and on the old database (The database we use now came from
> an 6.5 SQL Server machine) things are perfect!!!!

> My thought is to replace all SQL Server 2000 I have with
> SQL Server 7.0.
> P.S. After executing this type of Query the Memory exceeds
> the limit of 512 MB my server has and never returns back
> to the normal limit. This is not happpening on SQL 7.0.

> I must confess that the database contains a huge amount of
> data