May 12, 2015 at 9:54 am
I ran following Glen Berry's query on my Prod server:
SELECT DB_NAME(fs.database_id) AS [Database Name], mf.physical_name, io_stall_read_ms, num_of_reads,
CAST(io_stall_read_ms/(1.0 + num_of_reads) AS NUMERIC(10,1)) AS [avg_read_stall_ms],io_stall_write_ms,
num_of_writes,CAST(io_stall_write_ms/(1.0+num_of_writes) AS NUMERIC(10,1)) AS [avg_write_stall_ms],
io_stall_read_ms + io_stall_write_ms AS [io_stalls], num_of_reads + num_of_writes AS [total_io],
CAST((io_stall_read_ms + io_stall_write_ms)/(1.0 + num_of_reads + num_of_writes) AS NUMERIC(10,1))
AS [avg_io_stall_ms]
FROM sys.dm_io_virtual_file_stats(null,null) AS fs
INNER JOIN sys.master_files AS mf WITH (NOLOCK)
ON fs.database_id = mf.database_id
AND fs.[file_id] = mf.[file_id]
ORDER BY avg_io_stall_ms DESC OPTION (RECOMPILE);
the Prod server is UP for 3 weeks and got tempdb i/o:
have 1 mdf and 1 ldf file on same drive.
mdf file
Ave_Read_stall_ms = 48.7
avg_Write_Stall_ms = 944
Ave_io_Stall_ms = 491
ldf file
Ave_Read_stall_ms = 0.5
avg_Write_Stall_ms = 218
Ave_io_Stall_ms = 217
My question is to fix the issue, do I have to create multiple ndf file and put in different drive or just create more ndf files in the same drive?
What are the other option to minimize the issue? it's a physical machine, sql 2008r2.
thanks
May 12, 2015 at 12:14 pm
Tac11 (5/12/2015)
I ran following Glen Berry's query on my Prod server:SELECT DB_NAME(fs.database_id) AS [Database Name], mf.physical_name, io_stall_read_ms, num_of_reads,
CAST(io_stall_read_ms/(1.0 + num_of_reads) AS NUMERIC(10,1)) AS [avg_read_stall_ms],io_stall_write_ms,
num_of_writes,CAST(io_stall_write_ms/(1.0+num_of_writes) AS NUMERIC(10,1)) AS [avg_write_stall_ms],
io_stall_read_ms + io_stall_write_ms AS [io_stalls], num_of_reads + num_of_writes AS [total_io],
CAST((io_stall_read_ms + io_stall_write_ms)/(1.0 + num_of_reads + num_of_writes) AS NUMERIC(10,1))
AS [avg_io_stall_ms]
FROM sys.dm_io_virtual_file_stats(null,null) AS fs
INNER JOIN sys.master_files AS mf WITH (NOLOCK)
ON fs.database_id = mf.database_id
AND fs.[file_id] = mf.[file_id]
ORDER BY avg_io_stall_ms DESC OPTION (RECOMPILE);
the Prod server is UP for 3 weeks and got tempdb i/o:
have 1 mdf and 1 ldf file on same drive.
mdf file
Ave_Read_stall_ms = 48.7
avg_Write_Stall_ms = 944
Ave_io_Stall_ms = 491
ldf file
Ave_Read_stall_ms = 0.5
avg_Write_Stall_ms = 218
Ave_io_Stall_ms = 217
My question is to fix the issue, do I have to create multiple ndf file and put in different drive or just create more ndf files in the same drive?
What are the other option to minimize the issue? it's a physical machine, sql 2008r2.
thanks
Those are bad numbers. It's a best practice to put data and log files on different drives.
For tempdb best practices see:
-- Itzik Ben-Gan 2001
May 12, 2015 at 6:10 pm
Thanks Alan. Do you think this is the main reason the numbers are high?
May 12, 2015 at 10:36 pm
Hard to say, it could be a lot of things. What I can say for sure is splitting the data into more files will help and separating the ldfs and mdfs/ndfs onto seperate drives will help.
Bad indexing and/or bad queries, for example, will cause excessive & expensive sorts especially when your system is configured to sort in your tempdb. Something I have seen that can devastate your tempdb are what are commonly referred to as, "Temp DB spills".
Having your tempdb on the wrong type of disk will hurt you too (I think that's covered in the aforementioned article). SQLServer likes fast disks for tempdb. LDF and MDF/NDF file auto-growth hurts performance too and will raise those numbers. I generally like to have my tempdb data files on their own disk and fill up most of the disk with them so that that autogrow does is not likely.
There's a science to managing your tempdb. I recommend reading the article I posted, Brent Ozar has some good articles out there too.
-- Itzik Ben-Gan 2001
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply