We are running into the issue that our stored procedure on our new SQL Server performs a lot slower than on the old server. We looked into the problem and noticed the following on our temp db. The incremental growth takes 3 times as long on the new server. The same happens for the log file of our database. What is causing this?
We are running into the issue that our stored procedure on our new SQL Server performs a lot slower than on the old server.
The incremental growth takes 3 times as long on the new server.
Use DISKSPD to benchmark the IO performance on both servers. Log file growth always requires zero’ing the allocated space. Also pre-size the log files and increase the auto-growth increment.
While determining why the growth is taking longer on the new server is a task that can depend a lot o your environment, as noted by David Spillett’s comment, it’s likely that you might have sized the tempdb in a way that is not optimal for your server, causing auto growth to be triggered frequently.
As Brent Ozar says on his Cheat Sheet: How to Configure TempDB for Microsoft SQL Server:
configure one volume/drive for TempDB. Divide the total space by 9,
and that’s your size number. Create 8 equally sized data files and one
log file, each that size. Presto, the drive is full and your TempDB is
configured for easy performance.
Sizing tempdb properly is also listed on the Optimizing tempdb performance in SQL Server doc as a good practice:
Preallocate space for all tempdb files by setting the file size to a
value large enough to accommodate the typical workload in the
environment. Preallocation prevents tempdb from expanding too often,
which affects performance. The tempdb database should be set to
autogrow to increase disk space for unplanned exceptions.
Finally, if your new server has more than 64 CPUs, the doc on Best Practices for running SQL Server on computers that have more than 64 CPUs says:
Do not rely on autogrow to increase the size of the transaction log
file. Increasing the transaction log must be a serial process.
Extending the log can prevent transaction write operations from
proceeding until the log extension is finished. Instead, preallocate
space for the log files by setting the file size to a value large
enough to support the typical workload in the environment.
Considering mainly the point about preventing transaction write operations, if the SP that is not performing does any write operation, it would be affected by the autogrow.
So, even though this advice does not answer your question about the reason the growth is taking longer to be performed, I thought it was worth mentioning it.