Taming resource-intensive SQL Server queries is no small task. Finding them can be a challenge and fixing them is typically unique to the query. In this tip I will outline the means to identify resource draining queries in SQL Server 2000, as well as five common resource-intensive queries with possible resolutions.
How to find resource-intensive queries
Identifying resource-intensive queries is simple when your application experiences performance issues and users communicate when and where the issues occur. If the overall application is perceived as slow, the root cause and resolution can be much more complex. The following resources will help you address common problems:
- To identify resource-intensive queries, leverage SQL Server 2000 Profiler.
- To determine how the optimizer processes the code internally, review individual query plans in a graphical format using Query Analyzer.
- To access query plans, use the T-SQL command SET SHOWPLAN_ALL or SET SHOWPLAN_TEXT for a textual view of the output from the SQL Server optimizer.
Calculation queries
Users in management and executive management positions issue calculation queries throughout the day. They calculate figures over a long period of time with a primarily static data set (i.e., calculating year-to-date sales or monthly inventory figures). Depending on your applications, the calculations may be different though the premise remains the same. While these figures are needed by the business, they may have a significant resource drain.
To balance the need to run these resource-intensive queries and retrieve timely data for users, change the process to execute a stored procedure on a predefined basis, which populates a table that stores the aggregate results. Then have users access the aggregated data instead of issuing the resource-intensive query.
Table scanning
Table scanning is probably the single biggest offender of draining SQL Server resources. Good news is the problem is usually easy to fix. The best way to diagnose this resource drainer is to review the query plan. The SQL Server optimizer will indicate which portion of the query is scanning tables by table and column name. With this information, you can create the necessary index to support the query and avoid costly table scanning.
Large result sets
Querying for hundreds or thousands of rows while only displaying 10 to 50 rows in the application is certainly a drain on SQL Server, especially when the query is frequently issued by the same user. Since the data isn't going to change, the application's throughput would benefit from caching that data on the Web server using ADO.NET. Another option would be to cache the IDs or the unique identifier for the result set and query for the detailed data as the data is browsed.
A final option I have seen work well is to issue the query with a COUNT clause and let users know how much data will be returned. If it is a significant amount of data, fine-tune the query parameters to reduce the result set; not many users will be able to review a large result set, which just becomes overwhelming.
Cursors
Cursors are notorious for quickly turning a high-end server into a single-user machine. Cursors typically build a large data set and process data one row at a time, which often serializes the processing. Originally developed for ISAM and VSAM databases, Microsoft included support for this processing from the earliest versions of SQL Server. Although they are a viable way to perform data processing, they are not efficient -- and your goal should be to migrate away from cursors and use set-based logic.
Single queries that run repeatedly
One of the most deviant sets of queries are single queries that execute one or more times per second using few resources -- but the aggregate resources are staggering. They don't only eat up SQL Server resources, but also an excessive amount of network round trips. You can expect to see this happen in Web-based applications. If you store data in a session variable or cookie, the problem is resolved.
Conclusion
Take a step back and think about how your applications interact with SQL Server from a functional perspective. Think about complaints users have had historically about the application as well as long-running processes. Observe how users work with the applications and make performance improvements in the code based on how users have evolved with the application. Good luck!
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. Kadlec is also the SearchSQLServer.com Performance Tuning expert. Visit Ask the Expert to pose a question to him.
