Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
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
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:
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.
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.
