Troubleshoot SQL Server queries and improve I/O

Discovery

During the discovery phase of the process, the traditional question is, "How can I determine where I am having I/O performance related problems?" If your users have already found the problem, review the code for the screen, Web page or report to decide on the appropriate corrective action. If you are in fact left to find the source of the performance issue, then it is necessary to have an open mind during the discovery period. The problem could be the sum of a variety of issues that need to be determined, and then prioritized to improve the overall SQL Server performance. Typically a performance issue is not related to a single piece of code, but interwoven among a number of issues.

For the sake of this tip, since it is I/O related, if you are facing performance problems and you suspect the issue is related to an I/O bottleneck, then leveraging Performance Monitor should give you a general feel for the I/O related issues. A good place to start with Performance Monitor is to review the following counters for the Physical Disk object:

• Percent Disk Read Time
• Percent Disk Write Time
• Avg. Disk Queue Length
• Avg. Disk Read Queue Length
• Avg. Disk Write Queue Length
• Current Disk Queue Length
• Split IO/Sec

This information from Performance Monitor should give you a general sense of the severity of the I/O performance related issues, or at least a validation that I/O issues are occurring. From this point forward, addressing the issue could be taken in a variety of directions. The direction you choose will be based on the initial set of data collected and the local knowledge of the hardware, application, database design, code, etc. These next paths could include the following:

• How are the hardware, and more specifically, the disk drives configured?
• What are the current SQL Server configurations?
• What are the current database configurations?
• How do the databases map to the disk drives?
• Which portions of the database design are having issues?
• When was the last time database maintenance was performed?
• What indexes are needed to support the queries?
• What T-SQL code is experiencing high I/O (read or write) issues?

Query identification

To address issues from a query perspective, it is necessary to determine which queries are having I/O related issues. One simple way to do so is to use SQL Server Profiler to capture the queries that are being issued during a finite period of time. Another approach is to work directly with a user to capture the queries they issue to fine tune the data collection. In either situation, the Profiler data can be stored in a table or a file for further analysis. Once this information is captured, then it is necessary to dig into the detailed queries to determine the root cause.

Corrective actions

When it comes to correcting query performance -- although there are some guiding principals -- when it gets down to the nitty-gritty do not be afraid to test a variety of options to determine the best resolution. Try laying out as many options as possible and test each option in a systematic manner. Let the numbers speak for themselves to prove the best resolution for the situation. Remember, in the next situation the resolution can and probably will be different.

Validation

To be sure the I/O problems are corrected, it is imperative to build test cases in a development or test environment. Once these test cases are built, begin to validate the original issue is corrected and validate that the remainder of the application is not adversely affected. Be aware that a performance gain from a design change can benefit one portion of the application and adversely affect another. Be less concerned about a query change, but validate that the performance gain is calculated. As the code change is promoted to the production environment, monitor the overall SQL Server environment and the promoted code. Touching base with users is a good practice to ensure the results are as expected and do not have adverse affects on other portions of the application.

For more help, check out our FAQ: SQL Server query errors and explanations.

About the author:
Jeremy Kadlec is the Principal Database Engineer at Edgewood Solutions, a technology services company delivering professional services and product solutions for Microsoft SQL Server. He has authored numerous articles and delivers frequent presentations at regional SQL Server Users Groups and nationally at SQL PASS. He is the author of the Rational Guide to IT Project Management. Jeremy is also the SearchSQLServer.com Performance Tuning expert. Ask him a question here.