How to Optimize SQL Server Performance by Separating Workloads

How to Optimize SQL Server Performance by Separating Workloads

by

I wrote this to share a series of scripts that help identify if workloads are appropriately separated on the SQL Server host. The main reason we look for this is to avoid I/O contention and waits, which is a challenge that can significantly impact performance. If you are unsure if your server is experiencing I/O contention and waits, visit this Microsoft Learn article for ways to investigate.

The strategy to alleviate this is to separate SQL Server workloads (DATA, LOG, TEMPDB, and BACKUPS) onto different drives other than C:\ to avoid the crash that could come with SQL Server file growth eating up all the available space!

Understanding SQL Server Workloads

  • DATA – .mdf, .ndf: These are the heart of your database, storing the actual data.
  • Transaction Log Files (LOG – .ldf): These files record all transactions and database modifications.
  • Temporary Database Files (TEMPDB): TEMPDB is used for temporary storage – for sorting, indexing, and more. These share the same file structures as the data and log files above but should be separated due to the differences in workload. Ideally, most TempDB work would be done in memory, so here is your PSA to not skimp on your SQL server host’s memory!
  • Backup Files (BACKUPS – .bak, .dif, .trn, ): Backup operations are I/O heavy and can slow down other processes if not isolated.

I/O contention occurs when these different workloads compete for disk resources, leading to delays and sluggish performance. High I/O uses can cause waits, blocking, or otherwise slow query responses and can even hinder routine maintenance tasks. By allocating separate drives for each workload, you reduce competition for I/O resources. This separation leads to faster read/write operations and overall better performance. Along with different disks for different workloads, there are best practices in getting the correct number of files for TempDB, as well as more advanced configurations involving partitions or multiple data drives. I will not talk about these today, but there are plenty of resources to assist, and feel free to call us, we would love to help you out!

Here are the scripts I use to help identify the issue with our clients:

/*==========================================================*/
/*				Separation of Workloads Report				*/
/*						David Seis							*/
/*                   11/27/2023 - 1.0                       */
/* =========================================================*/

/* <www.straightpathsql.com> */

/* Current checks:
- User Database Data or log files are on the same drive as TempDB.
- User Database and log files are on the same drive.
- TempDB files are not together (this is not necessary, just typical unless an extra tempdb log drive is also provisioned).
- TempDB is on C:\.
- User Database data or log files are on C:\.
- Default data, log, or backup path is on C:\.

There is a secondary table for verification/ logging that reports on:
- Current Tempdb file locations and number of files.
- Current System DB file locations and number of files.
- Current User DB data file locations and number of files.
- Current User DB log file locations and number of files.
- Current backup locations including third party GUID, netowrk share, or local address with (hopefully) and effective method to remove timestamps to help grouping.
*/



DECLARE @TempDBlocation nvarchar(1) = (SELECT LEFT(mfr.physical_name,1) FROM  sys.master_files mfr WHERE mfr.type_desc = 'ROWS' AND mfr.file_id = 1 AND mfr.database_id = 2 )

SET NOCOUNT ON

IF OBJECT_ID('tempdb..#Results') IS NOT NULL
DROP TABLE #Results

CREATE TABLE #Results (
ServerName NVARCHAR(100)
, Issue NVARCHAR(255)
)

INSERT #Results
SELECT 
@@SERVERNAME
, 'Database Files ('+d.name+') are on the same drive as TEMPDB (' + @TempDBlocation +':\)'
FROM  sys.master_files mfr
INNER JOIN sys.master_files mfl ON mfl.database_id = mfr.database_id AND mfl.type_desc = 'LOG'
INNER JOIN sys.databases d ON d.database_id = mfr.database_id
WHERE LEFT(mfr.physical_name, 1) = @TempDBlocation
AND d.database_id > 4
OR  LEFT(mfl.physical_name, 1) = @TempDBlocation
AND d.database_id > 4

INSERT #Results
SELECT Distinct
@@SERVERNAME
,  'Database ('+d.name+') Log and Data Files are on the same drive: ' + LEFT(mfr.physical_name,3) 
FROM  sys.master_files mfr
INNER JOIN sys.master_files mfl ON mfl.database_id = mfr.database_id AND mfl.type_desc = 'LOG'
INNER JOIN sys.databases d ON d.database_id = mfr.database_id
WHERE mfr.type_desc = 'ROWS' AND
LEFT(mfr.physical_name, 1) = LEFT(mfl.physical_name, 1)
AND d.name <> 'tempdb'
AND d.database_id > 4

INSERT #Results
SELECT Distinct
@@SERVERNAME
, 'TempDB Log and Data Files are NOT on the same drive' 
FROM  sys.master_files mfr
INNER JOIN sys.master_files mfl ON mfl.database_id = mfr.database_id AND mfl.type_desc = 'LOG'
INNER JOIN sys.databases d ON d.database_id = mfr.database_id
WHERE mfr.type_desc = 'ROWS' AND
LEFT(mfr.physical_name, 1) <> LEFT(mfl.physical_name, 1)
AND d.name = 'tempdb'

INSERT #Results
SELECT Distinct
@@SERVERNAME
, 'TempDB files are on C:\' 
FROM  sys.master_files m
INNER JOIN sys.databases d ON d.database_id = m.database_id
WHERE LEFT(m.physical_name, 1) LIKE 'C%'
AND d.name = 'tempdb'

INSERT #Results
SELECT Distinct
@@SERVERNAME
, 'Database data or log files are on C:\ for ' + d.name
FROM  sys.master_files m
INNER JOIN sys.databases d ON d.database_id = m.database_id
WHERE LEFT(m.physical_name, 1) LIKE 'C%'
AND d.name <> 'tempdb'
AND d.database_id > 4

INSERT #Results
SELECT 
@@SERVERNAME
, 'Default DATA path is on ' + CAST(SERVERPROPERTY('InstanceDefaultDataPath') AS NVARCHAR(200)) 
WHERE CAST(SERVERPROPERTY('InstanceDefaultDataPath') AS NVARCHAR(200)) LIKE 'C:\%'

INSERT #Results
SELECT 
@@SERVERNAME
, 'Default LOG path is on ' + CAST(SERVERPROPERTY('InstanceDefaultLogPath') AS NVARCHAR(200)) 
WHERE CAST(SERVERPROPERTY('InstanceDefaultLogPath') AS NVARCHAR(200)) LIKE 'C:\%'

INSERT #Results
SELECT 
@@SERVERNAME
, 'Default BACKUP Path is on ' + CAST(SERVERPROPERTY('InstanceDefaultBackupPath') AS NVARCHAR(200)) 
WHERE CAST(SERVERPROPERTY('InstanceDefaultBackupPath') AS NVARCHAR(200)) LIKE 'C:\%'

SELECT * FROM #Results
DROP TABLE #Results


IF OBJECT_ID('tempdb..#Checklist') IS NOT NULL
DROP TABLE #Checklist

CREATE TABLE #Checklist (
ServerName NVARCHAR(100)
, Checks NVARCHAR(255)
)

INSERT #Checklist
SELECT Distinct @@servername as [Server Name]
, 'TempDB File Location: [' + LEFT(mfr.physical_name,3) + ']. Number of files: ['+CAST(COUNT(LEFT(mfr.physical_name,3)) AS NVARCHAR(10))+'].'
FROM  sys.master_files mfr
INNER JOIN sys.master_files mfl ON mfl.database_id = mfr.database_id AND mfl.type_desc = 'LOG'
WHERE mfr.database_id = 2
GROUP BY LEFT(mfr.physical_name,3)

INSERT #Checklist
SELECT Distinct @@servername as [Server Name]
, 'System DB (DATA+LOG- no issue if together) File Location: [' + LEFT(mfr.physical_name,3) + ']. Number of files: ['+CAST(COUNT(LEFT(mfr.physical_name,3)) AS NVARCHAR(10))+'].'
FROM  sys.master_files mfr
INNER JOIN sys.master_files mfl ON mfl.database_id = mfr.database_id AND mfl.type_desc = 'LOG'
WHERE mfr.database_id in (1,3,4)
GROUP BY LEFT(mfr.physical_name,3)

INSERT #Checklist
SELECT Distinct @@servername as [Server Name]
, 'User DB DATA File Location: [' + LEFT(mfr.physical_name,3) + ']. Number of files: ['+CAST(COUNT(LEFT(mfr.physical_name,3)) AS NVARCHAR(10))+'].'
FROM  sys.master_files mfr
INNER JOIN sys.master_files mfl ON mfl.database_id = mfr.database_id AND mfl.type_desc = 'LOG'
WHERE mfr.database_id > 4
AND mfr.type_desc = 'ROWS'
GROUP BY LEFT(mfr.physical_name,3)

INSERT #Checklist
SELECT Distinct @@servername as [Server Name]
, 'User DB LOG File Location: [' + LEFT(mfr.physical_name,3) + ']. Number of files: ['+CAST(COUNT(LEFT(mfr.physical_name,3)) AS NVARCHAR(10))+'].'
FROM  sys.master_files mfr
INNER JOIN sys.master_files mfl ON mfl.database_id = mfr.database_id AND mfl.type_desc = 'LOG'
WHERE mfr.database_id > 4
AND mfr.type_desc = 'LOG'
GROUP BY LEFT(mfr.physical_name,3)



SELECT DISTINCT 
CASE 
WHEN f.physical_device_name LIKE '%:\%' 
OR f.physical_device_name LIKE '\\%' 
THEN CASE
WHEN LEN(f.physical_device_name) > 75 THEN LEFT(f.physical_device_name, 30)
WHEN LEN(f.physical_device_name) > 50 THEN LEFT(f.physical_device_name, 20)
WHEN LEN(f.physical_device_name) > 30 THEN LEFT(f.physical_device_name, 15)
WHEN LEN(f.physical_device_name) > 10 THEN LEFT(f.physical_device_name, 10)
ELSE f.physical_device_name
END
ELSE 'Third-Party GUID' 
END AS [BackupLoc]
, b.database_name as [DB Name]
, b.type
Into #temp
FROM msdb.dbo.backupmediafamily f  
INNER JOIN msdb.dbo.backupset b ON f.media_set_id = b.media_set_id 
INNER JOIN sys.databases s on b.database_name = s.name  
WHERE s.database_id > 4 
AND (b.backup_start_date >=CONVERT(VARCHAR(10),dateadd(day,-30,getdate()),120)) 


INSERT #Checklist
SELECT DISTINCT @@servername as [Server Name]
,	'FULL Backup Location: ' + backuploc
FROM #temp
WHERE type = 'D'

INSERT #Checklist
SELECT DISTINCT @@servername as [Server Name]
,	'DIFF Backup Location: ' + backuploc 
FROM #temp
WHERE type = 'I'

INSERT #Checklist
SELECT DISTINCT @@servername as [Server Name]
,	'FULL Backup Location: ' + backuploc 
FROM #temp
WHERE type = 'L'

DROP TABLE #temp


SELECT * FROM #Checklist
DROP TABLE #Checklist


SET NOCOUNT OFF

Utilizing Scripts to Identify Issues

These scripts are part of the discovery side of proactive work. Fixing these issues will likely require a downtime window to fix. However, the script is very easy to interpret. You don’t want anything on C:\ (system databases sometimes get the pass, but we would insist on fixing it on the subsequent migration), and every other workload type should have its own drive(s) that aren’t shared with other file types. Feel free to make changes that make it easier for you to use to your own ends.

Please be sure to test the script in a development environment first and test your file relocation process there. There are tools that make it easy, such as DBATools– DBA tools is a repository of PowerShell scripts that make many common DBA tasks much easier and faster, so check those out if you haven’t already. As always, we recommend working with experienced DBAs on this to ensure things go smoothly.

Conclusion

Separating SQL Server workloads into different drives is a simple and effective strategy to enhance performance and reliability. It’s a proactive approach to managing I/O, ensuring that your database environment remains robust and responsive. Use the provided script to get a clear picture of your current setup and take steps toward optimization.

And let us know what you find!

Subscribe for Updates

Name

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Share This