[dba-SQLServer] SQL Server Speed testing - update to real codes.

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

More information about the dba-SQLServer mailing list