Performance of stored procedure is improved after database is backed up and restored

I have a stored procedure that performs very poorly on SQL Server 2000 standard edition. It takes approximately 40 seconds to execute. In the course of troubleshooting the issue I backed up the database to disk and restored it to another SQLServer and the performance issue was resolved. On a hunch I went to the original server, backed up the database to disk, and then restored the database from the backup I just created and voila the problem was resolved. Why would this resolve the issue? I have another database with the same stored procedure with the same problem. I have tried the following on the stored procedure and all related tables with no success:

DBCC DBREINDEX

UPDATE STATISTICS

sp_recompile my_storedprocedure.

What is the restore doing to resolve the issue? What command can I run to resolve this. I have a feeling it is the query plan but wouldn't update statistics correct this?

Is it possible that the physical database files are getting fragmented?

The restore would then lay the files down contiguously, which would improve performance.

Other than that, the content of the database including statistics would be exactly the same after the restore as it was at the time of backup. SQL backup is a physical copy of every allocated byte in the database files.

Performance of SQL2k5 with snapshot isolation level turned on

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:
>

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:

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?
>> >
>>
>>