Friday, February 24, 2012

2005 slower than 2000?

Admittedly I'm evaluating 2005 in a virtual machine but I'm comparing it to
an older machine:
2005 thinks it has 1Gb and 1 cpu on a host which has 2GB, 2 2.4Ghz Amd64's,
raid0. it runs in boosted priority when active.
2000 is on an older single Amd64 2Ghz, 1Gb, single sata drive.
A very ugly query built by selections on a web page is parsed and
parameterized.
on 2000 it runs in a couple seconds against a few thousand rows joining lots
of tables, varying by the selections made.
on 2005 it takes twice as long for the identical query. all other software
variables are identical. the db is a copy from 2000. stats updated. tried
with both compatibility levels.
I'm thinking I'll wait for 9.1 or 9.2 before I make the move.br (br@.discussions.microsoft.com) writes:
> Admittedly I'm evaluating 2005 in a virtual machine but I'm comparing it
> to an older machine:
> 2005 thinks it has 1Gb and 1 cpu on a host which has 2GB, 2 2.4Ghz
> Amd64's, raid0. it runs in boosted priority when active. 2000 is on an
> older single Amd64 2Ghz, 1Gb, single sata drive.
> A very ugly query built by selections on a web page is parsed and
> parameterized. on 2000 it runs in a couple seconds against a few
> thousand rows joining lots of tables, varying by the selections made.
> on 2005 it takes twice as long for the identical query. all other
> software variables are identical. the db is a copy from 2000. stats
> updated. tried with both compatibility levels.
> I'm thinking I'll wait for 9.1 or 9.2 before I make the move.
Before you do that... The database on SQL 2005, is that a copy of the
database from SQL 2000? In such case, did you run UPDATE STATISTICS
WITH FULLSCAN on all table (or just sp_updatestats if you prefer)? When
you restore a database from SQL 2000, all statistics are invalidated,
this can be an important reason for difference in performance.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Did you install SQL Server Standard/Enterprise Edition or Express Edition?
Express edition is limited by design to 1 CPU, 1 GB RAM, and 4 GB database
size.
There are server and database configuration settings that can impact
performance:
http://www.sql-server-performance.c...ance_audit5.asp
Analyze the execution plan and performance monitor logs to determine what
exactly is going on.
http://msdn.microsoft.com/library/d... />
1_5pde.asp
http://www.informit.com/guides/cont...&seqNum=28&rl=1
BTY, what is "9.1 or 9.2" ?
"br" <br@.discussions.microsoft.com> wrote in message
news:2C96E892-1713-4486-8DE6-B0E56AEF29C3@.microsoft.com...
> Admittedly I'm evaluating 2005 in a virtual machine but I'm comparing it
> to
> an older machine:
> 2005 thinks it has 1Gb and 1 cpu on a host which has 2GB, 2 2.4Ghz
> Amd64's,
> raid0. it runs in boosted priority when active.
> 2000 is on an older single Amd64 2Ghz, 1Gb, single sata drive.
> A very ugly query built by selections on a web page is parsed and
> parameterized.
> on 2000 it runs in a couple seconds against a few thousand rows joining
> lots
> of tables, varying by the selections made.
> on 2005 it takes twice as long for the identical query. all other software
> variables are identical. the db is a copy from 2000. stats updated. tried
> with both compatibility levels.
> I'm thinking I'll wait for 9.1 or 9.2 before I make the move.
>|||Thanks guys,
It's the developer version. Roughly equivalent to standard.
The stats are updated.
I'm figuring I'll have to re-optimize for 2005, so I don't mind waiting for
a fix rev or two.
But thanks for the links. I'll check them out.
"JT" wrote:

> Did you install SQL Server Standard/Enterprise Edition or Express Edition?
> Express edition is limited by design to 1 CPU, 1 GB RAM, and 4 GB database
> size.
> There are server and database configuration settings that can impact
> performance:
> http://www.sql-server-performance.c...ance_audit5.asp
> Analyze the execution plan and performance monitor logs to determine what
> exactly is going on.
> http://msdn.microsoft.com/library/d...>
n_1_5pde.asp
> http://www.informit.com/guides/cont...&seqNum=28&rl=1
> BTY, what is "9.1 or 9.2" ?
> "br" <br@.discussions.microsoft.com> wrote in message
> news:2C96E892-1713-4486-8DE6-B0E56AEF29C3@.microsoft.com...
>
>|||Sorry, missed that. Means one or two revisions to Sql2005, which I'm
considering to be Sql Server 9.
"JT" wrote:
> BTY, what is "9.1 or 9.2" ?|||Try testing again in an environment other than a virtual machine (MS Virtual
PC ?). There may be VM session settings limiting RAM and CPU usage. Also,
SQL Server is optimized to work directly with the OS and hardware, and a VM
session is an abstraction layer.
"br" <br@.discussions.microsoft.com> wrote in message
news:9C34254A-E671-4E1D-84BF-E586D5A74830@.microsoft.com...
> Thanks guys,
> It's the developer version. Roughly equivalent to standard.
> The stats are updated.
> I'm figuring I'll have to re-optimize for 2005, so I don't mind waiting
> for
> a fix rev or two.
> But thanks for the links. I'll check them out.
> "JT" wrote:
>|||There are a lot of people using 2005 today with no broad based complaints
about performance. You are perhaps thinking that a future service pack will
include performance optimizations for a vitual machine environment? ;-)
"br" <br@.discussions.microsoft.com> wrote in message
news:4A3878EB-4E4B-491F-9C3B-5A81F2FC8510@.microsoft.com...
> Sorry, missed that. Means one or two revisions to Sql2005, which I'm
> considering to be Sql Server 9.
> "JT" wrote:
>|||True... ok, some wend when I have a free morning I'll put it on a real
machine.
"JT" wrote:

> ...a VM session is an abstraction layer.
>

No comments:

Post a Comment