Last week, I had the pleasure of attending the GIDS conference and meeting many passionate developers. It was truly inspiring to hear from readers of this blog and their positive feedback motivates me to write more. One of the topics I discussed during a session on performance was the importance of keeping data files and log files on separate drives. In this blog post, we will explore this concept further and understand why it is crucial for optimal SQL Server performance.
After my session, an attendee approached me with a question. He mentioned that they had hundreds of databases running in their environment and wanted to identify which databases had both the data files and log files on the same drive. This question intrigued me as I realized I hadn’t covered this topic in my previous blog posts. So, I decided to dive into the SQL Server metadata and DMVs to find a solution.
Here is the script I came up with:
SELECT SERVERPROPERTY('machinename') AS 'Server Name',
ISNULL(SERVERPROPERTY('instancename'), SERVERPROPERTY('machinename')) AS 'Instance Name',
name,
drive_letter AS 'Drive Letter',
Comments, Path
FROM
(
(
SELECT DISTINCT
UPPER(LEFT(LTRIM(physical_name),2)) AS drive_letter,
REVERSE(RIGHT(REVERSE(physical_name),(LEN(physical_name)-CHARINDEX('\', REVERSE(physical_name),1))+1)) [Path],
N'Device holds both tempdb and user database objects' AS 'Comments',
DB_NAME(database_id) [name],
1 AS OrderBy
FROM MASTER.sys.master_files
WHERE LOWER(DB_NAME(database_id)) = 'tempdb'
AND UPPER(LEFT(LTRIM(physical_name),2)) IN
(
SELECT UPPER(LEFT(LTRIM(physical_name),2))
FROM MASTER.sys.master_files
WHERE LOWER(DB_NAME(database_id)) NOT IN (N'tempdb', N'master', N'msdb',
N'adventureworks', N'adventureworksdw', N'model')
)
)
UNION
(
SELECT drive_letter, path,
N'Device holds both data and log objects' AS 'Comments', name,
2 AS OrderBy
FROM
(
SELECT drive_letter, name, Path
FROM
(
SELECT DISTINCT UPPER(LEFT(LTRIM(physical_name),2)) AS drive_letter,
REVERSE(RIGHT(REVERSE(physical_name),(LEN(physical_name)-CHARINDEX('\', REVERSE(physical_name),1))+1)) [Path],
TYPE, DB_NAME(database_id) [name] FROM MASTER.sys.master_files
WHERE LOWER(DB_NAME(database_id))
NOT IN (N'master', N'msdb', N'tempdb', N'adventureworks', N'adventureworksdw', N'model')
) a
GROUP BY drive_letter, a.name, path
HAVING COUNT(1) >= 2
) Drives
)
) Drive
ORDER BY OrderBy, drive_letterWhen you run this script, it will provide you with a similar output, showing the databases where the data and log files are on the same drive. This information can be valuable for identifying potential performance bottlenecks and taking appropriate actions to optimize your SQL Server environment.
If you have ever needed a script like this and couldn’t find one, I would love to hear from you. Additionally, if there are any other simple daily scripts that would make your life easier, please let me know. I am always looking for new ideas to share on this blog.
Stay tuned for more SQL Server concepts and ideas in future blog posts!