SQL Server Performance Forum – Threads Archive

Query is taking ages to execute….

Hi, I am trying to update TBL1 (approx. 22 million records) by getting information from another table TBL2 (6 million records). Here is my query UPDATE DB1.DBO.TBL1 SET DB1.DBO.TBL1.FLD1=DB2.DBO.TBL2.FLD1,DB1.DBO.TBL1.FLD2=DB2.DBO.TBL2.FLD2 FROM DB1.DBO.TBL1 LEFT JOIN DB2.DBO.TBL2 ON DB1.DBO.TBL1.FLD3=DB2.DBO.TBL2.FLD3 (DB2.DBO.TBL1.FLD3 and DB2.DBO.TBL2.FLD3 both are indexed). DB1 size mdf = 43.2 GB, ldf = 7.11 GB. DB2 size mdf = 12.2 GB, ldf = 8.60 GB. This query is running from last 1:47 hours:minutes and still executing. Do it suppose to execute for this long or is there any thing i can do? I can see Harddisk is spinning like hell (i am using 300GB Ultra scsi 10K), but CPU is quite. FYI, tempdb mdf = 3.23 GB, ldf = 14.6 MB. Any suggestion [<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />]. Regards, Waqar.

You can try to do it in batches of 1000 rows or so. It wouldn’t make it faster but could save log space if simple recovery model is used or frequent enough transaction log backups are performed.
Composite index on tbl2.fld3, fld2, fld1 could speed up the query.

Yesterday night, i left my machine on to execute this query overnight. But this morning when i came back office, this query was still executing [B)] from last 18 hours. I was not able to do any thing, so i decided to cancel the query. I stop query and restart server, and now i cannot work. After start it hangs, i stop SQL server and start in Single user mode and check logs and got the error below 2006-03-16 09:58:28.57 spid10 Recovery of database ‘DB2′ (6) is 4% complete (approximately 36044 more seconds) (Phase 3 of 3). [<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />!]. I think i need to recover this file. I am also getting error 2006-03-16 05:08:19.18 spid3 SQL Server has encountered 1 occurrence(s) of IO requests taking longer than 15 seconds to complete on file [D:MSSQLDataDB2.mdf] in database [DB2] (6). The OS file handle is 0x000003C0. The offset of the latest long IO is: 0x00000720938000 2006-03-16 05:13:19.18 spid53 SQL Server has encountered 8 occurrence(s) of IO requests taking longer than 15 seconds to complete on file [D:MSSQLDataDB2.mdf] in database [DB2] (6). The OS file handle is 0x000003C0. The offset of the latest long IO is: 0x000007209ae000 2006-03-16 05:18:19.18 spid53 SQL Server has encountered 548 occurrence(s) of IO requests taking longer than 15 seconds to complete on file [D:MSSQLDataDB2.mdf] in database [DB2] (6). The OS file handle is 0x000003C0. The offset of the latest long IO is: 0x00000445098000 2006-03-16 05:23:19.18 spid53 SQL Server has encountered 2214 occurrence(s) of IO requests taking longer than 15 seconds to complete on file [D:MSSQLDataDB2.mdf] in database [DB2] (6). The OS file handle is 0x000003C0. The offset of the latest long IO is: 0x000007208ee000 FYI, this is development machine, which have ULTRA SCSI 320, 300GB drive. Any advise for errors above? Waqar.

I think as your query was doing updates so when you killed the process it started to rollback.But as you restarted the server the rollback was in process.So when the server came back up again it started to finish the rollback.In the worst case the rollback might take as long as the job had been running.
This is just a guess and the reason could be something else too. cheers
Pali
Everything happens for your own good.

Hi Pali, Thanks for your reply.
I am running database at my machine with Single 300GB drive. My IO start sucking when i was trying to update table with join of 22 million and 6 million record.
Now i am waiting for another server with at least 4-5 drives to perform any such opertaion.

http://www.sqljunkies.com/Article/7F8518F9-FDAA-4FF3-8FC5-25E8946C8D0C.scuk
http://support.microsoft.com/?id=224587
http://support.microsoft.com/kb/835864 HTH Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>