Our client outsourced their SQL server environment to a large multinational IT consultant company. (I don't want to mention the name). They are using a Hitachi Universal Storage Platform. When our client moved, the performance was reduced to a third of original performance. After some investigations, I can see that there is a IO bottleneck. When I run the code below I get 140125054 io_stall (ms) on four files in tempdb. (the avg_stall is 151ms/file) The outsourcing partner is stubborn and claims that RAID 5 will be good enough for tempDB, and I claims the opposite (and recommendation from Hitatchi and Microsoft claims the same as me). How do i convince the outsourcing partner that they are soo wrong. (The server was restarted in less than a week)
The database is 1Tb and is used in a telecom billing application.
The median for a write operation is 40ms in the SAN.
SELECT db_name(database_id) AS Database_Name
, file_id
,CAST(num_of_reads AS BIGINT) AS [total_num_reads]
,CAST(num_of_writes AS BIGINT) AS [total_num_writes]
,CAST(io_stall_read_ms AS BIGINT)
/ CAST(CASE WHEN num_of_reads=0
THEN 1
ELSE num_of_reads
END AS BIGINT) AS [avg_read_stall]
,CAST(io_stall_write_ms AS BIGINT)
/ CAST(CASE WHEN num_of_writes=0
THEN 1
ELSE num_of_writes
END AS BIGINT) AS [avg_write_stall]
,io_stall_write_ms
,io_stall_read_ms
,CAST(num_of_bytes_read AS BIGINT)/1024/1024 AS [total_bytes_read_mb]
,CAST(num_of_bytes_written AS BIGINT)/1024/1024 AS [total_bytes_written_mb]
,CAST(num_of_bytes_read AS BIGINT)
/ CAST(CASE WHEN num_of_reads=0
THEN 1
ELSE num_of_reads
END AS BIGINT)
/ 1024 AS [avg_read_size_Kbytes]
,CAST(num_of_bytes_written AS BIGINT)
/ CAST(CASE WHEN num_of_writes=0
THEN 1
ELSE num_of_writes
END AS BIGINT)
/ 1024 AS [avg_write_size_Kbytes]
FROM sys.dm_io_virtual_file_stats(NULL,NULL)
