SQL Server Performance Forum – Threads Archive
I/O, I/O its what I want to know 🙂hi, forum: I’m putting together a script that measures overall server I/O, and usually get a low percentage (which is good). 1)What is considered the threshold of when server perf. degrades and say should generate an alert? 2)Is there anyway I can force I/O usage to max out via script? (2000 and 2005)
Also, from the experts, what is the best way to monitor this? Thanks!
I/O basics chapters:http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx
http://www.microsoft.com/technet/prodtechnol/sql/2005/iobasics.mspx KBA on logging and data storeage algorithm:http://support.microsoft.com/default.aspx?scid=kb;en-us;230785 http://support.microsoft.com/kb/110352/ Optimizing performance
Look at Perfmon counter for page life expectancy as this can indicate an increase in the physical I/O requirements for a user database. The rate decrease could likely indicate that the memory taken away from the buffer pool is forcing database pages to exit the buffer pool prematurely. Combine with the other indicators and test to fully understand the parameter boundaries. http://www.simple-talk.com/sql/performance/sql-server-wait-events-taking-the-guesswork-out-of-performance-profiling/ – on wait events and profiling. SQL Server uses Windows I/O calls to perform disk reads and writes. SQL Server manages when and how disk I/O is performed, but the Windows operating system performs the underlying I/O operations. It also depends upon the different disk controllers and drivers use different amounts of CPU time to perform disk I/O. So overall hardware is key factor of the performance on I/o. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
Here are the two I/O related counters that can help you understand if there is any I/O bottleneck PhysicalDisk(_Total)Avg. Disk Queue Length
– if this counter is more than Avg. 2 per disk (or spindle if using SAN) then there is an issue PhysicalDisk(_Total)Avg. Disk sec/Transfer
– if this is counter value is more than 15 milli seconds this also indicates I/O issues. Girish Patil