SQL Server Performance Tuning 2022: What Still Applies and What You Should Avoid

SQL Server tuning is rarely about applying fixed values to every environment. Recommendations that were useful in 2022 may still be valid today, but only when workload, hardware, SQL Server version, and operational requirements are considered. In this article, I revisit several common configuration recommendations and explain which principles still hold up, which settings need more context, and why universal values often create new problems.

The SQL Server landscape has changed significantly. Hardware has become more powerful, virtualized infrastructures are now standard, new features have been introduced, and expectations around stability and performance have increased.

One well-known set of tuning recommendations comes from a Brent Ozar blog post published in 2011. It focuses on three important areas of SQL Server configuration:

• Lock Pages in Memory
• Max Degree of Parallelism, or MAXDOP
• Cost Threshold for Parallelism

I reviewed these recommendations with SQL Server 2022 in mind, while also considering the direction of SQL Server 2025. This article summarizes which recommendations still make sense and which ones should be handled more carefully.

Lock Pages in Memory: Useful or Outdated?

The Lock Pages in Memory privilege, commonly referred to as LPIM, was often recommended as a general solution for preventing Windows from paging SQL Server memory to disk under memory pressure.

The idea is understandable, but in practice it is not always beneficial.

In dedicated SQL Server environments where memory is properly limited, LPIM can provide useful protection. In shared or heavily virtualized environments, however, it may create additional problems rather than solve them.

My conclusion is simple: LPIM is not a general performance booster. It is a targeted protection mechanism and should only be enabled when the surrounding environment supports it.

It should not be treated as a default setting.

Lock Pages in Memory should not be treated as an isolated performance switch. It affects how SQL Server memory interacts with Windows and requires the correct user right assignment. Microsoft’s guide to Lock Pages in Memory describes the required configuration and operational considerations.

MAXDOP: Regaining Control

Effective SQL Server tuning starts with understanding the workload rather than copying configuration values from another environment. MAXDOP is a good example: the appropriate value depends on processor topology, NUMA layout, workload characteristics, and the SQL Server version in use.

Many SQL Server instances still run with the default setting:

MAXDOP = 0

This means that SQL Server may use all available CPU cores when executing a parallel query.

At first glance, this may sound efficient. In real-world environments, however, it can easily become a performance problem, particularly for OLTP workloads.

Modern SQL Server environments usually benefit from limiting parallelism based on the server architecture and the actual workload.

For many OLTP environments, values between 4 and 8 can provide a reasonable initial range, but the final setting should be based on processor topology, NUMA boundaries, and measured workload behavior.

MAXDOP is one of the settings I rarely leave at its default value. A properly chosen configuration often improves not only individual large queries, but also the overall behavior and stability of the workload.

Microsoft’s MAXDOP configuration guidance provides additional recommendations based on processor topology, NUMA configuration, and workload type.

Cost Threshold for Parallelism: Moving Beyond the 1990s

The Cost Threshold for Parallelism determines the estimated query cost at which SQL Server considers using a parallel execution plan.

The default value is still 5.

This is a historical value that no longer reflects modern hardware or current workloads.

A low threshold may cause relatively small queries to run in parallel, creating unnecessary overhead for thread coordination and CPU scheduling.

Possible consequences include:

• increased CPU usage
• THREADPOOL waits
• unstable execution behavior
• unnecessary parallelism for small queries

A higher value, such as 25 to 50, often provides a more stable starting point.

The correct value always depends on the workload, but one conclusion is clear: 5 is rarely a sensible starting point for a modern SQL Server environment.

The Microsoft documentation for cost threshold for parallelism explains how the setting influences whether SQL Server considers a parallel execution plan. The value should therefore be reviewed together with MAXDOP, workload costs, and the execution plans actually generated by the system.

Instance-level settings are only one part of the picture. When performance changes affect individual queries, the cause may instead be blocking, parameter sniffing, outdated statistics, execution-plan changes, or resource pressure. I discuss these causes in more detail in Why Is My Query Suddenly Slow?.

Other Important SQL Server Tuning Settings

Beyond these three settings, effective SQL Server tuning also requires several other configuration areas to be reviewed when deploying or assessing an instance.

These include:

• SQL Server memory limits
• TempDB configuration
• backup strategies and compression
• database file initialization
• emergency administration and diagnostic options
• statistics updates
• handling of ad hoc workloads

The appropriate configuration depends heavily on the environment.

This is where individual performance tuning begins. There is no universal configuration that works for every SQL Server system.

There are, however, principles that apply to every engagement:

• understand the environment
• evaluate the workload
• optimize based on evidence
• avoid changing settings without understanding their impact

Configuration values should never replace diagnosis. Monitoring can show that CPU, waits, memory, or query duration have changed, but those signals do not explain the root cause by themselves. This distinction is explored further in Monitoring Is Not Diagnosis: Why SQL Server Alerts Are Only the Beginning.

Conclusion: SQL Server Tuning Is Not Copy and Paste

Good SQL Server tuning is not based on universal values, but on evidence, workload behavior, hardware topology, and a clear understanding of the underlying bottleneck. Recommendations can provide a useful starting point, but they should always be validated against the actual environment.

The core ideas from Brent Ozar’s 2011 article are still relevant, but modern SQL Server environments require more precise decisions.

Many default values no longer reflect current hardware or workload characteristics. At the same time, the impact of individual configuration settings has become more complex.

My recommendation is straightforward: anyone responsible for SQL Server, whether as an administrator, developer, or technical decision-maker, should understand the basic principles of performance tuning.

Not every setting needs to be changed immediately.

But ignoring the configuration entirely can reduce stability, waste resources, and create avoidable performance problems.

When you are unsure whether your SQL Server environment is configured appropriately, the right next step is not to apply another generic checklist. It is to review the environment, understand the workload, and make informed decisions.