September 16, 2013 at 10:54 pm
Hi, I'm using the below query to find out the IO stalls on my SharePoint databases and the values are very very high tempdb and all other content databases also.
SELECT DB_NAME(database_id) AS [Database Name] , file_id , 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)ORDER BY avg_io_stall_ms DESC ;
Results:
Database Namefile_idio_stall_read_msnum_of_readsavg_read_stall_msio_stall_write_msnum_of_writesavg_write_stall_msio_stallstotal_ioavg_io_stall_ms
tempdb1737963751935791.41342605641878088091719.313433436055130023881033.2
tempdb3770470851915841.51342809255378188501717.413435797261130104341032.7
WSS_Logging136889260537913329466.21141437489807712.737003404278811406419.9
Questions:
1. Is this DMV giving cumulative values for io_stall_read_ms and io_stall_write_ms from last sql service restart?
2. We are taking content database full backups daily, which will take 8 hours to complete. Is full backup creates any IO on
tempDB and content databases?
3. Running DBCC CHECKDB on weekly, taking 8 hours to complete. Is DBCC CHECKDB is causing this high IO stalls?
4. Tempdb is data files and log files are on separate drives. We have 2 mdf files on same drive with equal size since we
have 2 cpus and ldf file is on separate drive. Tempdb is pre sized to 50 GB. Indexes are healthy. But still we are seeing
high IO wait times?
Can you please give step by step details on how to run SQLIO tool to see the SAN IO stats?
September 17, 2013 at 2:54 am
So is sharepoint slow? Or you are just seeing this in DMV?
Questions:
1. Is this DMV giving cumulative values for io_stall_read_ms and io_stall_write_ms from last sql service restart?
Yes, that's correct, it takes every single IO read/write and is a cumulative average. To get read/write latency (io stalls) for a period of time, you can take the difference of 1days or of 1hours, or 1 min then divide num reads/io read ms num writes/io write ms
1) Run the query, then re-run again in 1 day
2) Run the query, then re-run again in 1 hour
etc..
Then take the difference of the two numbers, this will give you a more granular view of the performance.
2. We are taking content database full backups daily, which will take 8 hours to complete. Is full backup creates any IO on
tempDB and content databases?
not tempdb but any user dbs such as content databases should generate I/O - more read IO than write IO during backups in user dbs.
3. Running DBCC CHECKDB on weekly, taking 8 hours to complete. Is DBCC CHECKDB is causing this high IO stalls?
This causes IO in tempdb as well as IO in your user dbs that are running checkdb. Yes it's possible. Try to take compare after a shorter period of time and take the difference rather than the cumulative over time since last reboot. It's possible this contributes to the skewing the DMW query results.
4. Tempdb is data files and log files are on separate drives. We have 2 mdf files on same drive with equal size since we
have 2 cpus and ldf file is on separate drive. Tempdb is pre sized to 50 GB. Indexes are healthy. But still we are seeing
high IO wait times?
Are you seeing high IO wait times from the query or on SharePoint? Are you on direct attached local storage or on a SAN? Have you looked at the wait stats dmv also as an additional perspective on what is occurring on server.
Can you please give step by step details on how to run SQLIO tool to see the SAN IO stats?
You should be able to google step by step references and examples on the web.
--------------------------------------------------
...0.05 points per day since registration... slowly crawl up to 1 pt per day hopefully 😀
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply