jwcolby
jwcolby at colbyconsulting.com
Wed Sep 30 21:38:56 CDT 2009
OK so this pass will use my stored procedure / udf to perform actual updates of the AddrValid field to the appropriate code, dependent upon the address data in other fields of each record. BTW I went to three as the Max Degree of Parallelism. Having looked this up it seems that this is the maximum number of CPUs (or cores) that SQL Server will use for queries that can be processed in parallel. I have not experienced the "lockup" issue since I went from 0 (all available cores) to <=3. However I am not yet ready to declare the problem solved. So, on to numbers: 500K 8 seconds 500K 8 seconds 1M 16 seconds 1M 16 seconds 2M 33 seconds 2M 35 seconds 4M 68 seconds 4M 77 seconds So it appears that using the stored procedure takes roughly the "worst case" time from the previous timings. The first set of a direct update of the field to NULL took approximately the same as my results here, except that I am not getting an advantage the second time. This would tend to indicate that it is the "compile" time or whatever that is called (execution plan stuff), that is taking the extra time, and given that I am running dynamic SQL it is not possible to generate an execution plan that can be used the next time. Maybe. I am so uneducated in SQL Server that I probably sound like a rambling madman. ;) BTW, the log file now sits at 4.524 GB however it is 98% available. And BTW I did not shrink before this set of runs so I did not have to endure as much log file expansion. So one final run, just passing in the table directly to be updated, rather than a view of Top(N), but also no WHERE AddrValid IS NULL. My expectations would be that it would take about 6 minutes, i.e. if 4 megs is ~70 seconds and there are 20M records to process - 70 seconds * 5 = 350 seconds or ~ 6 minutes. As you saw from my ramblings in yesterday's email it actually took some 24 minutes which is completely unexpected and unexplained. And the results are in... (drum roll please).... 3:47. The log file is now 4.524 GB and 98% available. OK so I shrank the log file. The results including having to perform a log file expansion... 3:45. And the log file is not expanded. OK, something wrong there. I went back and updated all records to null. 18:54. Didn't check log file size. Sorry for the lack of science here. Updated to valid codes: 10:44. Log file size 17.18 GB, 84% available free space. Updated back to NULL: 9:52 minutes. Log file 17.18 GB. 84% free. Update to valid codes: 9:58. Log file 18.132. 49% Free. So there you have it. The computer is not locking up so far and the updates seem to be happening reasonably quickly. So what was the real issue before? Another MS mystery. -- John W. Colby www.ColbyConsulting.com