I did several tests of performance of SQL2k5, and I see quite strange result
s.
I restored database from SQL2k, rebuilded indexes, updated statistics. I did
all what described in migration process. Backuped the resulting database. In
each of next tests I restored the database from backup. Next I run tests.
Since our application, OLTP, is very performance sensitive, we need to have
good distribution of transaction execution time. In case if I run tests on
SQL2k5, when snapshot isolation level is not turned on, I see good
distribution - less than 0.5% of transactions executing more than 200ms,
average execution time is ~5 ms. In case if I change compatibility level to
90 and turn on allow_snapshot_isolation, behavior is dramatically changing.
Near 5% of transactions start to execute more than 200 ms, average execution
time is also significally increasing. There are no transactions which are
really use snapshot isolation level, stored procedures are exacly same as
before. There is no any memory pressure, no IO bottlenecks. During the tests
counters showed nearby 1 GB of free memory. IO system - EMC CX500. Data on
5x73GB FC 15kRPM drives (RAID0), log on 3 drives (RAID0). Database have 2 GB
size, but only ~200 MB of the data was read in the tests. 1.2GB write cache
-
it means what all writing during the test was done only to write cache on
CX500, tests were not so long to fill the entire write cache. Windows 2003
x64 Enterprise Edition, SQL2k5 x64 Developer Edition.
CPU load was ~50% in tests without snapshot isolation level, and ~70% with
tests with snapshot level. 2CPU.
From the test it looks like I can not remove replication to QueryDB and run
queries on same computer where OLTP load...
Why impact of snapshot isolation level is so big?As soon as you change the isolation level to either Snapshot Isolation or
Read Committed Snapshot, SQL Server will start saving ALL updated data in
the version store in tempdb, whether or not any queries every read the
versioned data. So every changed row will have additional overhead
associated with it.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"andsm" <andsm@.discussions.microsoft.com> wrote in message
news:B12BA2E7-8639-4DE6-9113-75F56E6BAB7C@.microsoft.com...
>I did several tests of performance of SQL2k5, and I see quite strange
>results.
> I restored database from SQL2k, rebuilded indexes, updated statistics. I
> did
> all what described in migration process. Backuped the resulting database.
> In
> each of next tests I restored the database from backup. Next I run tests.
> Since our application, OLTP, is very performance sensitive, we need to
> have
> good distribution of transaction execution time. In case if I run tests on
> SQL2k5, when snapshot isolation level is not turned on, I see good
> distribution - less than 0.5% of transactions executing more than 200ms,
> average execution time is ~5 ms. In case if I change compatibility level
> to
> 90 and turn on allow_snapshot_isolation, behavior is dramatically
> changing.
> Near 5% of transactions start to execute more than 200 ms, average
> execution
> time is also significally increasing. There are no transactions which are
> really use snapshot isolation level, stored procedures are exacly same as
> before. There is no any memory pressure, no IO bottlenecks. During the
> tests
> counters showed nearby 1 GB of free memory. IO system - EMC CX500. Data on
> 5x73GB FC 15kRPM drives (RAID0), log on 3 drives (RAID0). Database have 2
> GB
> size, but only ~200 MB of the data was read in the tests. 1.2GB write
> cache -
> it means what all writing during the test was done only to write cache on
> CX500, tests were not so long to fill the entire write cache. Windows 2003
> x64 Enterprise Edition, SQL2k5 x64 Developer Edition.
> CPU load was ~50% in tests without snapshot isolation level, and ~70% with
> tests with snapshot level. 2CPU.
> From the test it looks like I can not remove replication to QueryDB and
> run
> queries on same computer where OLTP load...
> Why impact of snapshot isolation level is so big?
>|||I understand what snapshot isolation adds some overhead, but I expected
lesser overhead. CPU consumption near 1.4 times more in case if snapshot
option turned on. It means what for most of performance sensitive OLTP
applications, which currently use SQL2k and which use replication to another
database used for longer queries, can not remove replication and run queries
on same server where main OLTP load after migration to SQL2k5. Quite
disappointing...
"Kalen Delaney" wrote:
> As soon as you change the isolation level to either Snapshot Isolation or
> Read Committed Snapshot, SQL Server will start saving ALL updated data in
> the version store in tempdb, whether or not any queries every read the
> versioned data. So every changed row will have additional overhead
> associated with it.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "andsm" <andsm@.discussions.microsoft.com> wrote in message
> news:B12BA2E7-8639-4DE6-9113-75F56E6BAB7C@.microsoft.com...
>
>|||There is a lot to consider with this. Snapshot isolation or read committed
snapshot write every single transaction into the version store. Since the
version store is in tempdb, in a high volume environment, you are in effect
pounding tempdb with every single transaction you issue. These are intended
to be applied when you have situations when you do not want reads to block
writes, BUT there is a performance trade-off when you implement that
functionality.
There are a couple of things you can do to minimize the impact of the
version store overhead. First, you need to move tempdb to its own disk
device so that it doesn't have to compete with everything else. Secondly,
you can add additional files (equal to the nmber of processors) to tempdb
that are of equal size in order to stripe the writes. BUT, you are still
going to have a performance impact.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"andsm" <andsm@.discussions.microsoft.com> wrote in message
news:C3C9D444-5CD1-4C9A-9F2F-4BCD5DF26019@.microsoft.com...[vbcol=seagreen]
>I understand what snapshot isolation adds some overhead, but I expected
> lesser overhead. CPU consumption near 1.4 times more in case if snapshot
> option turned on. It means what for most of performance sensitive OLTP
> applications, which currently use SQL2k and which use replication to
> another
> database used for longer queries, can not remove replication and run
> queries
> on same server where main OLTP load after migration to SQL2k5. Quite
> disappointing...
> "Kalen Delaney" wrote:
>
Showing posts with label sql2k5. Show all posts
Showing posts with label sql2k5. Show all posts
Friday, March 9, 2012
Performance of SQL2k5 with snapshot isolation level turned on
I did several tests of performance of SQL2k5, and I see quite strange results.
I restored database from SQL2k, rebuilded indexes, updated statistics. I did
all what described in migration process. Backuped the resulting database. In
each of next tests I restored the database from backup. Next I run tests.
Since our application, OLTP, is very performance sensitive, we need to have
good distribution of transaction execution time. In case if I run tests on
SQL2k5, when snapshot isolation level is not turned on, I see good
distribution - less than 0.5% of transactions executing more than 200ms,
average execution time is ~5 ms. In case if I change compatibility level to
90 and turn on allow_snapshot_isolation, behavior is dramatically changing.
Near 5% of transactions start to execute more than 200 ms, average execution
time is also significally increasing. There are no transactions which are
really use snapshot isolation level, stored procedures are exacly same as
before. There is no any memory pressure, no IO bottlenecks. During the tests
counters showed nearby 1 GB of free memory. IO system - EMC CX500. Data on
5x73GB FC 15kRPM drives (RAID0), log on 3 drives (RAID0). Database have 2 GB
size, but only ~200 MB of the data was read in the tests. 1.2GB write cache -
it means what all writing during the test was done only to write cache on
CX500, tests were not so long to fill the entire write cache. Windows 2003
x64 Enterprise Edition, SQL2k5 x64 Developer Edition.
CPU load was ~50% in tests without snapshot isolation level, and ~70% with
tests with snapshot level. 2CPU.
From the test it looks like I can not remove replication to QueryDB and run
queries on same computer where OLTP load...
Why impact of snapshot isolation level is so big?
As soon as you change the isolation level to either Snapshot Isolation or
Read Committed Snapshot, SQL Server will start saving ALL updated data in
the version store in tempdb, whether or not any queries every read the
versioned data. So every changed row will have additional overhead
associated with it.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"andsm" <andsm@.discussions.microsoft.com> wrote in message
news:B12BA2E7-8639-4DE6-9113-75F56E6BAB7C@.microsoft.com...
>I did several tests of performance of SQL2k5, and I see quite strange
>results.
> I restored database from SQL2k, rebuilded indexes, updated statistics. I
> did
> all what described in migration process. Backuped the resulting database.
> In
> each of next tests I restored the database from backup. Next I run tests.
> Since our application, OLTP, is very performance sensitive, we need to
> have
> good distribution of transaction execution time. In case if I run tests on
> SQL2k5, when snapshot isolation level is not turned on, I see good
> distribution - less than 0.5% of transactions executing more than 200ms,
> average execution time is ~5 ms. In case if I change compatibility level
> to
> 90 and turn on allow_snapshot_isolation, behavior is dramatically
> changing.
> Near 5% of transactions start to execute more than 200 ms, average
> execution
> time is also significally increasing. There are no transactions which are
> really use snapshot isolation level, stored procedures are exacly same as
> before. There is no any memory pressure, no IO bottlenecks. During the
> tests
> counters showed nearby 1 GB of free memory. IO system - EMC CX500. Data on
> 5x73GB FC 15kRPM drives (RAID0), log on 3 drives (RAID0). Database have 2
> GB
> size, but only ~200 MB of the data was read in the tests. 1.2GB write
> cache -
> it means what all writing during the test was done only to write cache on
> CX500, tests were not so long to fill the entire write cache. Windows 2003
> x64 Enterprise Edition, SQL2k5 x64 Developer Edition.
> CPU load was ~50% in tests without snapshot isolation level, and ~70% with
> tests with snapshot level. 2CPU.
> From the test it looks like I can not remove replication to QueryDB and
> run
> queries on same computer where OLTP load...
> Why impact of snapshot isolation level is so big?
>
|||I understand what snapshot isolation adds some overhead, but I expected
lesser overhead. CPU consumption near 1.4 times more in case if snapshot
option turned on. It means what for most of performance sensitive OLTP
applications, which currently use SQL2k and which use replication to another
database used for longer queries, can not remove replication and run queries
on same server where main OLTP load after migration to SQL2k5. Quite
disappointing...
"Kalen Delaney" wrote:
> As soon as you change the isolation level to either Snapshot Isolation or
> Read Committed Snapshot, SQL Server will start saving ALL updated data in
> the version store in tempdb, whether or not any queries every read the
> versioned data. So every changed row will have additional overhead
> associated with it.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "andsm" <andsm@.discussions.microsoft.com> wrote in message
> news:B12BA2E7-8639-4DE6-9113-75F56E6BAB7C@.microsoft.com...
>
>
|||There is a lot to consider with this. Snapshot isolation or read committed
snapshot write every single transaction into the version store. Since the
version store is in tempdb, in a high volume environment, you are in effect
pounding tempdb with every single transaction you issue. These are intended
to be applied when you have situations when you do not want reads to block
writes, BUT there is a performance trade-off when you implement that
functionality.
There are a couple of things you can do to minimize the impact of the
version store overhead. First, you need to move tempdb to its own disk
device so that it doesn't have to compete with everything else. Secondly,
you can add additional files (equal to the nmber of processors) to tempdb
that are of equal size in order to stripe the writes. BUT, you are still
going to have a performance impact.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"andsm" <andsm@.discussions.microsoft.com> wrote in message
news:C3C9D444-5CD1-4C9A-9F2F-4BCD5DF26019@.microsoft.com...[vbcol=seagreen]
>I understand what snapshot isolation adds some overhead, but I expected
> lesser overhead. CPU consumption near 1.4 times more in case if snapshot
> option turned on. It means what for most of performance sensitive OLTP
> applications, which currently use SQL2k and which use replication to
> another
> database used for longer queries, can not remove replication and run
> queries
> on same server where main OLTP load after migration to SQL2k5. Quite
> disappointing...
> "Kalen Delaney" wrote:
I restored database from SQL2k, rebuilded indexes, updated statistics. I did
all what described in migration process. Backuped the resulting database. In
each of next tests I restored the database from backup. Next I run tests.
Since our application, OLTP, is very performance sensitive, we need to have
good distribution of transaction execution time. In case if I run tests on
SQL2k5, when snapshot isolation level is not turned on, I see good
distribution - less than 0.5% of transactions executing more than 200ms,
average execution time is ~5 ms. In case if I change compatibility level to
90 and turn on allow_snapshot_isolation, behavior is dramatically changing.
Near 5% of transactions start to execute more than 200 ms, average execution
time is also significally increasing. There are no transactions which are
really use snapshot isolation level, stored procedures are exacly same as
before. There is no any memory pressure, no IO bottlenecks. During the tests
counters showed nearby 1 GB of free memory. IO system - EMC CX500. Data on
5x73GB FC 15kRPM drives (RAID0), log on 3 drives (RAID0). Database have 2 GB
size, but only ~200 MB of the data was read in the tests. 1.2GB write cache -
it means what all writing during the test was done only to write cache on
CX500, tests were not so long to fill the entire write cache. Windows 2003
x64 Enterprise Edition, SQL2k5 x64 Developer Edition.
CPU load was ~50% in tests without snapshot isolation level, and ~70% with
tests with snapshot level. 2CPU.
From the test it looks like I can not remove replication to QueryDB and run
queries on same computer where OLTP load...
Why impact of snapshot isolation level is so big?
As soon as you change the isolation level to either Snapshot Isolation or
Read Committed Snapshot, SQL Server will start saving ALL updated data in
the version store in tempdb, whether or not any queries every read the
versioned data. So every changed row will have additional overhead
associated with it.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"andsm" <andsm@.discussions.microsoft.com> wrote in message
news:B12BA2E7-8639-4DE6-9113-75F56E6BAB7C@.microsoft.com...
>I did several tests of performance of SQL2k5, and I see quite strange
>results.
> I restored database from SQL2k, rebuilded indexes, updated statistics. I
> did
> all what described in migration process. Backuped the resulting database.
> In
> each of next tests I restored the database from backup. Next I run tests.
> Since our application, OLTP, is very performance sensitive, we need to
> have
> good distribution of transaction execution time. In case if I run tests on
> SQL2k5, when snapshot isolation level is not turned on, I see good
> distribution - less than 0.5% of transactions executing more than 200ms,
> average execution time is ~5 ms. In case if I change compatibility level
> to
> 90 and turn on allow_snapshot_isolation, behavior is dramatically
> changing.
> Near 5% of transactions start to execute more than 200 ms, average
> execution
> time is also significally increasing. There are no transactions which are
> really use snapshot isolation level, stored procedures are exacly same as
> before. There is no any memory pressure, no IO bottlenecks. During the
> tests
> counters showed nearby 1 GB of free memory. IO system - EMC CX500. Data on
> 5x73GB FC 15kRPM drives (RAID0), log on 3 drives (RAID0). Database have 2
> GB
> size, but only ~200 MB of the data was read in the tests. 1.2GB write
> cache -
> it means what all writing during the test was done only to write cache on
> CX500, tests were not so long to fill the entire write cache. Windows 2003
> x64 Enterprise Edition, SQL2k5 x64 Developer Edition.
> CPU load was ~50% in tests without snapshot isolation level, and ~70% with
> tests with snapshot level. 2CPU.
> From the test it looks like I can not remove replication to QueryDB and
> run
> queries on same computer where OLTP load...
> Why impact of snapshot isolation level is so big?
>
|||I understand what snapshot isolation adds some overhead, but I expected
lesser overhead. CPU consumption near 1.4 times more in case if snapshot
option turned on. It means what for most of performance sensitive OLTP
applications, which currently use SQL2k and which use replication to another
database used for longer queries, can not remove replication and run queries
on same server where main OLTP load after migration to SQL2k5. Quite
disappointing...
"Kalen Delaney" wrote:
> As soon as you change the isolation level to either Snapshot Isolation or
> Read Committed Snapshot, SQL Server will start saving ALL updated data in
> the version store in tempdb, whether or not any queries every read the
> versioned data. So every changed row will have additional overhead
> associated with it.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "andsm" <andsm@.discussions.microsoft.com> wrote in message
> news:B12BA2E7-8639-4DE6-9113-75F56E6BAB7C@.microsoft.com...
>
>
|||There is a lot to consider with this. Snapshot isolation or read committed
snapshot write every single transaction into the version store. Since the
version store is in tempdb, in a high volume environment, you are in effect
pounding tempdb with every single transaction you issue. These are intended
to be applied when you have situations when you do not want reads to block
writes, BUT there is a performance trade-off when you implement that
functionality.
There are a couple of things you can do to minimize the impact of the
version store overhead. First, you need to move tempdb to its own disk
device so that it doesn't have to compete with everything else. Secondly,
you can add additional files (equal to the nmber of processors) to tempdb
that are of equal size in order to stripe the writes. BUT, you are still
going to have a performance impact.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"andsm" <andsm@.discussions.microsoft.com> wrote in message
news:C3C9D444-5CD1-4C9A-9F2F-4BCD5DF26019@.microsoft.com...[vbcol=seagreen]
>I understand what snapshot isolation adds some overhead, but I expected
> lesser overhead. CPU consumption near 1.4 times more in case if snapshot
> option turned on. It means what for most of performance sensitive OLTP
> applications, which currently use SQL2k and which use replication to
> another
> database used for longer queries, can not remove replication and run
> queries
> on same server where main OLTP load after migration to SQL2k5. Quite
> disappointing...
> "Kalen Delaney" wrote:
Performance of SQL2k5 with snapshot isolation level turned on
I did several tests of performance of SQL2k5, and I see quite strange results.
I restored database from SQL2k, rebuilded indexes, updated statistics. I did
all what described in migration process. Backuped the resulting database. In
each of next tests I restored the database from backup. Next I run tests.
Since our application, OLTP, is very performance sensitive, we need to have
good distribution of transaction execution time. In case if I run tests on
SQL2k5, when snapshot isolation level is not turned on, I see good
distribution - less than 0.5% of transactions executing more than 200ms,
average execution time is ~5 ms. In case if I change compatibility level to
90 and turn on allow_snapshot_isolation, behavior is dramatically changing.
Near 5% of transactions start to execute more than 200 ms, average execution
time is also significally increasing. There are no transactions which are
really use snapshot isolation level, stored procedures are exacly same as
before. There is no any memory pressure, no IO bottlenecks. During the tests
counters showed nearby 1 GB of free memory. IO system - EMC CX500. Data on
5x73GB FC 15kRPM drives (RAID0), log on 3 drives (RAID0). Database have 2 GB
size, but only ~200 MB of the data was read in the tests. 1.2GB write cache -
it means what all writing during the test was done only to write cache on
CX500, tests were not so long to fill the entire write cache. Windows 2003
x64 Enterprise Edition, SQL2k5 x64 Developer Edition.
CPU load was ~50% in tests without snapshot isolation level, and ~70% with
tests with snapshot level. 2CPU.
From the test it looks like I can not remove replication to QueryDB and run
queries on same computer where OLTP load...
Why impact of snapshot isolation level is so big?As soon as you change the isolation level to either Snapshot Isolation or
Read Committed Snapshot, SQL Server will start saving ALL updated data in
the version store in tempdb, whether or not any queries every read the
versioned data. So every changed row will have additional overhead
associated with it.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"andsm" <andsm@.discussions.microsoft.com> wrote in message
news:B12BA2E7-8639-4DE6-9113-75F56E6BAB7C@.microsoft.com...
>I did several tests of performance of SQL2k5, and I see quite strange
>results.
> I restored database from SQL2k, rebuilded indexes, updated statistics. I
> did
> all what described in migration process. Backuped the resulting database.
> In
> each of next tests I restored the database from backup. Next I run tests.
> Since our application, OLTP, is very performance sensitive, we need to
> have
> good distribution of transaction execution time. In case if I run tests on
> SQL2k5, when snapshot isolation level is not turned on, I see good
> distribution - less than 0.5% of transactions executing more than 200ms,
> average execution time is ~5 ms. In case if I change compatibility level
> to
> 90 and turn on allow_snapshot_isolation, behavior is dramatically
> changing.
> Near 5% of transactions start to execute more than 200 ms, average
> execution
> time is also significally increasing. There are no transactions which are
> really use snapshot isolation level, stored procedures are exacly same as
> before. There is no any memory pressure, no IO bottlenecks. During the
> tests
> counters showed nearby 1 GB of free memory. IO system - EMC CX500. Data on
> 5x73GB FC 15kRPM drives (RAID0), log on 3 drives (RAID0). Database have 2
> GB
> size, but only ~200 MB of the data was read in the tests. 1.2GB write
> cache -
> it means what all writing during the test was done only to write cache on
> CX500, tests were not so long to fill the entire write cache. Windows 2003
> x64 Enterprise Edition, SQL2k5 x64 Developer Edition.
> CPU load was ~50% in tests without snapshot isolation level, and ~70% with
> tests with snapshot level. 2CPU.
> From the test it looks like I can not remove replication to QueryDB and
> run
> queries on same computer where OLTP load...
> Why impact of snapshot isolation level is so big?
>|||I understand what snapshot isolation adds some overhead, but I expected
lesser overhead. CPU consumption near 1.4 times more in case if snapshot
option turned on. It means what for most of performance sensitive OLTP
applications, which currently use SQL2k and which use replication to another
database used for longer queries, can not remove replication and run queries
on same server where main OLTP load after migration to SQL2k5. Quite
disappointing...
"Kalen Delaney" wrote:
> As soon as you change the isolation level to either Snapshot Isolation or
> Read Committed Snapshot, SQL Server will start saving ALL updated data in
> the version store in tempdb, whether or not any queries every read the
> versioned data. So every changed row will have additional overhead
> associated with it.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "andsm" <andsm@.discussions.microsoft.com> wrote in message
> news:B12BA2E7-8639-4DE6-9113-75F56E6BAB7C@.microsoft.com...
> >I did several tests of performance of SQL2k5, and I see quite strange
> >results.
> > I restored database from SQL2k, rebuilded indexes, updated statistics. I
> > did
> > all what described in migration process. Backuped the resulting database.
> > In
> > each of next tests I restored the database from backup. Next I run tests.
> > Since our application, OLTP, is very performance sensitive, we need to
> > have
> > good distribution of transaction execution time. In case if I run tests on
> > SQL2k5, when snapshot isolation level is not turned on, I see good
> > distribution - less than 0.5% of transactions executing more than 200ms,
> > average execution time is ~5 ms. In case if I change compatibility level
> > to
> > 90 and turn on allow_snapshot_isolation, behavior is dramatically
> > changing.
> > Near 5% of transactions start to execute more than 200 ms, average
> > execution
> > time is also significally increasing. There are no transactions which are
> > really use snapshot isolation level, stored procedures are exacly same as
> > before. There is no any memory pressure, no IO bottlenecks. During the
> > tests
> > counters showed nearby 1 GB of free memory. IO system - EMC CX500. Data on
> > 5x73GB FC 15kRPM drives (RAID0), log on 3 drives (RAID0). Database have 2
> > GB
> > size, but only ~200 MB of the data was read in the tests. 1.2GB write
> > cache -
> > it means what all writing during the test was done only to write cache on
> > CX500, tests were not so long to fill the entire write cache. Windows 2003
> > x64 Enterprise Edition, SQL2k5 x64 Developer Edition.
> > CPU load was ~50% in tests without snapshot isolation level, and ~70% with
> > tests with snapshot level. 2CPU.
> > From the test it looks like I can not remove replication to QueryDB and
> > run
> > queries on same computer where OLTP load...
> > Why impact of snapshot isolation level is so big?
> >
>
>|||There is a lot to consider with this. Snapshot isolation or read committed
snapshot write every single transaction into the version store. Since the
version store is in tempdb, in a high volume environment, you are in effect
pounding tempdb with every single transaction you issue. These are intended
to be applied when you have situations when you do not want reads to block
writes, BUT there is a performance trade-off when you implement that
functionality.
There are a couple of things you can do to minimize the impact of the
version store overhead. First, you need to move tempdb to its own disk
device so that it doesn't have to compete with everything else. Secondly,
you can add additional files (equal to the nmber of processors) to tempdb
that are of equal size in order to stripe the writes. BUT, you are still
going to have a performance impact.
--
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"andsm" <andsm@.discussions.microsoft.com> wrote in message
news:C3C9D444-5CD1-4C9A-9F2F-4BCD5DF26019@.microsoft.com...
>I understand what snapshot isolation adds some overhead, but I expected
> lesser overhead. CPU consumption near 1.4 times more in case if snapshot
> option turned on. It means what for most of performance sensitive OLTP
> applications, which currently use SQL2k and which use replication to
> another
> database used for longer queries, can not remove replication and run
> queries
> on same server where main OLTP load after migration to SQL2k5. Quite
> disappointing...
> "Kalen Delaney" wrote:
>> As soon as you change the isolation level to either Snapshot Isolation or
>> Read Committed Snapshot, SQL Server will start saving ALL updated data in
>> the version store in tempdb, whether or not any queries every read the
>> versioned data. So every changed row will have additional overhead
>> associated with it.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.solidqualitylearning.com
>>
>> "andsm" <andsm@.discussions.microsoft.com> wrote in message
>> news:B12BA2E7-8639-4DE6-9113-75F56E6BAB7C@.microsoft.com...
>> >I did several tests of performance of SQL2k5, and I see quite strange
>> >results.
>> > I restored database from SQL2k, rebuilded indexes, updated statistics.
>> > I
>> > did
>> > all what described in migration process. Backuped the resulting
>> > database.
>> > In
>> > each of next tests I restored the database from backup. Next I run
>> > tests.
>> > Since our application, OLTP, is very performance sensitive, we need to
>> > have
>> > good distribution of transaction execution time. In case if I run tests
>> > on
>> > SQL2k5, when snapshot isolation level is not turned on, I see good
>> > distribution - less than 0.5% of transactions executing more than
>> > 200ms,
>> > average execution time is ~5 ms. In case if I change compatibility
>> > level
>> > to
>> > 90 and turn on allow_snapshot_isolation, behavior is dramatically
>> > changing.
>> > Near 5% of transactions start to execute more than 200 ms, average
>> > execution
>> > time is also significally increasing. There are no transactions which
>> > are
>> > really use snapshot isolation level, stored procedures are exacly same
>> > as
>> > before. There is no any memory pressure, no IO bottlenecks. During the
>> > tests
>> > counters showed nearby 1 GB of free memory. IO system - EMC CX500. Data
>> > on
>> > 5x73GB FC 15kRPM drives (RAID0), log on 3 drives (RAID0). Database have
>> > 2
>> > GB
>> > size, but only ~200 MB of the data was read in the tests. 1.2GB write
>> > cache -
>> > it means what all writing during the test was done only to write cache
>> > on
>> > CX500, tests were not so long to fill the entire write cache. Windows
>> > 2003
>> > x64 Enterprise Edition, SQL2k5 x64 Developer Edition.
>> > CPU load was ~50% in tests without snapshot isolation level, and ~70%
>> > with
>> > tests with snapshot level. 2CPU.
>> > From the test it looks like I can not remove replication to QueryDB and
>> > run
>> > queries on same computer where OLTP load...
>> > Why impact of snapshot isolation level is so big?
>> >
>>
>>
I restored database from SQL2k, rebuilded indexes, updated statistics. I did
all what described in migration process. Backuped the resulting database. In
each of next tests I restored the database from backup. Next I run tests.
Since our application, OLTP, is very performance sensitive, we need to have
good distribution of transaction execution time. In case if I run tests on
SQL2k5, when snapshot isolation level is not turned on, I see good
distribution - less than 0.5% of transactions executing more than 200ms,
average execution time is ~5 ms. In case if I change compatibility level to
90 and turn on allow_snapshot_isolation, behavior is dramatically changing.
Near 5% of transactions start to execute more than 200 ms, average execution
time is also significally increasing. There are no transactions which are
really use snapshot isolation level, stored procedures are exacly same as
before. There is no any memory pressure, no IO bottlenecks. During the tests
counters showed nearby 1 GB of free memory. IO system - EMC CX500. Data on
5x73GB FC 15kRPM drives (RAID0), log on 3 drives (RAID0). Database have 2 GB
size, but only ~200 MB of the data was read in the tests. 1.2GB write cache -
it means what all writing during the test was done only to write cache on
CX500, tests were not so long to fill the entire write cache. Windows 2003
x64 Enterprise Edition, SQL2k5 x64 Developer Edition.
CPU load was ~50% in tests without snapshot isolation level, and ~70% with
tests with snapshot level. 2CPU.
From the test it looks like I can not remove replication to QueryDB and run
queries on same computer where OLTP load...
Why impact of snapshot isolation level is so big?As soon as you change the isolation level to either Snapshot Isolation or
Read Committed Snapshot, SQL Server will start saving ALL updated data in
the version store in tempdb, whether or not any queries every read the
versioned data. So every changed row will have additional overhead
associated with it.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"andsm" <andsm@.discussions.microsoft.com> wrote in message
news:B12BA2E7-8639-4DE6-9113-75F56E6BAB7C@.microsoft.com...
>I did several tests of performance of SQL2k5, and I see quite strange
>results.
> I restored database from SQL2k, rebuilded indexes, updated statistics. I
> did
> all what described in migration process. Backuped the resulting database.
> In
> each of next tests I restored the database from backup. Next I run tests.
> Since our application, OLTP, is very performance sensitive, we need to
> have
> good distribution of transaction execution time. In case if I run tests on
> SQL2k5, when snapshot isolation level is not turned on, I see good
> distribution - less than 0.5% of transactions executing more than 200ms,
> average execution time is ~5 ms. In case if I change compatibility level
> to
> 90 and turn on allow_snapshot_isolation, behavior is dramatically
> changing.
> Near 5% of transactions start to execute more than 200 ms, average
> execution
> time is also significally increasing. There are no transactions which are
> really use snapshot isolation level, stored procedures are exacly same as
> before. There is no any memory pressure, no IO bottlenecks. During the
> tests
> counters showed nearby 1 GB of free memory. IO system - EMC CX500. Data on
> 5x73GB FC 15kRPM drives (RAID0), log on 3 drives (RAID0). Database have 2
> GB
> size, but only ~200 MB of the data was read in the tests. 1.2GB write
> cache -
> it means what all writing during the test was done only to write cache on
> CX500, tests were not so long to fill the entire write cache. Windows 2003
> x64 Enterprise Edition, SQL2k5 x64 Developer Edition.
> CPU load was ~50% in tests without snapshot isolation level, and ~70% with
> tests with snapshot level. 2CPU.
> From the test it looks like I can not remove replication to QueryDB and
> run
> queries on same computer where OLTP load...
> Why impact of snapshot isolation level is so big?
>|||I understand what snapshot isolation adds some overhead, but I expected
lesser overhead. CPU consumption near 1.4 times more in case if snapshot
option turned on. It means what for most of performance sensitive OLTP
applications, which currently use SQL2k and which use replication to another
database used for longer queries, can not remove replication and run queries
on same server where main OLTP load after migration to SQL2k5. Quite
disappointing...
"Kalen Delaney" wrote:
> As soon as you change the isolation level to either Snapshot Isolation or
> Read Committed Snapshot, SQL Server will start saving ALL updated data in
> the version store in tempdb, whether or not any queries every read the
> versioned data. So every changed row will have additional overhead
> associated with it.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "andsm" <andsm@.discussions.microsoft.com> wrote in message
> news:B12BA2E7-8639-4DE6-9113-75F56E6BAB7C@.microsoft.com...
> >I did several tests of performance of SQL2k5, and I see quite strange
> >results.
> > I restored database from SQL2k, rebuilded indexes, updated statistics. I
> > did
> > all what described in migration process. Backuped the resulting database.
> > In
> > each of next tests I restored the database from backup. Next I run tests.
> > Since our application, OLTP, is very performance sensitive, we need to
> > have
> > good distribution of transaction execution time. In case if I run tests on
> > SQL2k5, when snapshot isolation level is not turned on, I see good
> > distribution - less than 0.5% of transactions executing more than 200ms,
> > average execution time is ~5 ms. In case if I change compatibility level
> > to
> > 90 and turn on allow_snapshot_isolation, behavior is dramatically
> > changing.
> > Near 5% of transactions start to execute more than 200 ms, average
> > execution
> > time is also significally increasing. There are no transactions which are
> > really use snapshot isolation level, stored procedures are exacly same as
> > before. There is no any memory pressure, no IO bottlenecks. During the
> > tests
> > counters showed nearby 1 GB of free memory. IO system - EMC CX500. Data on
> > 5x73GB FC 15kRPM drives (RAID0), log on 3 drives (RAID0). Database have 2
> > GB
> > size, but only ~200 MB of the data was read in the tests. 1.2GB write
> > cache -
> > it means what all writing during the test was done only to write cache on
> > CX500, tests were not so long to fill the entire write cache. Windows 2003
> > x64 Enterprise Edition, SQL2k5 x64 Developer Edition.
> > CPU load was ~50% in tests without snapshot isolation level, and ~70% with
> > tests with snapshot level. 2CPU.
> > From the test it looks like I can not remove replication to QueryDB and
> > run
> > queries on same computer where OLTP load...
> > Why impact of snapshot isolation level is so big?
> >
>
>|||There is a lot to consider with this. Snapshot isolation or read committed
snapshot write every single transaction into the version store. Since the
version store is in tempdb, in a high volume environment, you are in effect
pounding tempdb with every single transaction you issue. These are intended
to be applied when you have situations when you do not want reads to block
writes, BUT there is a performance trade-off when you implement that
functionality.
There are a couple of things you can do to minimize the impact of the
version store overhead. First, you need to move tempdb to its own disk
device so that it doesn't have to compete with everything else. Secondly,
you can add additional files (equal to the nmber of processors) to tempdb
that are of equal size in order to stripe the writes. BUT, you are still
going to have a performance impact.
--
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"andsm" <andsm@.discussions.microsoft.com> wrote in message
news:C3C9D444-5CD1-4C9A-9F2F-4BCD5DF26019@.microsoft.com...
>I understand what snapshot isolation adds some overhead, but I expected
> lesser overhead. CPU consumption near 1.4 times more in case if snapshot
> option turned on. It means what for most of performance sensitive OLTP
> applications, which currently use SQL2k and which use replication to
> another
> database used for longer queries, can not remove replication and run
> queries
> on same server where main OLTP load after migration to SQL2k5. Quite
> disappointing...
> "Kalen Delaney" wrote:
>> As soon as you change the isolation level to either Snapshot Isolation or
>> Read Committed Snapshot, SQL Server will start saving ALL updated data in
>> the version store in tempdb, whether or not any queries every read the
>> versioned data. So every changed row will have additional overhead
>> associated with it.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.solidqualitylearning.com
>>
>> "andsm" <andsm@.discussions.microsoft.com> wrote in message
>> news:B12BA2E7-8639-4DE6-9113-75F56E6BAB7C@.microsoft.com...
>> >I did several tests of performance of SQL2k5, and I see quite strange
>> >results.
>> > I restored database from SQL2k, rebuilded indexes, updated statistics.
>> > I
>> > did
>> > all what described in migration process. Backuped the resulting
>> > database.
>> > In
>> > each of next tests I restored the database from backup. Next I run
>> > tests.
>> > Since our application, OLTP, is very performance sensitive, we need to
>> > have
>> > good distribution of transaction execution time. In case if I run tests
>> > on
>> > SQL2k5, when snapshot isolation level is not turned on, I see good
>> > distribution - less than 0.5% of transactions executing more than
>> > 200ms,
>> > average execution time is ~5 ms. In case if I change compatibility
>> > level
>> > to
>> > 90 and turn on allow_snapshot_isolation, behavior is dramatically
>> > changing.
>> > Near 5% of transactions start to execute more than 200 ms, average
>> > execution
>> > time is also significally increasing. There are no transactions which
>> > are
>> > really use snapshot isolation level, stored procedures are exacly same
>> > as
>> > before. There is no any memory pressure, no IO bottlenecks. During the
>> > tests
>> > counters showed nearby 1 GB of free memory. IO system - EMC CX500. Data
>> > on
>> > 5x73GB FC 15kRPM drives (RAID0), log on 3 drives (RAID0). Database have
>> > 2
>> > GB
>> > size, but only ~200 MB of the data was read in the tests. 1.2GB write
>> > cache -
>> > it means what all writing during the test was done only to write cache
>> > on
>> > CX500, tests were not so long to fill the entire write cache. Windows
>> > 2003
>> > x64 Enterprise Edition, SQL2k5 x64 Developer Edition.
>> > CPU load was ~50% in tests without snapshot isolation level, and ~70%
>> > with
>> > tests with snapshot level. 2CPU.
>> > From the test it looks like I can not remove replication to QueryDB and
>> > run
>> > queries on same computer where OLTP load...
>> > Why impact of snapshot isolation level is so big?
>> >
>>
>>
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 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:
> 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 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:
> 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 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
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:
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 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:
> 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 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:
> 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 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
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:
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...
>>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.
>|||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:
> 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.
>
>|||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:
> 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.
>
>|||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 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
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...
>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:
>> 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.
>>|||"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...
> 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:
>> 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.
>>|||i'm getting similar results. sql2005 much slower.
however, for one particular query, i was able to get very similar speeds
after running the query through the index tuning wizard in 2005. of
course, it made me do quite a few things to get similar performance.
the worst things it had me do were to make huge covering indexes using
the "include" option on the create index statement. if i have to go
through this for every query against every database, it will be years
before i'll be able to upgrade to 2005.
andsm wrote:
> 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.|||I also encountered an overall loss of performance when running the same
tests on the same machine - inserting data into tables seems to be the
problem: Doing an OLE DB insert of 100.000 records lasts 42,3 seconds on
2000 but 189,5 seconds on 2005! Even bulk inserting 1 million records is
quite slow (9,8 seconds on 2000 but 25,2 seconds on 2005). Queries perform
the same or somewhat better...
2x AMD Athlon MP 2800+
4 GB of RAM
Windows 2000 Server, SP4
SQL Server 2000, Standard, SP3 (8.00.760) <=> SQL Server 2005, Standard, RTM
(9.0.1399)
Kind regards
Th. Hein
"andsm" <andsm@.discussions.microsoft.com> schrieb im Newsbeitrag
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.
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...
>>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.
>|||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:
> 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.
>
>|||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:
> 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.
>
>|||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 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
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...
>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:
>> 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.
>>|||"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...
> 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:
>> 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.
>>|||i'm getting similar results. sql2005 much slower.
however, for one particular query, i was able to get very similar speeds
after running the query through the index tuning wizard in 2005. of
course, it made me do quite a few things to get similar performance.
the worst things it had me do were to make huge covering indexes using
the "include" option on the create index statement. if i have to go
through this for every query against every database, it will be years
before i'll be able to upgrade to 2005.
andsm wrote:
> 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.|||I also encountered an overall loss of performance when running the same
tests on the same machine - inserting data into tables seems to be the
problem: Doing an OLE DB insert of 100.000 records lasts 42,3 seconds on
2000 but 189,5 seconds on 2005! Even bulk inserting 1 million records is
quite slow (9,8 seconds on 2000 but 25,2 seconds on 2005). Queries perform
the same or somewhat better...
2x AMD Athlon MP 2800+
4 GB of RAM
Windows 2000 Server, SP4
SQL Server 2000, Standard, SP3 (8.00.760) <=> SQL Server 2005, Standard, RTM
(9.0.1399)
Kind regards
Th. Hein
"andsm" <andsm@.discussions.microsoft.com> schrieb im Newsbeitrag
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.
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:
>
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:
>
Subscribe to:
Comments (Atom)