"Identical" database, huge performance difference

I have a database that has big performance issue. I backup (complete
backup) the database and restore it with a new name within the same
instance, the performance gets back to normal. The size of both database
is 1,900 MB and space available of both is 1,100 MB.
Any idea about what makes the difference?so you're saying that you can:
* start with DB1
* back up DB1 and restore it as DB2
* you could then run the same exact query on DB1 and DB2. It would be fast
on DB2 and slow on DB1?
* If so... what steps do you need to do to then make DB2 slow? Or does
performance stay good forever?
Have you looked at SQL Profiler?
What are the exact performance problems you're seeing?
Are you restoring DB2 to the same disk arrays as DB1?
Is it possible that the physical files associated with DB1 have a serious
fragmentation problem?
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"qluo" <jluost1@.yahoo.com> wrote in message
news:40042824.2030705@.yahoo.com...
> I have a database that has big performance issue. I backup (complete
> backup) the database and restore it with a new name within the same
> instance, the performance gets back to normal. The size of both database
> is 1,900 MB and space available of both is 1,100 MB.
> Any idea about what makes the difference?
>|||Brian,
You are correct for the procedures I performed.
The performance problem is, the Web page with DB1 as the backend
database takes too long to load.
The query I used in Query Analyzer on both DB1 and DB2 is:
--
select count(*) from table1
--
It returns 72,000 on both database.
For DB1, it takes 220 ms consistently; for DB2, it takes 36ms consistently.
I have not looked at the fragmentation problem yet. But the data file
and log file for both databases are in the same physical location.
Brian Moran wrote:
> so you're saying that you can:
> * start with DB1
> * back up DB1 and restore it as DB2
> * you could then run the same exact query on DB1 and DB2. It would be fast
> on DB2 and slow on DB1?
> * If so... what steps do you need to do to then make DB2 slow? Or does
> performance stay good forever?
> Have you looked at SQL Profiler?
> What are the exact performance problems you're seeing?
> Are you restoring DB2 to the same disk arrays as DB1?
> Is it possible that the physical files associated with DB1 have a serious
> fragmentation problem?
>|||is DB1 a live database? ie, are people connecting to DB1
and making updates to table1?
if so, then the reason DB2 is faster for the same query is
that it knows no one has updated table1 on DB2, hence it
is safe to execute as select count(*) from table1 (NOLOCK)
while DB1 must row lock if there were recent
inserts/upd/del to table1
try
select count(*) from table1 (NOLOCK)
on both
-joe
>--Original Message--
>Brian,
>You are correct for the procedures I performed.
>The performance problem is, the Web page with DB1 as the
backend
>database takes too long to load.
>The query I used in Query Analyzer on both DB1 and DB2 is:
>--
>select count(*) from table1
>--
>It returns 72,000 on both database.
>For DB1, it takes 220 ms consistently; for DB2, it takes
36ms consistently.
>I have not looked at the fragmentation problem yet. But
the data file
>and log file for both databases are in the same physical
location.
>
>Brian Moran wrote:
>> so you're saying that you can:
>> * start with DB1
>> * back up DB1 and restore it as DB2
>> * you could then run the same exact query on DB1 and
DB2. It would be fast
>> on DB2 and slow on DB1?
>> * If so... what steps do you need to do to then make
DB2 slow? Or does
>> performance stay good forever?
>> Have you looked at SQL Profiler?
>> What are the exact performance problems you're seeing?
>> Are you restoring DB2 to the same disk arrays as DB1?
>> Is it possible that the physical files associated with
DB1 have a serious
>> fragmentation problem?
>.
>|||Your point is exactly right. I ran the query with (NOLOCK) on both DB1
and DB2 and they are taking the same amount of time, and dB1 is quicker
now than before.
So, what do I need to do on DB1 to achieve the same performance without
using (NOLOCK)?
DB1 is not actually busy and it is used by Web applications.
Thank you.
joe chang wrote:
> is DB1 a live database? ie, are people connecting to DB1
> and making updates to table1?
> if so, then the reason DB2 is faster for the same query is
> that it knows no one has updated table1 on DB2, hence it
> is safe to execute as select count(*) from table1 (NOLOCK)
> while DB1 must row lock if there were recent
> inserts/upd/del to table1
> try
> select count(*) from table1 (NOLOCK)
> on both
> -joe
>>--Original Message--
>>Brian,
>>You are correct for the procedures I performed.
>>The performance problem is, the Web page with DB1 as the
> backend
>>database takes too long to load.
>>The query I used in Query Analyzer on both DB1 and DB2 is:
>>--
>>select count(*) from table1
>>--
>>It returns 72,000 on both database.
>>For DB1, it takes 220 ms consistently; for DB2, it takes
> 36ms consistently.
>>I have not looked at the fragmentation problem yet. But
> the data file
>>and log file for both databases are in the same physical
> location.
>>Brian Moran wrote:
>>so you're saying that you can:
>>* start with DB1
>>* back up DB1 and restore it as DB2
>>* you could then run the same exact query on DB1 and
> DB2. It would be fast
>>on DB2 and slow on DB1?
>>* If so... what steps do you need to do to then make
> DB2 slow? Or does
>>performance stay good forever?
>>Have you looked at SQL Profiler?
>>What are the exact performance problems you're seeing?
>>Are you restoring DB2 to the same disk arrays as DB1?
>>Is it possible that the physical files associated with
> DB1 have a serious
>>fragmentation problem?
>>
>>.
>|||You could set SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED for your
connection. There should be a trx isolation level property for your
database connectivity object. Not that this setting will be effective for
all queries passing through the connection.
--
Regards
Ray Mond|||I am reluctant to change the default (Read Committed).
My database is not busy at all. It is used by Web app and every
connection should come and go almost immediately. There shouldn't be a
concurrent(locking) issue like this.
Do you know what else I can do to minimize the locking so that I don't
have to change TRANSACTION ISOLATION LEVEL?
Thank you.
Ray Mond wrote:
> You could set SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED for your
> connection. There should be a trx isolation level property for your
> database connectivity object. Not that this setting will be effective for
> all queries passing through the connection.
Your point is exactly right. I ran the query with (NOLOCK) on both DB1
and DB2 and they are taking the same amount of time, and dB1 is quicker
now than before.
So, what do I need to do on DB1 to achieve the same performance without
using (NOLOCK)?
DB1 is not actually busy and it is used by Web applications.
Thank you.
joe chang wrote:
is DB1 a live database? ie, are people connecting to DB1 and making
updates to table1?
if so, then the reason DB2 is faster for the same query is that it knows
no one has updated table1 on DB2, hence it is safe to execute as select
count(*) from table1 (NOLOCK)
while DB1 must row lock if there were recent inserts/upd/del to table1
try
select count(*) from table1 (NOLOCK)
on both
-joe
--Original Message--
Brian,
You are correct for the procedures I performed.
The performance problem is, the Web page with DB1 as the
backend
database takes too long to load.
The query I used in Query Analyzer on both DB1 and DB2 is:
--
select count(*) from table1
--
It returns 72,000 on both database.
For DB1, it takes 220 ms consistently; for DB2, it takes
36ms consistently.
I have not looked at the fragmentation problem yet. But
the data file
and log file for both databases are in the same physical
location.
Brian Moran wrote:
so you're saying that you can:
* start with DB1
* back up DB1 and restore it as DB2
* you could then run the same exact query on DB1 and
DB2. It would be fast
on DB2 and slow on DB1?
* If so... what steps do you need to do to then make
DB2 slow? Or does
performance stay good forever?
Have you looked at SQL Profiler?
What are the exact performance problems you're seeing?
Are you restoring DB2 to the same disk arrays as DB1?
Is it possible that the physical files associated with
DB1 have a serious
fragmentation problem?
.
>|||I think the only option left here is to use that suggested by Joe Young i.e.
use the (NOLOCK) hint in the queries where you do not care about open
transactions.
--
Regards
Ray Mond
"qluo" <jluost1@.yahoo.com> wrote in message
news:400554D8.8080900@.yahoo.com...
> I am reluctant to change the default (Read Committed).
> My database is not busy at all. It is used by Web app and every
> connection should come and go almost immediately. There shouldn't be a
> concurrent(locking) issue like this.
> Do you know what else I can do to minimize the locking so that I don't
> have to change TRANSACTION ISOLATION LEVEL?
> Thank you.
> Ray Mond wrote:
> > You could set SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED for your
> > connection. There should be a trx isolation level property for your
> > database connectivity object. Not that this setting will be effective
for
> > all queries passing through the connection.
> Your point is exactly right. I ran the query with (NOLOCK) on both DB1
> and DB2 and they are taking the same amount of time, and dB1 is quicker
> now than before.
> So, what do I need to do on DB1 to achieve the same performance without
> using (NOLOCK)?
> DB1 is not actually busy and it is used by Web applications.
> Thank you.
>
> joe chang wrote:
> is DB1 a live database? ie, are people connecting to DB1 and making
> updates to table1?
> if so, then the reason DB2 is faster for the same query is that it knows
> no one has updated table1 on DB2, hence it is safe to execute as select
> count(*) from table1 (NOLOCK)
> while DB1 must row lock if there were recent inserts/upd/del to table1
> try
> select count(*) from table1 (NOLOCK)
> on both
> -joe
> --Original Message--
> Brian,
> You are correct for the procedures I performed.
> The performance problem is, the Web page with DB1 as the
> backend
> database takes too long to load.
> The query I used in Query Analyzer on both DB1 and DB2 is:
> --
> select count(*) from table1
> --
> It returns 72,000 on both database.
> For DB1, it takes 220 ms consistently; for DB2, it takes
> 36ms consistently.
> I have not looked at the fragmentation problem yet. But
> the data file
> and log file for both databases are in the same physical
> location.
>
> Brian Moran wrote:
> so you're saying that you can:
> * start with DB1
> * back up DB1 and restore it as DB2
> * you could then run the same exact query on DB1 and
> DB2. It would be fast
> on DB2 and slow on DB1?
> * If so... what steps do you need to do to then make
> DB2 slow? Or does
> performance stay good forever?
> Have you looked at SQL Profiler?
> What are the exact performance problems you're seeing?
> Are you restoring DB2 to the same disk arrays as DB1?
> Is it possible that the physical files associated with
> DB1 have a serious
> fragmentation problem?
>
> .
>
> >
>|||I did "Set transaction isolation level read uncommitted" on the database
server, but it made no difference. I think the OLEDB used by PHP Web app
is using "Set transaction isolation level read committed". So no
matter what I have set on database server doesn't matter.
I can use "NOLOCK" hint for each sql statement in the application. But
there are too many of them and I am reluctant to do so.
Again, I backed up DB1 and restore it with the new name DB2. I don't
need to use "NOLOCK" hint on DB2. But for DB1, I have to use the hint to
gain the same performance.
When I check the Process/Locks in SQL Enterprise Manager, I can see that
the locks come and go. When I see no locks for DB1 and then run my
sql, I can see the locks by my sql are the only locks. Why NOLOCK hint
will make the difference for DB1 (it cuts the response time from 1.1
seconds to 0.6 seconds for 746 rows)?
Joe Young said the reason is DB1 is "busy" and DB2 is not. Why does DB1
appear to be busy, indeed, it is not busy at all?
Thank you.
Ray Mond wrote:
> I think the only option left here is to use that suggested by Joe Young i.e.
> use the (NOLOCK) hint in the queries where you do not care about open
> transactions.
>|||By any chance, is DB2 (your database, not IBM's :) in read-only mode? Also,
could you pls run SET STATISTICS IO ON in Query Analyzer, run both queries
and post the resulting messages? Then run SET STATISTICS IO OFF and run SET
STATISTICS TIME ON, rerun the queries and post the messages too? Just
curious to see the results.
Thanks.
--
Regards
Ray Mond
"qluo" <jluost1@.yahoo.com> wrote in message
news:4006BF8E.7060302@.yahoo.com...
> I did "Set transaction isolation level read uncommitted" on the database
> server, but it made no difference. I think the OLEDB used by PHP Web app
> is using "Set transaction isolation level read committed". So no
> matter what I have set on database server doesn't matter.
> I can use "NOLOCK" hint for each sql statement in the application. But
> there are too many of them and I am reluctant to do so.
>
> Again, I backed up DB1 and restore it with the new name DB2. I don't
> need to use "NOLOCK" hint on DB2. But for DB1, I have to use the hint to
> gain the same performance.
> When I check the Process/Locks in SQL Enterprise Manager, I can see that >
the locks come and go. When I see no locks for DB1 and then run my
> sql, I can see the locks by my sql are the only locks. Why NOLOCK hint
> will make the difference for DB1 (it cuts the response time from 1.1
> seconds to 0.6 seconds for 746 rows)?
> Joe Young said the reason is DB1 is "busy" and DB2 is not. Why does DB1
> appear to be busy, indeed, it is not busy at all?
> Thank you.
>
>
> Ray Mond wrote:
> > I think the only option left here is to use that suggested by Joe Young
i.e.
> > use the (NOLOCK) hint in the queries where you do not care about open
> > transactions.
> >
>