Similar databases behave different

[RiyaKumar]

Hi All,

We have two databases 'A' & 'B' having the same objects and data and got exactly the same database settings. The datafiles for the databases 'A' & 'B' reside in the same drive of the server. We've got an upload job which uploads data from multiple spreadsheets into database 'A' or 'B'. The upload job in DB 'A' seem to take twice the time as it does in DB 'B'. I tried executing the job in the morning when there's no user connected. But still DB 'A' takes more time.

Can someone throw light on why two similar databases behave differently sometimes?


Thanks.
RiyaKumar

 

[SQLBill]

Are you positive they are the same? Does one have indexes that the other doesn't? Does A have triggers on it that B doesn't?

-SQLBill

Posting advice: FAQ481-4875

 

[Catadmin]

Further more, are the two DBs on the same machine or different ones?

If different, have you verified your hardware is the same on both machines and in working condition? Have you also verified nothing else on the machine with DB A is having problems?


If both are using the same machine, also verify that your update job for DB A isn't running concurrently with any other Windows or SQL jobs.



Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!

 

[RiyaKumar]

The two databases are exactly similar b'cos we copy the objects and data from DB 'A' to DB 'B' every night. And both of them remain in the same server with their datafiles in the same drive. And we have simple recovery mode for both the databases.

The update job is not a scheduled job and I tried executing it at different times of the day on DB 'A' and 'B'. And always DB 'A' took twice the time as 'B'.

'A' is the production DB and 'B' is used as Test DB. Since they are syncronised in the night, I tried the upload job in the morning as a first thing when no users are connected,still there's a time difference!

Anything which I need to check?

Thanks,
Priya

 

[Catadmin]

The number of open connections on the A database. If it is production, there is always the possibility that there are orphaned connections on it.

Also verify that the processor on your SQL Server is set to prioritize SQL Server requests (Server -> Properties -> Processor).

Are both DBs on the same instance of SQL?



Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!

 

[RiyaKumar]

Thanks for the reply.

Yes. The databases 'A' and 'B' reside in the same instance of SQL.

No. The option 'Boost SQL Priority on Windows' is not set.

I used to check the number of connections on each database in Enterprise Manager -> Server -> Management -> Current Activity -> Process Info. And I used to see almost the same number of connections to both 'A' and 'B' databases in the morning when I do the upload testing.

By the way, how to check 'Orphan' connections?

Is there anything else I need to check?

Thanks.