2000 vs 2005 performance issue
- From: DNNX <6aLLIaPuMoB@xxxxxxxxx>
- Date: Wed, 26 Sep 2007 05:55:21 -0700
Hi pals,
I have a performance issue occurred during migration from 2000 to 2005
SQL Server. The migration process was basically as follows: backup the
DB on a source server, restore the DB and run sp_updatestats on a
target server.
After migration I ran the same resource-intensive task against the DB
on SQL 2000 and then against the migrated DB on 2005. The execution
time on SQL 2000 was almost half of the time on SQL 2005. Both servers
have the same hardware configuration. Please see the statistics:
System time: 4.94 seconds on 2000 against 5.17 seconds on 2005.
User time: 306.92 seconds on 2000 against 226.31 seconds on 2005.
Wait time: 837.21 seconds on 2000 against 1601.78 seconds on 2005.
I think the wait time grows due to more intensive HDD usage, but I'm
not sure. Anyway, even if that's the case, I need to determine the
problem more precisely. Could you please give me an advice how can I
figure out where exactly the problem may be and how can I resolve the
issue? Maybe I missed any important steps after migration? As per
client, both systems have similar configurations. Though I wonder if
hardware could cause such a performance difference (e.g. can a slower
HDD result in twofold increase of wait time)? Your recommendations are
welcome.
See the detailed problem description below:
The executed task consists of repeatedly executed updates (updated
field type is an image) and selects from a table of about 20,000,000
rows. The table consists of three columns: id - varchar(20) not null,
collection_id - varchar(101) not null and data - image null, and has a
primary clustered key (collection_id, id). Also the table has a
trigger on delete.
The following statements were executed repeatedly while the task
running:
SELECT id FROM big_table WHERE collection_id=@P1 AND id=@P2
SELECT data FROM big_table WHERE collection_id=@P1 AND id=@P2
UPDATE big_table SET data=0x00......AC13 WHERE collection_id=@P1 AND
id=@P2
Looking forward to your response.
.
- Prev by Date: Re: Copying tables without data
- Next by Date: Find unmatched records
- Previous by thread: Restoring a new version of a base already running
- Next by thread: Find unmatched records
- Index(es):
Relevant Pages
|
