0

For many years there has been a long running SQL Agent job that has taken nearly two hours to complete. Approximately two weeks ago on a slight whim a decision was made to change the growth rate on the database in question. Since then the job has been completing in less than 30 minutes. Would changing the growth rate on the log force the generation of a new execution plan. I am just curious what has happened internally within SQL Server that would have resulted in quicker run times.

Thanks

1
  • 2
    What tasks does that job do? Can you get the older execution plan from the plan cache to compare with the new one to check if it really changed or if the improvement on the speed of the job was due to something else?
    – Ronaldo
    Sep 22, 2021 at 14:53

2 Answers 2

7

I am just curious what has happened internally within SQL Server that would have resulted in quicker run times.

When a query is running, and the transaction log file is required to grow, a bunch of stuff happens that will inevitably slow down the total runtime of the query.

A non-exhaustive list of those things include:

  • the query is put into a wait state,
  • SQL server may have to wait for other concurrent queries to finish so it can get exclusive access to the transaction log file,
  • SQL Server calls into the Windows file APIs to grow the log file (which depends on drivers, Windows task scheduling, the speed of your disks, etc.),
  • the log file has to be "zeroed out",
  • the query has to be started back up and resume processing where it left off.

If this job does generates a lot of transaction log (i.e., it does inserts, updates, and deletes), that can cause the log to grow. If you increase the "filegrowth" setting for the log file to a higher value, then this big filegrowth operation will happen less frequently, and the overall job will run faster (as you observed).

All of the above is essentially true for data file growth as well, except that the data files don't necessarily need to be zeroed out (if you have "instant file initialization" (IFI) enabled).


The defaults for data and log file growth settings, especially on older versions of SQL Server, is very low. One recommendation is to set them 256 MB and 128 MB respectively. Regardless of the growth settings, you should strive to do what you can to avoid these file growth events when possible, especially during time sensitive operations.

For transaction log growth, this might mean doing smaller batches of work at once, or doing more frequent log backups, etc.

For data files, it might mean enabling IFI and manually growing data files periodically during a maintenance window when your monitoring alerts that the file is nearly full.


As I hope it's clear from my answer, it seems unlikely that an execution plan change is responsible for the performance difference you saw. But to answer the question from the title, which I just realized I forgot to do:

Does changing database growth rate force a new execution plan?

I don't actually know for sure if changing the data file growth settings invalidates the plan cache, but I don't think that it does.

2
  • All true - but TJFFSSQLDEV would have to be shrinking the log file in between job runs Sep 23, 2021 at 15:14
  • @StephenMorris-Mo64 True! I got mixed signals from the question as to whether it was just log growth that was changed, or data file growth as well ("a decision was made to change the growth rate on the database in question"). If the job imports new data (or something like that), and the data file growth is tiny, they could hit the growth events on every run. Sep 23, 2021 at 16:11
0

Possibly, but execution plan changes due to parameter sniffing or some other table rebuild or just stats update - seems more likely to get a big performance gain from those sources rather than just the autogrow setting itself.

New execution plan due to growth setting change? Indirectly, it might happen.

Potentially the growth setting itself could have an effect, so if the growth setting resulted in a lot of very small growth & that stalled the query all the way through the query. It would seem like a pretty slow filesystem & a very small autogrow setting.

For the query to speed up that much just by changing the setting to a higher value seems unusual - can you be sure no other settings were changed in the meantime?

To repeat regularly, the query would also have to find almost zero headroom in the files the next time job has to run again too (autoshrink is on?), or maybe the db is growing steadily.

In general, growth rate shouldn't be a factor unless it's always being triggered, so I would look at the amounts that are free prior to each run, what is used during, whether growth is triggered & whether autoshrink is set.

Autoshrink does shuffle pages so some tables can be out of sequence & that can be a performance hit. Not sure it changes the execution plans though.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.