What's the server IO load with MSSQL
- Microsoft SQL Server is a very complex software, that does a lot of work. How busy is it? What's the server load?
- Microsoft SQL Server's job is save data and later retrieve it: SQL = Structure Query Language. These are disk activities and you should monitor them.
- Like most SQL Server, MSSQL provides many tools for monitoring the IO information.
- When switching from MSSQL 2000 to MSSQL 2005, Microsoft has switched from stored procedures to Dynamic Management Views.
- This will tell you if you will need to upgrade to faster disks.
Applies to:
- Microsoft SQL Server 2000
- Microsoft SQL Server 2005
SQL Server IO for Microsoft SQL Server 2000
- SELECT * FROM FN_VIRTUALFILESTATS(DEFAULT, DEFAULT);
- go
DbId FileId TimeStamp NumberReads BytesRead IoStallReadMS NumberWrites BytesWritten IoStallWriteMS IoStallMS BytesOnDisk FileHandle ------ ------ ----------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- ------------------ 1 1 89523656 77 630784 736 1 8192 1 737 4194304 0x0000052C 1 2 89523656 11 400384 25 22 80384 270 295 524288 0x00000534 2 1 89523656 61 499712 306 2 16384 2 308 11534336 0x0000067C 2 2 89523656 8 516096 0 6 92672 41 41 524288 0x00000674 3 1 89523656 38 1400832 307 2 16384 2 309 1245184 0x0000056C 3 2 89523656 10 425984 70 7 36864 68 138 524288 0x00000644 4 1 89523656 65 532480 1452 1 8192 24 1476 5963776 0x00000708 4 2 89523656 8 397312 203 5 16896 306 509 786432 0x00000734 5 1 89523656 38 311296 1142 1 8192 15 1157 3342336 0x0000070C 5 2 89523656 8 397312 183 5 14336 70 253 786432 0x0000072C 6 1 89523656 30 245760 1069 1 8192 8 1077 2293760 0x00000714 6 2 89523656 8 330752 291 5 11776 197 488 786432 0x0000073C 7 1 89523656 35 286720 1485 1 8192 28 1513 71827456 0x00000720 7 2 89523656 9 163840 242 5 61440 77 319 2097152 0x00000740 8 1 89523656 49 450560 872 1 8192 1 873 188678144 0x00000748 8 2 89523656 9 401408 138 5 24576 47 185 2097152 0x0000074C 9 1 89523656 23 188416 472 1 8192 11 483 5242880 0x00000744 9 2 89523656 9 401408 90 5 11264 57 147 2097152 0x00000750 10 1 89523656 84 688128 345 1 8192 7 352 15728640 0x00000790 10 2 89523656 15 148480 300 4 25600 35 335 1835008 0x000007A0 (20 row(s) affected)
SQL Server IO for Microsoft SQL Server 2005
- SELECT SUBSTRING(name,1,20) AS 'Database',
- CASE FILE_ID WHEN 1 THEN 'Data'
- WHEN 2 THEN 'Log'
- END AS 'Type',
- num_of_reads,
- num_of_bytes_read,
- num_of_writes,
- num_of_bytes_written,
- size_on_disk_bytes,
- io_stall_read_ms,
- io_stall_write_ms,
- io_stall
- FROM sys.dm_io_virtual_file_stats(null,null),
- sys.databases
- WHERE sys.databases.database_id = sys.dm_io_virtual_file_stats.database_id;
- go
Database Type num_of_reads num_of_bytes_read num_of_writes num_of_bytes_written size_on_disk_bytes io_stall_read_ms io_stall_write_ms io_stall -------------------- ---- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- master Data 77 630784 1 8192 4194304 736 1 737 master Log 11 400384 22 80384 524288 25 270 295 tempdb Data 61 499712 2 16384 11534336 306 2 308 tempdb Log 8 516096 6 92672 524288 0 41 41 model Data 38 1400832 2 16384 1245184 307 2 309 model Log 10 425984 7 36864 524288 70 68 138 msdb Data 65 532480 1 8192 5963776 1452 24 1476 msdb Log 8 397312 5 16896 786432 203 306 509 ReportServer Data 38 311296 1 8192 3342336 1142 15 1157 ReportServer Log 8 397312 5 14336 786432 183 70 253 ReportServerTempDB Data 30 245760 1 8192 2293760 1069 8 1077 ReportServerTempDB Log 8 330752 5 11776 786432 291 197 488 AdventureWorksDW Data 35 286720 1 8192 71827456 1485 28 1513 AdventureWorksDW Log 9 163840 5 61440 2097152 242 77 319 AdventureWorks Data 49 450560 1 8192 188678144 872 1 873 AdventureWorks Log 9 401408 5 24576 2097152 138 47 185 AdventureWorksLT Data 23 188416 1 8192 5242880 472 11 483 AdventureWorksLT Log 9 401408 5 11264 2097152 90 57 147 sql911 Data 84 688128 1 8192 15728640 345 7 352 sql911 Log 15 148480 4 25600 1835008 300 35 335 (20 row(s) affected)
- Both, the fn_virtualfilestats and the sys.dm_io_virtual_file_stats provide the same information.
- You can just applies that information to just one database by select the dbid as the 1st parameter of fn_virtualfilestats(default or sys.dm_io_virtual_file_stats(null.
The important items are:
- Io_stall_read_ms: Total time, in milliseconds, waiting for the reads to be completed.
- Io_stall_write_ms: Total time, in milliseconds, waiting for the writes to be completed.
- Io_stall: Total time, in milliseconds, waiting for the IO to be completed.
If they get too large, then you will need to to upgrade to a faster disk subsystem.
