October 5, 2004 at 8:59 am
Today I copied my production database to a developer's database. Same server, same disk, same everything. It's a 6 Gig DB on SQLServer2000 under Win2003. In production a maximum of 8 users. Usually the server is not very busy.
I noticed that a query is 30-50% slower in production compared to execution in my new DB copy. Since there were no changes at all, the two DBs should be identical, including Indexes, Query plans etc. The speed difference is quite constant, I've tried more than a hundred times - it always takes 30-50% more time in production.
It took me a while to find out what happens. In production, my select statement obviously uses row locks. In development, SQL server uses page locks. SLQ Profiler shows about 10,000 Lock-acquired/released cycles in production and aboput 1,000 cycles in development. This makes dev faster.
But why does SQL server use different Lock types? Is it some optiimization, because users are present in production? sp_lock does not show any locks on the table I use. Updating statistics / reindexing indexes did not make a change.
Anybody any ideas?
October 5, 2004 at 9:13 pm
October 6, 2004 at 1:06 am
Good idea, but it did not change the server's behavior.
I have checked the query plan - it is identical. Both DBs run in the same SQL Server instance on a single Proliant Server. The .MDF / .LDF files of prod and dev sit next to each other in the same directory. The development DB was built using a "restore" of last night's production DB. Just to be sure, I ran sp_configure and sp_dboption in either DB and compared - all the same.
But still SQL Server chooses different lock types on the same query. The query is:
select min(HTOT_REQUEST_FROM_DATE)
from hdtot
where HTOT_CLIENT = '155 780 00'
Even though I do not see any locks from other processes on the table, I can't think of any other reason than the users's presence in production. I have a feeling though there's something I haven't thought of.....
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply