Sql Script takes much longer than estimated

Posted on

Question :

In order to clean up a very large table holding binary Files in IMAGE Cols, I created the following sequence (based on this: Article) to get rid of a part of those image Blobs quickly:

  1. Select * into temp table where (filtering a small part I want to keep)
  2. Select the rest of rows WITH a NULL value for the BLOB into same temp table.
  3. Truncate the original blob table
  4. copy all records back from the temp table to the original table.

I did a test run on a local machine (notebook) and Step No. one took about 20 seconds for about 2 Milion records.

The whole script took about 4 minutes while the original table held about 2.5 Milion REcords.

In a staging server env. step No. one meanwhile takes > 25 Minutes to copy 3.2 Million records!!! And SSMS actually stopped interacting with me. I estimated (using calc.exe) it might take about 1 Minute…

Now I wonder what is wrong…?


Answer :

I figured it was the Revovery Model that was left set to FULL. As the article linked in the question says, it is required to change it to simple of bulk: “The recovery model SIMPLE and BULK_LOGGED perform “best” contrary to the recovery model FULL.”
So, after setting it to simple the process performed within couple of minutes.

One reason why it is taking so long could be the AutoGrow settings on the database files and log files. If that is set to a very low value, you might end up with many many, synchronous, auto grow events. You can avoid this by pregrowing the data/log files and/or increasing the AutoGrow settings.

are you beeing blocked by another process?

you can check that by using sp_whoisactive (see http://whoisactive.com/)

here you can also see how far your job is, and what the execution plan look like.

Leave a Reply

Your email address will not be published. Required fields are marked *