Hi
I have a problem with sevral update scripts having massively increased in run time :
The scripts updates one column on a table by joining to another table of the same size and then joining to another smaller table the following is an example:
UPDATE AGG_10_TT_DATA
SET PUB_ISBN_CHECK_SUM = UI.ID_FIELD
FROM AGG_10_TT_DATA AD,
AGG_10_KEY_IDS AK,
UNIQUE_ISBN UI
WHERE AD.AGG_10_ID_CHECK_SUM = AK.AGG_10_ID_CHECK_SUM AND
AK.ISBN = UI.ISBN
The two AGG_ tables have 29121587 rows each and the UNIQUE_ISBN has 241905 rows.
When I ran these a couple of months ago on my production database they took no more than 2-3 hours to run, the data volumes have not changes significantly in the meantime.
I have made a copy of the database on the same machine (quad processor & 5gb memory) and when I run the scripts on this they are still running 30-40 hours later & I have not been able to let them finish, if indeed they would.
I have run a DBCC CHECKDATABASE on the database and on the tables involved and have no errors. I have backed up the database and run a maintenance job to optimise the indexes etc. to ensure the database is not corrupt.
Whilst the Job is runnning there is very little CPU activity but a lot of I/O so it appears to be disk bound ( the disk is held in a SAN)
The database has AUTOSHRINK set on it and whilst this job is running the AUTOSHRINK process is permanently blocked by the job.
The only difference I can see between the production database and this development version is that some of the data files on the development database have been placed on a different disk in the SAN, this disk is normally used for holding the backups. The two large tables are held on the normal data disk the lookup table is on the backup disk.
When the job is running in isolation the only other process which appears to be running on the server is bengine.exe which is running a backup of the backup disk. This is using no CPU and its I/O is fairly small when compared with SQL server, also the I/O is on the backup disk whereas the sql job is mainly accessing the main data disk.
The job whilst running does not seem to impact the performance of the production database to any great measure.
Any suggestions as to what might be slugging this job?
I have a problem with sevral update scripts having massively increased in run time :
The scripts updates one column on a table by joining to another table of the same size and then joining to another smaller table the following is an example:
UPDATE AGG_10_TT_DATA
SET PUB_ISBN_CHECK_SUM = UI.ID_FIELD
FROM AGG_10_TT_DATA AD,
AGG_10_KEY_IDS AK,
UNIQUE_ISBN UI
WHERE AD.AGG_10_ID_CHECK_SUM = AK.AGG_10_ID_CHECK_SUM AND
AK.ISBN = UI.ISBN
The two AGG_ tables have 29121587 rows each and the UNIQUE_ISBN has 241905 rows.
When I ran these a couple of months ago on my production database they took no more than 2-3 hours to run, the data volumes have not changes significantly in the meantime.
I have made a copy of the database on the same machine (quad processor & 5gb memory) and when I run the scripts on this they are still running 30-40 hours later & I have not been able to let them finish, if indeed they would.
I have run a DBCC CHECKDATABASE on the database and on the tables involved and have no errors. I have backed up the database and run a maintenance job to optimise the indexes etc. to ensure the database is not corrupt.
Whilst the Job is runnning there is very little CPU activity but a lot of I/O so it appears to be disk bound ( the disk is held in a SAN)
The database has AUTOSHRINK set on it and whilst this job is running the AUTOSHRINK process is permanently blocked by the job.
The only difference I can see between the production database and this development version is that some of the data files on the development database have been placed on a different disk in the SAN, this disk is normally used for holding the backups. The two large tables are held on the normal data disk the lookup table is on the backup disk.
When the job is running in isolation the only other process which appears to be running on the server is bengine.exe which is running a backup of the backup disk. This is using no CPU and its I/O is fairly small when compared with SQL server, also the I/O is on the backup disk whereas the sql job is mainly accessing the main data disk.
The job whilst running does not seem to impact the performance of the production database to any great measure.
Any suggestions as to what might be slugging this job?