Performance of SQL2k5 vs SQL2k

I have SQL 2k5 RTM Developer Edition. Today I started to compare performance
of SQL2k vs performance of SQL2k5. For several of tests I have seen 35%
increase of performance of SQL2k5. But for most of tests, SQL2k5 have 60%
worse performance in comparision with SQL2k. The tests is simply simulate
load of our production database. It is call of stored procedures which do
inserts/update/deletes. Why in most of tests SQL2k5 have worse performance?
I
restored database from backup, updated statistics, rebuilded indices.
Additionally - I see what average CPU usage of SQL2k is near 90%, and SQL2k5
is 50%. Why? Same computer. When I do tests with SQL2k, SQL2k5 is stopped.
And vise versa. Computer - Intel Pentium 3.2 Ghz, dual core. 1 IDE disk,
where located OS, log and data. Default trace disabled.Hi,
How are you comparing speed? Have you checked the query plans and see how
they compared?
kind regards
Greg O
--
Looking to use CLR in SQL 2005. Try some pre-build CLR Functions and SP
AGS SQL 2005 Utilities, over 20+ functions
http://www.ag-software.com/?tabid=38
"andsm" <andsm@.discussions.microsoft.com> wrote in message
news:CC8320FE-BF12-4368-B7D0-AAE15B19FE72@.microsoft.com...
>I have SQL 2k5 RTM Developer Edition. Today I started to compare
>performance
> of SQL2k vs performance of SQL2k5. For several of tests I have seen 35%
> increase of performance of SQL2k5. But for most of tests, SQL2k5 have 60%
> worse performance in comparision with SQL2k. The tests is simply simulate
> load of our production database. It is call of stored procedures which do
> inserts/update/deletes. Why in most of tests SQL2k5 have worse
> performance? I
> restored database from backup, updated statistics, rebuilded indices.
> Additionally - I see what average CPU usage of SQL2k is near 90%, and
> SQL2k5
> is 50%. Why? Same computer. When I do tests with SQL2k, SQL2k5 is stopped.
> And vise versa. Computer - Intel Pentium 3.2 Ghz, dual core. 1 IDE disk,
> where located OS, log and data. Default trace disabled.|||You are trying to do performance tests on a dual core Pentium with a single
IDE drive with everything on that same drive? I would look at the disks as a
bottleneck first.
Andrew J. Kelly SQL MVP
"andsm" <andsm@.discussions.microsoft.com> wrote in message
news:CC8320FE-BF12-4368-B7D0-AAE15B19FE72@.microsoft.com...
>I have SQL 2k5 RTM Developer Edition. Today I started to compare
>performance
> of SQL2k vs performance of SQL2k5. For several of tests I have seen 35%
> increase of performance of SQL2k5. But for most of tests, SQL2k5 have 60%
> worse performance in comparision with SQL2k. The tests is simply simulate
> load of our production database. It is call of stored procedures which do
> inserts/update/deletes. Why in most of tests SQL2k5 have worse
> performance? I
> restored database from backup, updated statistics, rebuilded indices.
> Additionally - I see what average CPU usage of SQL2k is near 90%, and
> SQL2k5
> is 50%. Why? Same computer. When I do tests with SQL2k, SQL2k5 is stopped.
> And vise versa. Computer - Intel Pentium 3.2 Ghz, dual core. 1 IDE disk,
> where located OS, log and data. Default trace disabled.|||Agree!
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eflnySL7FHA.3588@.TK2MSFTNGP15.phx.gbl...
> You are trying to do performance tests on a dual core Pentium with a
> single IDE drive with everything on that same drive? I would look at the
> disks as a bottleneck first.
>
> --
> Andrew J. Kelly SQL MVP
>
> "andsm" <andsm@.discussions.microsoft.com> wrote in message
> news:CC8320FE-BF12-4368-B7D0-AAE15B19FE72@.microsoft.com...
>|||Well, actually I do not benchmark tests, but preparations for benchmark test
s
- writing scripts, test programs etc. In December we will run the benchmark
tests on good hardware, and with EMC CX500 as IO system. Depending on result
s
of the tests, possibly will be started project to move database from SQL2k t
o
SQL2k5. But if SQL2k5 will not show better performance in the tests, no
chances what we will migrate from SQL2k to SQL2k5. And first tests from dual
core Pentium with a single IDE drive is not show good results. Ok, IO system
most likely bottleneck here. But why it is less bottleneck for SQL2k?
"Andrew J. Kelly" wrote:

> You are trying to do performance tests on a dual core Pentium with a singl
e
> IDE drive with everything on that same drive? I would look at the disks as
a
> bottleneck first.
>
> --
> Andrew J. Kelly SQL MVP
>
> "andsm" <andsm@.discussions.microsoft.com> wrote in message
> news:CC8320FE-BF12-4368-B7D0-AAE15B19FE72@.microsoft.com...
>
>|||I run scripts from several threads, from 1 to 20 threads. And I measure time
from start of the tests till end of the tests. Additionally I measure number
of long transactions - production our systems is very performance sensitive.
Long transaction - it is transaction with duration much more than some
threshold. I measured number of transaction with duration above 50 ms in the
tests. Our production system (SQL2k) currently have more than 99% of
transactions with duration below 50 ms. I see longer execution time and more
long transactions in SQL2k5 in comparison with SQL2k. Note what is my tests,
in case of one thread, total execution time of tests in SQL2k5 was ~60% wors
e
than SQL2k. In case of 20 threads, it was about same time, with very little
advantage of SQL2k5. So in most of tests, SQL2k5 had worse performance.
"GregO" wrote:

> Hi,
> How are you comparing speed? Have you checked the query plans and see how
> they compared?
> kind regards
> Greg O
> --
> Looking to use CLR in SQL 2005. Try some pre-build CLR Functions and SP
> AGS SQL 2005 Utilities, over 20+ functions
> http://www.ag-software.com/?tabid=38
> "andsm" <andsm@.discussions.microsoft.com> wrote in message
> news:CC8320FE-BF12-4368-B7D0-AAE15B19FE72@.microsoft.com...
>
>|||Start with the execution plans as suggested by Greg.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"andsm" <andsm@.discussions.microsoft.com> wrote in message
news:CC8320FE-BF12-4368-B7D0-AAE15B19FE72@.microsoft.com...
>I have SQL 2k5 RTM Developer Edition. Today I started to compare performanc
e
> of SQL2k vs performance of SQL2k5. For several of tests I have seen 35%
> increase of performance of SQL2k5. But for most of tests, SQL2k5 have 60%
> worse performance in comparision with SQL2k. The tests is simply simulate
> load of our production database. It is call of stored procedures which do
> inserts/update/deletes. Why in most of tests SQL2k5 have worse performance
? I
> restored database from backup, updated statistics, rebuilded indices.
> Additionally - I see what average CPU usage of SQL2k is near 90%, and SQL2
k5
> is 50%. Why? Same computer. When I do tests with SQL2k, SQL2k5 is stopped.
> And vise versa. Computer - Intel Pentium 3.2 Ghz, dual core. 1 IDE disk,
> where located OS, log and data. Default trace disabled.|||Hi
Ok, so you have a deve version of SQL 2005 what very of SQL 2000 do you
have. Are they configured the same? In terms of memory settings and mode ?
The next thing I would look at is not times because time can be miss
leading. While times are important to end users you need to see what SQL is
doing to cause these time. I would be looking at reads and query plans and
seeing what the difference is in them. If SQL 200 is doing less read
(physical or logical ) then SQL 2005 then you would think the query plan and
therefore the index usage is different. I understand that you restored the
DB but there can still be differences in what he optimiser does. If the
reads are the same and the plans are the same then it is the transport
medium that is different. If the transport layer is the same and the plans
are the same as well as the IO then yes MS has stuffed up. But just looking
at the number of tranactions over 50ms is not going to give you the tru
impression of whats going on.
Now you said that you had 20 threads does anyone know if dev of SQL 2005 has
a connection limit?
kind regards
Greg O
"andsm" <andsm@.discussions.microsoft.com> wrote in message
news:B1D8A275-BCC5-4D44-BCCF-1941765C9458@.microsoft.com...[vbcol=seagreen]
>I run scripts from several threads, from 1 to 20 threads. And I measure
>time
> from start of the tests till end of the tests. Additionally I measure
> number
> of long transactions - production our systems is very performance
> sensitive.
> Long transaction - it is transaction with duration much more than some
> threshold. I measured number of transaction with duration above 50 ms in
> the
> tests. Our production system (SQL2k) currently have more than 99% of
> transactions with duration below 50 ms. I see longer execution time and
> more
> long transactions in SQL2k5 in comparison with SQL2k. Note what is my
> tests,
> in case of one thread, total execution time of tests in SQL2k5 was ~60%
> worse
> than SQL2k. In case of 20 threads, it was about same time, with very
> little
> advantage of SQL2k5. So in most of tests, SQL2k5 had worse performance.
> "GregO" wrote:
>|||"andsm" <andsm@.discussions.microsoft.com> wrote in message
news:7C1D93E4-2724-4582-AA9F-22CA290E3D04@.microsoft.com...
> Well, actually I do not benchmark tests, but preparations for benchmark
> tests
> - writing scripts, test programs etc. In December we will run the
> benchmark
> tests on good hardware, and with EMC CX500 as IO system. Depending on
> results
> of the tests, possibly will be started project to move database from SQL2k
> to
> SQL2k5. But if SQL2k5 will not show better performance in the tests, no
> chances what we will migrate from SQL2k to SQL2k5. And first tests from
> dual
> core Pentium with a single IDE drive is not show good results. Ok, IO
> system
> most likely bottleneck here. But why it is less bottleneck for SQL2k?
>
One thing is that with SQL2005 is _much_ easier to spot and resolve
bottlenecks in a workload. With SQL2000 you had to run a trace and do a lot
of analysis to find your "top 10" queries by Total CPU, Average CPU, Total
Reads and Average Reads. With 2005 these are built-in reports in SQL Server
Management Studio. The Database Engine Tuning Adviser is great, and there
are many new features for tuning an application.
For tuning when you can change the queries:
-Common Table Expressions
-OUTPUT clause
-Ranking functions
-CLR Procs and Functions
-APPLY operator
For tuning when you cannot change the queries:
-indexes with included columns (these are awesome!!),
-improvements to Indexed Views,
-query plan guides.
So, if the baseline performance is close I would much rather have SQL2005
because it's so much easier to analyze and improve the performance.
David|||SQL 2005 is different than 2000 in many ways. For instance tempdb is used a
lot more in 2005 than 2000 so it is very important to ensure tempdb is on a
disk subsystem that is properly configured to handle the intended load. This
is true of coarse for all the data and log files as well. It is useless to
do any testing and make any decisions on hardware that is not what you will
be running on in the end.
Andrew J. Kelly SQL MVP
"andsm" <andsm@.discussions.microsoft.com> wrote in message
news:7C1D93E4-2724-4582-AA9F-22CA290E3D04@.microsoft.com...[vbcol=seagreen]
> Well, actually I do not benchmark tests, but preparations for benchmark
> tests
> - writing scripts, test programs etc. In December we will run the
> benchmark
> tests on good hardware, and with EMC CX500 as IO system. Depending on
> results
> of the tests, possibly will be started project to move database from SQL2k
> to
> SQL2k5. But if SQL2k5 will not show better performance in the tests, no
> chances what we will migrate from SQL2k to SQL2k5. And first tests from
> dual
> core Pentium with a single IDE drive is not show good results. Ok, IO
> system
> most likely bottleneck here. But why it is less bottleneck for SQL2k?
> "Andrew J. Kelly" wrote:
>