We observed numerous SQL sessions that were waiting for LOGBUFFER and IO_COMPLETION; all of these sessions were executing an INSERT INTO #Temp table, as illustrated below.
Upon examining the count of Virtual Log Files (VLFs) in the tempdb .ldf file, we discovered that the tempdb transaction log was significantly fragmented.
Subsequently, we executed defragmentation of the VLFs within the TempDB transaction log file, using the SQL commands provided below:
use tempdb
DBCC SHRINKFILE ( 'templog', 0, TRUNCATEONLY)
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = 'templog', SIZE = 150000MB )
The VLFs in the tempdb log file have been decreased to 32, as shown below:
We also observed that the DB Full Backup job was planned to run at that time. Thus, we rescheduled the backup job to operate during off-peak hours.
We found that the maximum memory allocation for SQL Server was set to 75% of the overall server memory, which we think was a result of the SQL Server installer GUI's suggestions.
Next, we increased the SQL max memory limit to 1216GB, representing 95% of the total 1.25TB, on the server, which is chiefly assigned to SQL server services. It should be adequate to reserve 64GB of RAM for the Windows operating system and other various tasks.
Finally, we relocated the tempdb transaction log file to a separate disk that offers improved write speed.
