¡ÒÃÇÔ¹Ô¨©Ñ 㹠SP4 2000 à«ÔÃì¿àÇÍÃì SQL áÅÐÃØè¹·ÕèãËÁè¡ÇèÒªèǵÃǨ¨Ñº stalled áÅÐ stuck ´Óà¹Ô¹¡Òà I/O

Tracking the I/O request packet (IRP)

SQL Server uses the standard Microsoft Windows API calls to read and to write data. For example, SQL Server uses the following functions:

• WriteFile
• readfile
• WriteFileScatter
• ReadFileGather

The read or the write request is handled by Windows as an I/O request packet (IRP). To determine the state of the IRP, use both of the following:

• Microsoft Platform Support assistance.
• The kernel debugger.

For more information about IRP and IRP tracing, visit the following Microsoft Web site and search for the keyword "IRP":ËÁÒÂà˵Ø:Kernel debugging can be an invasive process because kernel debugging can require you to stop the system to complete the debugging actions. We recommend that you see if there are any updates available for the following items:

• The BIOS
• The firmware
• Any other I/O path components

Contact your hardware vendors before you perform additional debugging actions. The debug session will likely involve a third-party driver, firmware, or a filter driver component.

System performance and query plan actions

Overall system performance can play a key role in I/O processing. You should take the general health of the system into consideration when you are investigating reports of stalled or stuck I/O operations. Excessive loads can cause the overall system to be slow. This includes I/O processing. The behavior of the system at the time the problem occurs can be a key factor in determining the root cause of the problem. For example, if CPU usage becomes high or if CPU usage remains high when the problem occurs, this behavior may indicate that a process on the system is using so much CPU that other processes are being adversely affected.

Performance counters

To monitor I/O performance, examine the following performance counters for specific I/O path information:

• Average Disk Sec/Transfer
• Average Disk Queue Length
• Current Disk Queue Length

For example, the Average Disk Sec/Transfer time on a computer that is running SQL Server is typically less than 15 milliseconds. If the Average Disk Sec/Transfer value climbs, this indicates that the I/O subsystem is not optimally keeping up with the I/O demand.

ÃÐÇѧ¡Ñºà¤Ò¹ìàµÍÃìÇÑ´»ÃÐÊÔ·¸ÔÀÒ¾¡Ò÷ӧҹ·Õèä´éà¹×èͧ¨Ò¡ SQL Server ãªé»ÃÐ⪹ì¨Ò¡¤ÇÒÁÊÒÁÒö¢Í§ I/O ẺÍЫԧâ¤Ã¹ÑÊ·Õè´Ñ¹¤ÇÒÁÂÒǤÔÇ´ÔÊ¡ì heavily àµçÁ ´Ñ§¹Ñé¹ ¤ÇÒÁÂÒÇ´ÔÊ¡ì¤ÔÇÂÒÇà¾Õ§ÍÂèÒ§à´ÕÂÇ·ÕèäÁèÃкػѭËÒ

㹡ÒõÃǨÊͺÃкº¢Í§ Windows ¤Ø³ÊÒÁÒöµÃǨÊͺµÑǹѺ·Õè "´ÔÊ¡ì·Ò§¡ÒÂÀÒ¾: ´ÔÊ¡ì亵ì/ÇÔ¹Ò·Õ"ÊÓËÃѺáµèÅдÔÊ¡ì·Õèä´éÃѺ¼Å¡ÃзºáÅÐà»ÃÕºà·ÕºÍѵÃҢͧ¡Ô¨¡ÃÃÁ¨Ò¡µÑǹѺ·Õè"´Óà¹Ô¹¡ÒÃ: IO ¢éÍÁÙÅ亵ì/ÇÔ¹Ò·Õ"áÅÐ “ »ÃÐÁÇżÅ: IO 亵ì/ÇÔ¹Ò·Õà¾ÔèÁ ” ÊÓËÃѺáµèÅТÑ鹵͹㹡ÒÃÃкØÇèҪش੾ÒТͧ¡Ãкǹ¡Ò÷Õè¨ÐÊÃéÒ§ I/O ÁÒ¡à¡Ô¹ä»Ãéͧ¢Íä´é äÁèÁÕ I/O Í×è¹ æ µèÒ§ æ ·Õèà¡ÕèÂÇ¢éͧµÑǹѺ·Õè¾ÃéÍÁãªé§Ò¹ã¹¡Ãкǹ¡ÒÃÇѵ¶Ø·Õè reveals ¢éÍÁÙÅ granular ÁÒ¡¢Öé¹ ¶éҤسµÃǨÊͺÇèÒ ÍÔ¹Êáµ¹«ì SQL Server ·ÕèÃѺ¼Ô´ªÍºÊÓËÃѺ¡ÒÃâËÅ´ IO ÁÒ¡à¡Ô¹ä»º¹à«ÔÃì¿àÇÍÃì µÃǨ´ÙÊèǹ¶Ñ´ä»º¹ “´Ñª¹ÕáÅÐ Parallelism ” ÊÓËÃѺ¡ÒÃÃÒÂÅÐàÍÕ´ʹ·¹Ò㹡ÒõÃǨËÒ áÅСÒäÅÕè¤ÅÒ I/O bottlenecks µÃǨ´ÙÊèǹ “ I/O Bottlenecks ” ã¹ MSDN whitepaper¡ÒÃá¡é䢻ѭËÒ»ÃÐÊÔ·¸ÔÀÒ¾¡Ò÷ӧҹ㹠SQL Server 2008ËÃ×Í¡ÒÃá¡é䢻ѭËÒ»ÃÐÊÔ·¸ÔÀÒ¾¡Ò÷ӧҹ㹠SQL Server 2005.

´Ñª¹ÕáÅÐ parallelism

ºèͤÃÑé§ bursts ¢Í§ I/O à¡Ô´¢Öé¹à¹×èͧ¨Ò¡´Ñª¹Õ¢Ò´ËÒÂä» ÅѡɳСÒ÷ӧҹ¹ÕéÊÒÁÒö´Ñ¹¾Ò¸ I/O heavily ÃËÑʼèÒ¹´éÇ¡Ò÷ӴѪ¹Õ¡ÒÃà»Ô´µÑǪèÇÂÊÃéÒ§ (ITW) ÍÒ¨ªèÇÂá¡éä¢ I/O äǵèÍáç¡´º¹Ãкº ¶éÒẺÊͺ¶ÒÁä´é»ÃÐ⪹ì¨Ò¡´Ñª¹Õá·¹¨Ò¡¡ÒÃÊ᡹µÒÃÒ§ ËÃ×ÍÍÒ¨áÁéáµèãªéàÃÕ§ÅӴѺËÃ×ÍáÎ ÃкºÊÒÁÒöä´éÃѺ¢éʹմѧµèÍ仹Õé:

• ¡ÒÃÅ´¢¹Ò´¢Í§ I/O ¡ÒÂÀÒ¾à¾×èÍ·Ó¡ÒôÓà¹Ô¹¡ÒÃâ´ÂµÃ§ equates »ÃÐ⪹ì»ÃÐÊÔ·¸ÔÀÒ¾ÊÓËÃѺ¡ÒÃÊͺ¶ÒÁ
• Fewer pages in the data cache have to be turned over. Therefore, those pages that are in the data cache remain relevant to active queries.
• Sorts and hashes are used because an index may be missing or because statistics are out of date. You may reducetempdbuse and contention by adding one or more indexes.
• A reduction of resources, a reduction of parallel operations, or both. Because SQL Server does not guarantee parallel query execution, and because the load on the system is considered, it is best to optimize all queries for serial execution. To optimize a query, open Query Analyzer and set thesp_configure value¢Í§ÃдѺÊÙ§ÊØ´¢Í§ parallelismoption to 1. If all the queries are tuned to run promptly as a serial operation, parallel execution is often just a better result. However, many times parallel execution is selected because the amount of data is just large. For a missing index, a large sort may have to occur. Multiple workers that are performing the sort operation will create a quicker response. However, this action can dramatically increase the pressure on the system. Large read requests from many workers can cause an I/O burst together with increased CPU usage from multiple workers. Many times a query can be tuned to run faster and to use fewer resources if an index is added or if another tuning action occurs.

Practical examples from Microsoft SQL Server Support

The following examples have been handled by Microsoft SQL Server Support and Platforms Escalation Support. These examples are intended to provide a frame of reference and help set your expectations about stalled and stuck I/O situations and about how a system may be affected or may respond. There is no specific hardware or set of drivers that pose any specific risk or increased risk over another. All systems are the same in this respect.

Example 1: A log write that is stuck for 45 seconds

A SQL Server log file write attempt periodically became stuck for approximately 45 seconds. The log write was not finishing in a timely manner. This behavior created a blocking condition that led to 30-second client time-outs.

The application submitted a commit to SQL Server and the commit became stuck as a log write pending. This behavior caused the query to continue holding locks and to block incoming requests from other clients. Then, other clients started to time out. This compounded the problem because the application was not designed to roll back open transactions when a query time out occurred. This created hundreds of open transactions that were holding locks. Therefore, a severe blocking situation occurred.

For more information about transaction handling and blocking, click the following article number to view the article in the Microsoft Knowledge Base:The application serviced a Web site using connection pooling. As more connections became blocked, the Web site created more connections. These connections became blocked and the cycle continued.

After approximately 45 seconds, the log write completed. However, by this time, hundreds of connections were backed up. The blocking problems resulted in several minutes of recovery time for SQL Server and for the application. When combined with the application problems, the stalled I/O condition had a very negative affect on the system.

¡ÒÃá¡éä¢

The problem was tracked to a stuck I/O request in a Host Bus Adapter (HBA) driver. The computer had multiple HBA cards with failover support. When one HBA was behind or was not communicating with the Storage Area Network (SAN), the "retry before failover" time-out value was configured to 45 seconds. When the time-out was exceeded, the I/O request was routed to the second HBA. The second HBA handled the request and quickly finished. To help prevent such stall conditions, the hardware manufacturer recommended a "retry before failover" setting of 5 seconds.

Example 2: Filter driver intervention

Many antivirus software programs and backup products use I/O filter drivers. These I/O filter drivers become part of the I/O request stack, and they have access to the IRP request. Microsoft Product Support Services has seen various issues from bugs that create stuck I/O conditions or stalled I/O conditions in a filter driver implementation.

One such condition was a filter driver for backup processing that allowed a backup of the files that were open when the backup occurred. The system administrator had included the SQL Server data file directory in the file backup selections. When the backup occurred, the backup tried to gather the correct image of the file at the time the backup started. Doing this delayed I/O requests. The I/O requests were allowed to only complete one at a time as they were handled by the software.

When the backup started, SQL Server performance dropped dramatically because the I/Os of SQL Server were forced to complete one at a time. To compound the issue, the "one at a time" logic was such that the I/O operation could not be performed asynchronously. Therefore, when SQL Server expected to post an I/O request and to continue, the worker was stuck in the read or the write call until the I/O request finished. Processing tasks such as a SQL Server read ahead were effectively disabled by the actions of the filter driver. Additionally, another bug in the filter driver left the "one at a time" actions in process, even when the backup was finished. The only way to restore SQL Server performance was to close and then reopen the database, or to restart SQL Server so that the file handle was released and reacquired without the filter driver interaction.

¡ÒÃá¡éä¢

To resolve this problem, the SQL Server data files were removed from the file backup process. The software manufacture also corrected the problem that left the file in "one at a time" mode.

Example 3: Hidden errors

Many higher end systems have multichannel I/O paths to handle load balancing or similar activities. Microsoft Product Support has found problems with the load balancing software where an I/O request fails but the software does not handle the error condition correctly. The software can attempt infinite retries. The I/O operation becomes stuck and SQL Server cannot finish the specified action. Much like the log write condition that was described earlier, many poor system behaviors can occur after such a condition wedges the system.

¡ÒÃá¡éä¢

To resolve this problem, restarting SQL Server is often required. However, sometimes you must restart the operating system to restore processing. We also recommend that you obtain a software update from the I/O vendor.

µÑÇÍÂèÒ§·Õè 4: ¨Ñ´à¡çºÃÐÂÐä¡Å Mirroring áÅÐ Raid ä´Ã¿ì

Ãкº¨Ó¹Ç¹ÁÒ¡ãªéÁÔàÃÍÃì ËÃ×Í·ÓµÒÁ¢Ñ鹵͹·ÕèàËÁ×͹¡Ñ¹à¾×èÍ»éͧ¡Ñ¹¡ÒÃÊÙ­àÊÕ¢éÍÁÙÅ ºÒ§Êèǹ¢Í§Ãкº·ÕèãªéÁÔàÃÍÃì¤×Í«Í¿µìáÇÃì·Õèãªé áÅкҧÊèǹÁÕÎÒÃì´áÇÃì·Õèãªé ʶҹ¡Òóì·Õèâ´Â»¡µÔ uncovered â´ÂºÃÔ¡ÒÃʹѺʹع¼ÅÔµÀѳ±ìÊÓËÃѺÃкºàËÅèÒ¹ÕéÁÕàÇÅÒὧ·Õèà¾ÔèÁ¢Öé¹

¡ÒÃà¾ÔèÁàÇÅÒ I/O â´ÂÃÇÁ·Õèà¡Ô´¢Öé¹àÁ×èÍ I/O µéͧ´Óà¹Ô¹¡ÒÃÁÔàÃÍÃì¡è͹ I/O ¨Ð¶×Íà»ç¹àÊÃç¨ÊÁºÙóì ÊÓËÃѺ¡ÒõԴµÑé§ÊÓËÃѺÁÔàÃÍÃìÃÐÂÐä¡Å ¡ÒÃÅͧãËÁèËÅÒ¤ÃÑ駢ͧà¤Ã×Í¢èÒÂÊÒÁÒö¡ÅÒÂà»ç¹à¡ÕèÂǡѺ§Ò¹ àÁ×èÍà¡Ô´¤ÇÒÁÅéÁàËÅǢͧä´Ã¿ì áÅÐÃкº raid ¤×Í rebuilding ÅÇ´ÅÒ¢ͧ I/O ÊÒÁÒö¶Ù¡¢Ñ´¨Ñ§ËÇСÃкǹàªè¹¡Ñ¹

¡ÒÃá¡éä¢

µÑ駤èҤ͹¿Ô¡·Õèà¢éÁ§Ç´¨Óà»ç¹µéͧãªéà¾×èÍÅ´àÇÅÒὧ·ÕèÁÔàÃÍÃì ËÃ×Í¡ÒôÓà¹Ô¹¡Òà rebuild raid

µÑÇÍÂèÒ§·Õè 5: ¡ÒÃÃÇÁ

Microsoft äÁèʹѺʹع Microsoft SQL Server 7.0 ËÃ×Íä¿Åì¢éÍÁÙÅ Microsoft SQL Server 2000 áÅÐä¿ÅìºÑ¹·Ö¡·ÕèÍÂÙ躹ä´Ã¿ì·Õè¶Ù¡ºÕºÍÑ´ ¡ÒúպÍÑ´ ntfs äÁè»ÅÍ´ÀÑÂÊÓËÃѺ SQL Server ä´éà¹×èͧ¨Ò¡¡ÒúպÍÑ´ NTFS áºè§â¾Ãâ·¤ÍÅà¢Õ¹ËèÒ§ÅçÍ¡ (WAL) ¡ÒúպÍÑ´ ntfs Âѧµéͧ¡ÒûÃÐÁÇżÅà¾ÔèÁ¢Öé¹ÊÓËÃѺáµèÅСÒôÓà¹Ô¹¡Òà I/O ºÕºÍÑ´ÊÃéÒ§ "·Õ" àËÁ×͹¡ÑºÅѡɳСÒ÷ӧҹ·Õè·ÓãËéà¡Ô´»Ñ­ËÒ»ÃÐÊÔ·¸ÔÀÒ¾¡Ò÷ӧҹ·ÕèÃعáç·Õèà¡Ô´¢Öé¹

¡ÒÃá¡éä¢

àÁ×è͵éͧ¡ÒÃá¡é䢻ѭËÒ¹Õé uncompress ¢éÍÁÙÅáÅÐá¿éÁºÑ¹·Ö¡

¨Ø´¢éÍÁÙÅà¾ÔèÁàµÔÁ

¢éÍÁÙŪ¹Ô´¡ÒÃÃÍ·ÕèãËéäÇéã¹¹Ñé¹sysprocessesµÒÃÒ§ÃкºÍÒ¨à»ç¹»ÃÐ⪹ì㹡ÒÃÇÔà¤ÃÒÐËì I/O bottlenecks ºÑ¿à¿ÍÃì¢Í§ I/O latch Ãͪ¹Ô´áÅÐ writelog waits ÁÕµÑǺ觪Õé·Õè¤ÕÂì¡ÒõÃǨÊͺ»ÃÐÊÔ·¸ÔÀÒ¾¡Ò÷ӧҹàÊé¹·Ò§¢Í§ I/O

ÊÓËÃѺ¢éÍÁÙÅà¾ÔèÁàµÔÁà¡ÕèÂǡѺª¹Ô´¡ÒÃÃÍ ¤ÅÔ¡ËÁÒÂàÅ¢º·¤ÇÒÁµèÍ仹Õéà¾×èÍ´Ùº·¤ÇÒÁã¹°Ò¹¤ÇÒÁÃÙé¢Í§ Microsoft:

SQL Server µéͧãªéÃкºà¾×èÍʹѺʹع ‘ ÁÕ¡ÒèѴÊè§ä»ÂѧÊ×èÍ·ÕèàʶÕÂà ’ µÒÁ outlined ÀÒÂãµéâ»Ãá¡ÃÁµÃǨ·Ò¹ Microsoft SQL Server Always-On à¡çºâ«Å٪ѹá¡éä¢ FoFor more information about the input and output requirements for the SQL Server database engine, click the following article number to view the article in the Microsoft Knowledge Base: