Sql-server – Tempdb log growth 3x as slow on new server, SP does not perform

auto-growthperformancesql serversql-server-2019

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?

Best Answer

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.

Related Solutions

SQL Server – Cursor Performance on New Server

Im sorry so long not update this question. I solved this case 2 months after this question.

After few times trial and errors, my biggest impact in performance is using separated harddisk between Operation System and Data.

I try to bought new harddisk and look like changing storage system from RAID to AHCI give quite difference in performance for data processing. Well, we do 1 hour backup in Maintenance Job for safety to online cloud service.

I do some changes in BIOS settings which also gives many impacts to performance descried in this pdf http://h20195.www2.hp.com/v2/GetPDF.aspx%2F4AA3-6555ENW.pdf

Im also upgrading memory to 4x8GB

Comparing to my old server its 8 times faster now.

Thank you for helps.

SQL Server 2014 – History of Growth and Shrink Events

Is there a way to look at the complete history of growth and shrink events for the last weeks/months on SQL Server 2014

Yes there is a way, or should i say various ways to save the data collected from default trace as explained here Collecting the Information in the Default Trace

We could query the default trace files and save aggregated values

We could get the definition of the default trace and create a collection set, which can be used with Management Data Warehouse, which is built in with SQL Server.

We could get the definition of the default trace and create a remote trace from another server and save the data to a database table

We could use a robocopy script to copy the trace files from our server to a different folder or a remote machine, which will leave the files on disk for longer time, giving us the possibility to analyze them

Came up with below query, but it doesn't show any of the "manual" log shrink events, just the "auto" events. Am I doing this the wrong way, any other place to look for past info ?To capture the manual shrink events i use query like below:

SELECT 
    TextData,
    HostName,
    ApplicationName,
    LoginName, 
    StartTime  
FROM 
[fn_trace_gettable]('C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\log_75.trc', DEFAULT) 
WHERE TextData LIKE '%SHRINKFILE%'; ----- Location of default trace will be different ,so kindly check that accordingly

Probable reason you're query does not display manual grow or shrink events because you use events like (93, 95) which are for auto grow events

Best Answer

Related Solutions

SQL Server – Cursor Performance on New Server

SQL Server 2014 – History of Growth and Shrink Events

Related Question