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)