Just another WordPress.com site
Balancing Multiple Drives IO Operation by Distributing Data Files in Share SQL Environment
May 6, 2011Posted by on
Recently i worked on a share SQL server environment where many SharePoint 2010 databases were present in a Single SQL server instance. Those SharePoint databases have been upgraded from 2007 version to 2010, rendering some of the databases in SharePoint 2007 become obsolete. These databases can be deleted from the server to free up space in the drives.
The SQL server has five logical drives connecting to individual LUN in SAN, but the users were complaining about the application’s latency. The users had been experiencing the ongoing issue regarding latency for a long period of time.
Although SharePoint creates several databases in the SQL server, due to space limitation and logical drives, the data files spread randomly to different drives in the server based on data file size.
We ran the perfmon and collect data for the server. Using in depth analysis on perfmon data we figured out that the databases in the server had a performance problem with IO request.
Due to the file spread on multiple drives, it is possible to have several busy data files present in the same drive and these files share the same IO resources in the LUN.
The following query was executed to find out the individual database files IO access statistics in the entire SQL Instance:
declare @Mbytes_read as dec(18, 2)
declare @Mbytes_written as dec(18, 2)
declare @disk_Mbytes as dec(18, 2)
@Mbytes_read = SUM(num_of_bytes_read),
@Mbytes_written = SUM(num_of_bytes_written),
@disk_Mbytes = SUM(size_on_disk_bytes)
FROM sys.dm_io_virtual_file_stats (NULL, NULL)
where file_id <> 2
select DB_NAME(s.database_id) as [Database],
CONVERT(VARCHAR(12),CAST((num_of_bytes_read/(1024*1024)) as dec(10,2))) + ‘ (‘ + CONVERT(varchar(12),CONVERT(dec(11, 2), num_of_bytes_read*100.0/@Mbytes_read)) + ‘ %)’ AS ‘Mbytes_read (%)’,
ReadLatency = CASE WHEN num_of_reads = 0 THEN 0 ELSE (io_stall_read_ms / num_of_reads) END,
CONVERT(VARCHAR(12),CAST((num_of_bytes_written/(1024*1024)) as dec(10,2))) + ‘ (‘ + CONVERT(varchar(12),CONVERT(dec(11, 2), num_of_bytes_written*100.0/@Mbytes_written)) + ‘ %)’ AS ‘Mbytes_written (%)’,
WriteLatency = CASE WHEN num_of_writes = 0 THEN 0 ELSE (io_stall_write_ms / num_of_writes) END,
CONVERT(VARCHAR(12),CAST((size_on_disk_bytes/(1024*1024)) as dec(10,2))) + ‘ (‘ + CONVERT(varchar(12),CONVERT(dec(11, 2), size_on_disk_bytes*100.0/@disk_Mbytes)) + ‘ %)’ AS ‘disk_Mbytes (%)’,
CONVERT(VARCHAR(12),CAST((io_stall_read_ms/(1000*60)) as dec(10,2))) + ‘ (‘ + CONVERT(varchar(12),CONVERT(dec(11, 2), io_stall_read_ms*100.0/io_stall)) + ‘ %)’ AS ‘io_stall_read_minute (%)’,
CONVERT(VARCHAR(12),CAST((io_stall_write_ms/(1000*60)) as dec(10,2))) + ‘ (‘ + CONVERT(varchar(12),CONVERT(dec(11, 2), io_stall_write_ms*100.0/io_stall)) + ‘ %)’ AS ‘io_stall_write_minute (%)’,
CAST((io_stall/(1000*60)) as dec(10,2)) as io_stall_minute,
f.name as [Logical Name],
substring(f.filename,0,2) as [Drive Letter],
f.filename as [File Full Path]
from sys.sysaltfiles f
inner join sys.dm_io_virtual_file_stats (NULL, NULL) s
on f.dbid = s.database_id
and f.fileid = s.file_id
where s.file_id 2
order by DB_NAME(s.database_id)
The screenshot below is a portion of the query output:
I have calculated the percentage of “read and write” for individual data files compared to entire SQL Server. I divided the individual data file read and write value with the entire server read and write value to determine each of the percentages. This query output also included the read and write latency as well as total disk space used by each data file.
The following instructions highlight the steps for obtaining the data above to improve the server IO requests:
1. Delete obsolete database to free up space in drive (Make sure I took and saved the latest database full backup in case if we need these database again).
2. Keep the biggest data files in the existing drive.
3. Move the data files to different drives based on Read Write percentage values of these files so each drive does not flood with most read or write data files.
4. I found tempDB has the most read and write percentage so i moved tempDB data files completely separate high performance LUN.