Article ID: 309392
Article Last Modified on 10/28/2003
APPLIES TO
- Microsoft SQL Server 2000 Standard Edition
- Microsoft SQL Server 7.0 Standard Edition
This article was previously published under Q309392
SYMPTOMS
Under some stress conditions, when there is a significant bottleneck in the disk subsystem of SQL Server, the following error message may be logged in the SQL Server error log:
CAUSE
This behavior occurs because a bottleneck in SQL Server's disk subsystem (particularly the I/O subsystem for the drive on which Tempdb is stored) has caused a SORT operation in the execution plan of a query to wait for a free data buffer. SQL Server raises the warning message when this SORT operation has not received a buffer after a sixty-second wait.
NOTE: No cancellation occurs for this I/O request; it remains outstanding until it has actually completed.
RESOLUTION
One or more of the following suggestions or references (or both) may help you to resolve this problem:
- Examine the disk subsystem on SQL Server for any evidence of an I/O bottleneck. This may involve examining the following logical and physical disk counters in Windows NT Performance Monitor:
- Disk Reads/sec
- Disk Writes/sec
- Avg. Disk Queue Length
- Avg. Disk Sec/Read
- Avg. Disk Sec/Write
- For additional information about troubleshooting system bottlenecks and disk activity, please see the following SQL Server Books Online topics (which are both available at http://msdn.microsoft.com):
- "Monitoring Disk Activity"
- "Identifying Bottlenecks"
- Review the following Knowledge Base articles for guidance in monitoring and resolving potential performance problems for Microsoft Windows and SQL Server:
146005 Optimizing Windows NT for Performance
224587 INF: Troubleshooting Application Performance with SQL Server
- Verify that the disk hardware that is being used for the computer that is running SQL Server is functioning properly. Work with your hardware manufacturer and run any hardware diagnostics that they provide.
- Set the AUTOSHRINK option to OFF for the database in question to reduce the load that is placed on the database's disk (or disks).
- Verify that the AUTOGROW interval and size options for the database are set such that database file growth occurs infrequently. Create a SQL Server scheduled job to grow the database size during off-peak hours.
Keywords: kbprb kbpending KB309392
