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:

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.

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT Microsoft SQL Server Performance Monitoring and Tuning Performance implications of transaction log autogrowth in SQL Server The short course on how SQL Server really works Determining the source of full transaction logs in SQL Server Improving SQL Server full-text search performance New GROUP BY option provides better data control in SQL Server 2008 Microsoft SQL Server 2008 Resource Governor primer Examining data files when SQL Server tempdb is full Testing transaction log autogrowth behavior in SQL Server Meeting business needs with SQL Server full-text search Using dynamic management views to improve SQL Server index effectiveness SQL Server High Availability, Scalability and Reliability Top load balancing methods for SQL Server Maintaining high availability of SQL Server virtual machines Creating fault-tolerant SQL Server installations Scaling up vs. scaling out with SQL Server 2008 How to configure storage in SQL Server database with more writes than reads SQL Server database replication tutorial Licensing a standby server for SQL Server replication Get SQL Server log shipping functionality without Enterprise Edition Monitor database mirroring and replication after a SQL Server upgrade Upgrade live applications to SQL Server 2005 for high availability SQL Server High Availability, Scalability and Reliability Research Microsoft SQL Server Database Development Change tempdb from 'C' drive Tempdb changes in SQL Server 2005 Sub-queries supported in Analysis Services MDX MDX functions: SCOPE, THIS and FREEZE More powerful Analysis Services MDX in SQL Server 2005 CASE statements now supported by MDX script Top 5 SQL Server query tips Update table rows in SQL Server 2000 Delete .bak files automatically with CLR Conversion error in SQL Server business objects Microsoft SQL Server Database Development Research

RELATED GLOSSARY TERMS Terms from Whatis.com − the technology online dictionary contiguity  (SearchSQLServer.com) contiguous  (SearchSQLServer.com) drilldown  (SearchSQLServer.com) hashing  (SearchSQLServer.com) hybrid online analytical processing  (SearchSQLServer.com)

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

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:

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.

[TABLE]

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.

[TABLE]

Rate this Tip To rate tips, you must be a member of SearchSQLServer.com. Register now to start rating these tips. Log in if you are already a member. DISCLAIMER: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.