strange performance

[MGB]

I have a strange performance question hopefully someone can clarify
for me. I take a production database and make a copy of it, called
test, on the same instance on the same server both running at the same
time. All the same things are running for each database and no one is
using each database but me. From Query Analyzer I run a SQL against
production three times in a row and it takes 1 minute 40 seconds on
the last/best run. I then run the same SQL against the test copy and
run it three times in a row and the last/best time is 12 seconds. Can
anyone explain this behavior? If so, I hope this points to something I
can do to my production database to get the same performance.
Thanks,
MGB

[Andrey]

MGB wrote:

I have a strange performance question hopefully someone can clarify
for me. I take a production database and make a copy of it, called
test, on the same instance on the same server both running at the same
time. All the same things are running for each database and no one is
using each database but me. From Query Analyzer I run a SQL against
production three times in a row and it takes 1 minute 40 seconds on
the last/best run. I then run the same SQL against the test copy and
run it three times in a row and the last/best time is 12 seconds. Can
anyone explain this behavior? If so, I hope this points to something I
can do to my production database to get the same performance.
Thanks,
MGB

Did you check indexes for the tables in the original db?
Do you have the same indexes in the test db?
I had similar issue not long ago, when i copied a dozen of tables to another
database and quesries ran much faster against them comparing to the original tables in original db.
What i found that the original tables were 'overloaded' with 3/4/5 column indexes, so index file
was around 500Meg. When i dropped all indexes and let QA Wizard redo them, i got good execution times.

Also, it helps someties to stop and re-start the sql service :)

WYGL,
Andrey

[MGB]

Andrey,
Thanks for the reply. I should have been more clear in my original
post: The "copy' was made by taking a full backup of the production
database and restoring it onto the test database. Correct me if I am
wrong, but I assume the indexes would remain intact in that case. It
would probably be good to mention that this is on SQL Server 2000
running on a Windows 2000 Advanced Server.

Many times I have considered restarting the service but I have read
that the service "learns" how to automatically set setting for
performance. So I have not restarted so it wouldn't have to re-learn.
Is that not the case?

There is one other difference I thought of after my original post: As
part of the restore process I change the recovery model from Full to
Simple in the test database.

Thanks,
MGB

Andrey <le*******@yahoo.com> wrote in message news:<7Q36d.127675$D%.85272@attbi_s51>...

MGB wrote:

I have a strange performance question hopefully someone can clarify
for me. I take a production database and make a copy of it, called
test, on the same instance on the same server both running at the same
time. All the same things are running for each database and no one is
using each database but me. From Query Analyzer I run a SQL against
production three times in a row and it takes 1 minute 40 seconds on
the last/best run. I then run the same SQL against the test copy and
run it three times in a row and the last/best time is 12 seconds. Can
anyone explain this behavior? If so, I hope this points to something I
can do to my production database to get the same performance.
Thanks,
MGB

Did you check indexes for the tables in the original db?
Do you have the same indexes in the test db?
I had similar issue not long ago, when i copied a dozen of tables to another
database and quesries ran much faster against them comparing to the original tables in original db.
What i found that the original tables were 'overloaded' with 3/4/5 column indexes, so index file
was around 500Meg. When i dropped all indexes and let QA Wizard redo them, i got good execution times.

Also, it helps someties to stop and re-start the sql service :)

WYGL,
Andrey

[L. Bertolini]

A couple thoughts:

1. Does your benchmark SQL perform updates? If yes, is the original
database using "full" recovery model, and the copy using "simple"?
(The recovery model is inherited from the "model" database.)

2. Are both databases on the same disk(s) and controllers?
Is the original database .mdf file highly fragmented on the disk?
Try to detach the database, defrag the disk, then re-attach, and see
if it makes a difference.

MGB wrote:

I have a strange performance question hopefully someone can clarify
for me. I take a production database and make a copy of it, called
test, on the same instance on the same server both running at the same
time. All the same things are running for each database and no one is
using each database but me. From Query Analyzer I run a SQL against
production three times in a row and it takes 1 minute 40 seconds on
the last/best run. I then run the same SQL against the test copy and
run it three times in a row and the last/best time is 12 seconds. Can
anyone explain this behavior? If so, I hope this points to something I
can do to my production database to get the same performance.
Thanks,
MGB

[MGB]

Yes. Production (original) is using "full" recovery model, and the
copy is using "simple". Is "simple" faster?

Yes. both databases are on the same disk(s) and controllers. Haven't
checked for fragmentation.

Thanks,
MGB
"L. Bertolini" <be*********@osu.edu> wrote in message news:<cj*********@charm.magnus.acs.ohio-state.edu>...

A couple thoughts:

1. Does your benchmark SQL perform updates? If yes, is the original
database using "full" recovery model, and the copy using "simple"?
(The recovery model is inherited from the "model" database.)

2. Are both databases on the same disk(s) and controllers?
Is the original database .mdf file highly fragmented on the disk?
Try to detach the database, defrag the disk, then re-attach, and see
if it makes a difference.

MGB wrote:

I have a strange performance question hopefully someone can clarify
for me. I take a production database and make a copy of it, called
test, on the same instance on the same server both running at the same
time. All the same things are running for each database and no one is
using each database but me. From Query Analyzer I run a SQL against
production three times in a row and it takes 1 minute 40 seconds on
the last/best run. I then run the same SQL against the test copy and
run it three times in a row and the last/best time is 12 seconds. Can
anyone explain this behavior? If so, I hope this points to something I
can do to my production database to get the same performance.
Thanks,
MGB

[L. Bertolini]

MGB wrote:

Yes. Production (original) is using "full" recovery model, and the
copy is using "simple". Is "simple" faster?

Yes, because there is less logging overhead.