AN EXPERT GUIDE

TO SQL SERVER
PERFORMANCE TUNING
Top database performance
tuning tips and tricks
from industry experts
Brent Ozar, Pinal Dave
and Janis Griffin
The Trials and
Tribulations of SQL Server
Performance Tuning
Database professionals agree – SQL Server performance tuning is
hard. And on top of that, it never stops because complex database
environments are always changing with upgrades, application updates
and queries. It often feels like as soon as you get one query optimized,
there’s another one right behind it that’s eating CPU time or clogging
memory or otherwise slowing down the entire database. Then, add to
that, the instances when the latest SQL Server version itself has made
performance worse instead of making it better as promised.

As with many daunting tasks, often the toughest part of performance

tuning in SQL Server is knowing where to start. So, we asked several
SQL Server experts to share their advice on what to look for when the
database is performing slowly. During Quest’s Database Training Days
webcast series, we invited Pinal Dave, SQL Server Performance Tuning
Expert, Janis Griffin, Oracle ACE Director and Brent Ozar, Microsoft
Certified Master to host hands-on training sessions where they identified
quick wins, common performance inhibitors and the best places to begin
and focus your efforts when tuning SQL Server. We’ve compiled their
top tips to help guide you in your performance tuning efforts.

2
 Server uses to execute queries and support available features. After
an upgrade, you may be running the latest version of SQL Server but

Pinal Dave’s Kick the compatibility level instructs it to behave and perform like it’s still an

Start Advice
earlier version.

Figure 1 is a screenshot of SQL Server 2019 showing the different

compatibility levels available in the SQL Server Database Properties:

“99% of the time, when you change

compatibility level, you get better performance.”

Pinal Dave has tuned many SQL Server databases, and one of the most
common complaints he hears is that SQL Server is actually running more
slowly after an upgrade than it did before. This is particularly frustrating
since a software upgrade may also include an investment in hardware
that should be yielding positive performance results. So, what does Pinal
suggest you do?

CHECK THE COMPATIBILITY LEVEL

In the case of a SQL Server upgrade that doesn’t behave as expected,
the first thing to check is the system’s compatibility level. Compatibility
level is a database configuration that determines which algorithm SQL Figure 1: Database Properties dialog box in SQL Server 2019

3
Why else would you want to change the compatibility level? In addition PAY ATTENTION TO ROW GOALS
to updating the underlying logic by which SQL Server operates, the In SQL Server, when the Query Optimizer estimates the cost of a query
latest compatibility level also allows SQL Server to use new features and execution plan, it assumes that all qualifying rows from all sources must be
built-in optimizations. As an example, in 2017, SQL Server introduced processed and the entire results set returned at once. However, certain
adaptive join, where it dynamically decides which type of join will work query keywords (such as TOP, FAST, IN, etc.) instruct the Query Optimizer
most efficiently for a query. An earlier compatibility level would prevent to build an execution plan that returns a smaller number of rows faster –
the system from using that feature. these are row goals that regulate how the results set is returned.
UNDERSTAND THE CARDINALITY ESTIMATION
When determining the best query execution plan, the Query Optimizer
According to Microsoft, “If the row goal plan is
relies heavily on cardinality estimation – or the number of rows that will
applied, the estimated number of rows in the query
be processed at each level of the plan. The cardinality estimation is a
execution plan is reduced. This is because the plan
significant contributor to the overall estimation of the plan cost –
assumes that a smaller number of rows will have
so improved cardinality leads to better estimated costs and faster
to be processed in order to reach the row goal.”
plan execution.

The cardinality estimator has become more accurate in later versions of Most of the time, a row goal strategy works as expected. Other times,
SQL Server, and most queries benefit from running the latest version. it can go very wrong because many more rows need to be processed
But there are times when queries won’t perform as well with the latest than the Optimizer estimated. When searching for the root cause of the
cardinality estimator. You can test this by running the query using the resulting poor performance, you want to know whether the divergence
earlier and later versions of the cardinality estimator, and clearing your you see in estimated versus actual rows is caused by row goal logic.
query store in between. The on/off toggle for Legacy Cardinality Estimation
is found in the Database Properties area, and is visible in Figure 1. Prior to SQL Server 2019, there was no way to see row goals in execu-
tion plans, making them hard to detect when tuning queries. But 2019
Again, the goal is to have the best cardinality estimation for your introduced a visible EstimateRowsWithoutRowGoal attribute to each plan
compatibility level, so your queries execute at a lower cost. operator affected by a row goal. With this information, you can determine
whether the row goals logic is the cause of the performance problem.

4
 Wait time analysis must be done by viewing the wait types over time,

Janis Griffin’s
otherwise, you’re just seeing a snapshot of what’s happening. There are
benefits to capturing wait time information over a longer period. First,

Path to
you’re collecting valuable baseline metrics that enable you to set an
acceptable performance improvement goal and stop when you reach it.

Optimization Also, when you’re armed with wait time information, you can identify the
biggest contributor to slow performance and focus on fixing it.

REVIEW THE EXECUTION PLAN

Possibly the most important tool in your performance tuning arsenal is
the execution plan, so it is vital that you know how to read and interpret
what you see in the plan. The execution plan gives you a view into
what’s being done (filtering, sorting, joining) to the objects (tables and
“Have you ever found yourself tuning indexes) being used by the query.
something and no one notices?”
An estimated execution plan is SQL Server’s best guess at how the
query will perform when running and shows the anticipated cost (in CPU
MONITOR WAIT TIME time and I/O) of executing the query. An execution plan is read from right

Many database professionals undertaking SQL Server performance to left and bottom to top, with the thickness of the connecting arrows

tuning focus on resource utilization metrics, such as response time. This indicating the number of rows being passed in the step.

is an important part of the equation that tells you whether your hardware When you examine the estimated or actual execution plan, you’ll want
is up to the task of processing the query, but it’s also critical to know to look at CPU/IO costs and row counts, as well as review the predicate
what’s happening when the query isn’t being processed. What is it information to see how parameters are being interpreted. You’ll also
waiting for? That’s the concept behind wait time analysis. want to review join methods as you look for the most expensive steps in

SQL Server allows you to monitor both the total wait time, as well as the plan. SQL Server provides a wealth of information in execution plan

elapsed time for each step of the query. Wait types offer invaluable icon Tool Tips and the drill downs into the operator properties.

clues about the amount of time a query is taking and the resources it’s
consuming as it runs. Therefore, it’s useful to know how to interpret the
different wait types at work in your database, including locking/blocking,
I/O problems, latch contention and network slowdown.

5
 Figure 2: Read an execution plan from right to left and from bottom to top

GATHER OBJECT INFORMATION

AND FIND THE DRIVING TABLE
Once you’ve identified those expensive steps, you’ll want to dig deeper
into the objects underlying them. Get the table definition, review the
column information and make note of the row counts. You’re looking for
performance degraders that eat up memory. Be sure to examine the
WHERE and JOIN clauses, review indexes and index definitions, existing
keys and constraints, and look for implicit conversions.

Table row counts are important as you find the driving table – this is the
table that will return the least amount of data and minimize the number of
logical reads. Proper filtering done early on this table will further reduce
the amount of data that needs to be read in later steps. The table with the
most selective filter is going to be your best choice for the driving table.
A technique called SQL diagramming can be used to map this out.

6
 With the right hardware and software in place, you’ll want to understand

Brent Ozar how busy the server is by examining the data point batch requests
per second. It’s essentially a performance counter – how many T-SQL

Breaks it All command batches are received by the server each second. It will vary over
time, perhaps seasonally or cyclically, depending on what’s happening on

Down the server. It’s a first place to look when you hear that queries are running
slowly – it’s possible they’re running slowly because a lot of them are
occurring at the same time. A dramatic spike in batch requests/second will
be telling, but you have to know your system’s baseline for comparison.

The next important metric to keep as a baseline is how hard the server is
working to deliver query results. This is encapsulated by a wait time ratio,
which is the relationship between the CPU time and total database time
“Success means a performance needed to complete a process. It’s an effective indicator of how long tasks
boost that end users notice.” are waiting in the queue for available resources before being processed.
Ideally, the wait time ratio will be 100%, but 90% efficiency is good. If your
wait time ratio drops below what’s generally normal for your system, begin
Brent Ozar is one of the determined few who attained the status of
your investigation by analyzing the queries that ran during that time period.
Microsoft Certified Master in 2011 and is highly regarded as a SQL
Server expert, trainer and consultant. Brent helps clients understand UNDERSTAND INDEX FRAGMENTATION
AND FILL FACTOR IMPACTS
how to measure their SQL Server performance, since tuning makes
no sense unless you know whether your performance metrics are Many database professionals look at index fragmentation statistics as a
actually changing. performance measure, when in fact, they’re not useful. In a new or rebuilt
index, the data “pages” are all full and stored in order. But, as more data
SELECT AND MEASURE THE RIGHT METRICS
gets added, pages become split: not all pages are full and they occur
We touched on baseline metrics earlier, and for SQL Server performance out of order. This is the vital difference between external and internal
tuning you should know generally-accepted rules of thumb as well as fragmentation:
what’s normal for your particular system environment. The amount of data
you have will guide your resource procurement decisions, along with • External fragmentation – refers to pages being out of order
which version of SQL Server can handle your active data. So, first • Internal fragmentation – refers to the empty space on a page
understand how much data you’re dealing with.

7
 External fragmentation has little impact on the speed of maintenance Common wait types include:
tasks, running queries in RAM or reading data from disk. But trying to Wait Type Explanation What to Do
fix external fragmentation by reorganizing and rebuilding indexes can CXPACKET Indicates there are parallel Investigate where they are
actually make performance worse by causing internal fragmentation. query plans running occurring and determine
whether the parallelism is
Internal fragmentation is controlled by the fill factor, and any setting valid. If valid, is it working
normally or is skewed
under 100 tells SQL Server to leave free space on index pages for new
parallelism happening?
records to be added. PAGEIOLATCH Query is waiting to read Long waits may indicate
pages from disk problems with the disk
If an index is rebuilt with a reduced fill factor, page count gets higher. subsystem, but also think
about why SQL Server is
With more pages in the index, more reads are needed from disk into doing so many reads. Possibly
memory and overall performance will slow down. tune the query’s indexes.
ASYNC_NETWORK_IO Occurs on network writes Not much can be done. Could
when the task is blocked be due to network latency or
behind the network shared VM.
“Whatever your top wait stat is, that’s where LCK* Occurs when a task is Many ways to fix this, but

you focus your performance tuning” waiting to acquire an Intent

Exclusive (IX) lock on a
ultimately you need to
identify the thread that’s
resource holding the lock and blocking
everything else.

DID WE MENTION WAIT TIME? WRITELOG Occurs while waiting for a Reduce how much
log flush to complete. transaction log is generated
Wait time is such an important element of performance tuning, yet SQL and how often log flushes
are occurring.
Server doesn’t offer an easy way to monitor it over time. You need to
RESOURCE_ Happens when a query is Identify and then tune
collect the results, then interpret them to determine where query tasks SEMAPHORE waiting for an execution queries that need incorrectly
memory grant so it can large memory grants, or
are spending their time or getting hung up. The area where a query is begin executing operations troubleshoot general memory
spending the most time should be the first target of your performance like sorts and hashes. shortages on the server.

tuning efforts.
Advanced performance monitoring tools allow you to track wait type
statistics over time. Increases in wait time may be due to higher batch
requests/second, poorly-tuned queries, slow storage or shared hardware.
Conversely, decreases in wait time could mean there are fewer batch
requests/second, well-tuned queries and indexes, and more available
memory.

8
Watch the entire Database Training Days series on-demand now.

Further Insights Await

Quest’s Database Training Days webcast series brought together industry experts who have made just about every SQL
Server performance tuning mistake possible, and they have been kind enough to share that hard-earned wisdom in each
of their interactive training sessions throughout the series. This e-book just scratches the surface on the tips and insights,
detailed case studies and various query demonstrations candidly displayed. If you’d like to explore these concepts further,
you can watch the entire Database Training Days series on-demand.

9
ABOUT QUEST © 2020 Quest Software Inc. ALL RIGHTS RESERVED.

Quest provides software solutions for the rapidly changing world of This guide contains proprietary information protected by copyright. The software
described in this guide is furnished under a software license or nondisclosure agreement.
enterprise IT. We help simplify the challenges caused by data explosion, This software may be used or copied only in accordance with the terms of the applicable
cloud expansion, hybrid data centers, security threats and regulatory agreement. No part of this guide may be reproduced or transmitted in any form or by
any means, electronic or mechanical, including photocopying and recording for any
requirements. We’re a global provider to 130,000 companies across 100 purpose other than the purchaser’s personal use without the written permission of
countries, including 95% of the Fortune 500 and 90% of the Global 1000. Quest Software Inc.

Since 1987, we’ve built a portfolio of solutions which now includes data- The information in this document is provided in connection with Quest Software products.
base management, data protection, identity and access management, No license, express or implied, by estoppel or otherwise, to any intellectual property
right is granted by this document or in connection with the sale of Quest Software
Microsoft platform management and unified endpoint management. With products. EXCEPT AS SET FORTH IN THE TERMS AND CONDITIONS AS SPECIFIED IN
Quest, organizations spend less time on IT administration and more time THE LICENSE AGREEMENT FOR THIS PRODUCT, QUEST SOFTWARE ASSUMES NO
LIABILITY WHATSOEVER AND DISCLAIMS ANY EXPRESS, IMPLIED OR STATUTORY
on business innovation. For more information, visit www.quest.com.
WARRANTY RELATING TO ITS PRODUCTS INCLUDING, BUT NOT LIMITED TO, THE
IMPLIED WARRANTY OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE,
OR NON-INFRINGEMENT. IN NO EVENT SHALL QUEST SOFTWARE BE LIABLE FOR ANY
If you have any questions regarding your potential use of this material, DIRECT, INDIRECT, CONSEQUENTIAL, PUNITIVE, SPECIAL OR INCIDENTAL DAMAGES
contact: (INCLUDING, WITHOUT LIMITATION, DAMAGES FOR LOSS OF PROFITS, BUSINESS
INTERRUPTION OR LOSS OF INFORMATION) ARISING OUT OF THE USE OR INABILITY
TO USE THIS DOCUMENT, EVEN IF QUEST SOFTWARE HAS BEEN ADVISED OF THE
Quest Software Inc. POSSIBILITY OF SUCH DAMAGES. Quest Software makes no representations or
Attn: LEGAL Dept warranties with respect to the accuracy or completeness of the contents of this document
and reserves the right to make changes to specifications and product descriptions at
4 Polaris Way
any time without notice. Quest Software does not make any commitment to update the
Aliso Viejo, CA 92656 information contained in this document.

Patents
Refer to our website (www.quest.com) for regional and international office
Quest Software is proud of our advanced technology. Patents and pending patents may
information.
apply to this product. For the most current information about applicable patents for this
product, please visit our website at www.quest.com/legal

Trademarks
Quest and the Quest logo are trademarks and registered trademarks of Quest Software
Inc. For a complete list of Quest marks, visit www.quest.com/legal/trademark-information.
aspx. All other trademarks are property of their respective owners.

EBook-DatabaseTrainingDays-US-KA-59463

10