SQL Server: Effective Query Tuning

Focus on query tuning to truly optimize your SQL Server performance and efficiently manage workloads.

Excerpted from “SQL Server DMV Starter Pack,” published by Red Gate Books (2010).

Glenn Berry, Louis Davidson and Tim Ford

Query tuning is the heart and soul of optimizing SQL Server performance. If your typical workload consists of ill-designed or inefficient queries, you’ll experience performance and scalability issues. If your queries are longer, more numerous and more complex than necessary, they will consume more CPU resources during execution.

Consequently, they will also take longer to run. Ill-designed queries, along with a failure to make proper use of indexes, lead to SQL Server reading more data than is necessary. This causes an obvious lag in performance and execution time.

If SQL Server reads data from the buffer cache, it’s referred to as logical I/O. This can be an expensive operation from a performance standpoint. If the data is not in memory, and needs to be read from disk (or if data needs to be written), this is physical I/O and it’s even more expensive.

Size Matters

If you have numerous queries that return huge amounts of data, it could cause memory pressure on the buffer cache. This will result in SQL Server flushing data out of the cache, which will in turn affect the performance of other queries.

The “golden rule” of well-designed SQL queries is to return no more data than you really need. You’ll want SQL Server to pass through the data as few times as possible and use set-based logic to manipulate that data into the result set you need.

Parsing and optimizing SQL statements is not a “"high concurrency” operation. SQL Server stores plans for previously executed queries in a shared memory area called the plan cache. Whenever you submit a query for execution, SQL Server checks the plan cache to see if it can use an existing plan to execute the query. Every time it doesn’t find a match, it then parses, optimizes and generates a plan for the submitted query. This is a CPU-intensive process.

Furthermore, each time it does this, SQL Server acquires latches on the plan cache to protect the relevant area of memory from other updates. More ad hoc, non-parameterized SQL Server queries mean more single-use plans in the cache. This translates to greater consumption of CPU resources and acquiring latches during parsing. It can ultimately result in a non-scalable system. Well-designed SQL queries will promote plan reuse (“parse once, use many times”) to the greatest possible extent.

Design with Time in Mind

Ultimately, if your workload consists of poorly designed queries, it will cause unnecessary I/O operations. Your CPU and memory overhead will bog down, and execution times will be slow. The situation will get worse as the number of users grows. Their requests will be forced to wait for access to the shared resources that the improperly designed queries are monopolizing.

Conversely, if you can minimize the number of individual SQL statements you need to accomplish a particular job, then you can also minimize the work done by each of those individual SQL statements. You’re much more likely to have a fast, responsive SQL Server system, which will scale gracefully as the number of users and the overall workload grows.

An often-used approach to performance tuning is to retrieve a “Top 10” list of the slowest queries that constitute part of the normal, daily workload on your SQL Server instance and then tune them, one by one. Track down the sessions, requests and queries within your SQL Server infrastructure that are the most resource-intensive, and take the longest time to execute.

A slightly more scientific approach might start at the lower levels, looking for specific areas where SQL Server is experiencing resource pressure. Check to determine where processes are waiting unusually long times for some other action to complete before proceeding. This way, you can work out whether the major component of the slow execution time is CPU time (if the system is CPU-bound) or time spent waiting for I/O (if the system is I/O-bound) and so on.

You can then work back from there to the requests that are causing the resource contention. Having isolated the problem queries, you can then find a way to reduce the amount of work being performed. This usually involves tuning your SQL statements and queries or adding indexes. If all else fails, you can increase capacity by buying more memory/disk/CPU power.

Glenn Berry

Louis Davidson

Tim Ford

Glenn Berry works as a database architect at NewsGator Technologies in Denver, Colo. He’s a SQL Server MVP, and has a whole collection of Microsoft certifications, including MCITP, MCDBA, MCSE, MCSD, MCAD and MCTS, which proves he likes to take tests.

Louis Davidson has been in the IT industry for 16 years as a corporate database developer and architect. He’s been a SQL Server MVP for six years and has written four books on database design. Currently, he’s the data architect and sometimes DBA for the Christian Broadcasting Network, supporting offices in Virginia Beach, Va., and Nashville, Tenn.

Timothy Ford is a SQL Server MVP and has been working with SQL Server for more than 10 years. He’s the primary DBA and subject-matter expert for the SQL Server platform for Spectrum Health. He’s been writing about technology since 2007 for a variety of Web sites and maintains his own blog at thesqlagentman.com, covering SQL as well as telecommuting and professional development topics.

Learn more about “SQL Server DMV Starter Pack” at red-gate.com/our-company/about/book-store.