1

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?

enter image description here

1
  • 2
    Could be a number of things, we'd need to know details of the IO subsystems and layouts on both machines (drive types, local or network, where are the data and log files stored, ...) to make educated guesses. Also is Instant File Initialization enabled on both instances? Same CPUs and amount of RAM? As an aside I'd be inclined to increase the growth increment to have fewer larger growths, but that is the same on both so won't be the reason for a difference. Mar 19, 2021 at 17:05

2 Answers 2

0

We are running into the issue that our stored procedure on our new SQL Server performs a lot slower than on the old server.

Use the Session Wait Stats and the Query Store to figure out if there are more waits (like IO) on the new server or if the query plan is worse.

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.

0

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.

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.