Problem solve Get help with specific problems with your technologies, process and projects.

Troubleshoot SQL Server queries and improve I/O

SQL Server performance success often relies on I/O activity. Edgewood Solutions' Jeremy Kadlec first takes you through the steps applying Performance Monitor to troubleshoot I/O problems.

Jeremy Kadlec
By
Published: 16 Jan 2007

When SQL Server performance is critical, inevitably you find a problem related to a CPU, I/O or memory bottleneck. The culprit is typically related to the architecture (hardware or software) or the code being executed. With such a broad scope to determine the root cause of the performance issue, you must first conduct some discovery. Once the problem is pinpointed through that process, the next step is to implement corrective actions for a final resolution, especially with I/O related issues.

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.

ID I/O Related Issue Corrective Actions
1 Based on the information from Performance Monitor, the I/O sub system is flooded with disk queuing
  • Determine which function (program files, databases, transaction logs, tempdb, batch processes, backups, etc.) of SQL Server is residing on the disk drives experiencing the queuing. Then begin to troubleshoot those specific portions of SQL Server.
  • The disk drive may be supporting databases that are flooding the I/O subsystem, or you might find out that backups, online databases and batch processes are commingled and causing issues. The disk subsystem may not be able to support the volume of I/O with the current configuration, so splitting the I/O across multiple disks may do the trick in the short or mid term.
  • Knowing which function of SQL Server is experiencing the issue and where it resides on the disk drives, gives you a reasonable path to follow in order to narrow down the scope of the issue.
2 Queries are table scanning
  • Review query plans to build indexes to prevent the table
  • scanning.
  • In a development environment, experiment with a variety of indexes with a single column or multiple columns depending on the query.
  • Fine tune the indexes to determine the correct ordering (ASC or DESC), fill factor and file group.
  • As you fine tune the indexes for the query, validate the performance by reviewing the query plan.
3 Queries are getting a book mark lookup in the query plan
  • Review query plans to build a covering index to prevent the book mark lookup.
  • In a development environment, determine the correct column ordering and research other queries that access the same table to see if they benefit from the covering index.
4 Determine the missing indexes
  • According to the sys.dm_db_missing_index_details dynamic management view (DMV), this will outline the indexes that are missing.
  • For additional information, also reference the supporting DMV's: o
    • sys.dm_db_missing_index_columns o
    • sys.dm_db_missing_index_group_stats o
    • sys.dm_db_missing_index_groups
5 Review SQL Server's I/O metrics
  • Review the sys.dm_io_pending_io_requests DMV to determine the pending I/O requests in SQL Server that relate to a database file.
  • Review the sys.dm_io_virtual_file_stats DMV to determine the overall I/O activity for a database file and stalled metrics.
6 Query syntax options
  • Review the syntax versus the business logic to determine other options that will meet the business logic, but perform better. You will be surprised to see the query differences based on the database design, data volume, etc. from simple syntax changes such as EXIST, NOT EXISTS, LEFT OUTER JOIN, LIKE, =, <>, etc.
7 Excessive result set
  • SELECT and return only the data that is needed. In one example I can remember working with, there was an application returning megabytes of data to populate a single screen where a fraction of the data is reviewed by the users.

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.

Top 5 SQL Server query tips

Home: Introduction

Tip 1: SQL Server query design: 10 mistakes to avoid

Tip 2: Troubleshoot SQL Server queries and improve I/O

Tip 3: Tracking query execution with SQL Server 2005 Profiler

Tip 4: Find and fix resource-intensive SQL Server queries

Tip 5: Running analytical queries with Analysis Services

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.

Dig Deeper on Microsoft SQL Server Performance Monitoring and Tuning

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close