Configuration Settings

What Does Setting the SQL Server Compatibility Level Do?

Last Updated April 19, 2023

If you right-click on a database in SQL Server Management Studio, you get an option to set Compatibility Level at the database level: When you upgrade SQL Server or you want to improve performance, which option should you choose? Should you always go with the newest compatibility level? If you have an old application, can…

Is There a Bug in SQL Server’s MAXDOP Calculation? (Update: Yes!)

Last Updated November 3, 2022

Brent Ozar

Configuration Settings

I think I’ve found a bug in SQL Server setup’s MAXDOP calculation, and I need you to take a second look. Setup is recommending MAXDOP 8: Which is odd, because this is running on an AWS i3.16xlarge with 2 sockets, 2 NUMA nodes, 32 logical processors per node, 64 logical processors altogether. In this screenshot, I’ve laid…

Three Reasons to (Temporarily) Change Max Worker Threads

Last Updated January 7, 2020

Brent Ozar

Configuration Settings

When you go to the deli, there’s a “take a number” dispenser where you tear off a number and wait for your number to be called. If you walked into a deli and you saw 50 people lined up all waiting their turn, and you went to take a number and the dispenser was completely…

Should you run SSAS/SSIS/SSRS on the SQL Server?

Last Updated February 12, 2019

Brent Ozar

Architecture, Configuration Settings

When you’re building a new SQL Server, you’re going to see a few intriguing checkboxes during setup. It’s all free, right? Check everything! The services are all free, right? You can just check the boxes for Machine Learning Services, R, Python, Data Quality Services, PolyBase, Integration Services, Analysis Services, and Reporting Services – it’s not…

Checking for Strange Client Settings with sys.dm_exec_sessions

Last Updated April 5, 2017

Brent Ozar

Configuration Settings, Indexing

In my performance training class, people get really excited when I cover filtered indexes, computed columns, and indexed views. Then they get a really strange expression on their face when I talk about one of the biggest drawbacks: if your connection options aren’t set correctly, your delete/update/insert (DUI) operations will fail. That gave me a really…

Why Cost Threshold For Parallelism Shouldn’t Be Set To 5

Last Updated March 3, 2017

Erik Darling

Configuration Settings

CURRENT_YEAR() Here it is, 2017. A full 20 years since SQL Server 7. Back then, there was a developer named Nick. Nick was on the development team, and apparently the query-costing-magic development was done on his machine. All of it. The magical metric was that for every second a query ran, it would cost another…

Ten Ways to Set MAXDOP

Last Updated February 14, 2017

Brent Ozar

Configuration Settings

Whenever I work with SQL Server, I’m amazed at how many ways there are to influence its behavior. For example, take the maximum degree of parallelism for a query. Just offhand, I thought of ten different ways you can tweak it: 1. At the server level with SSMS. In SSMS, right-click on the server, click Properties, Advanced,…

Five SQL Server Settings to Change

Last Updated July 24, 2019

Jeremiah Peschka

Configuration Settings

129

SQL Server ships with sane defaults, right? Well, sort of. SQL Server ships with pretty sane defaults for a single CPU socket server or for a development work station. Honestly, the defaults are good enough for many things, but there are a few gotchas. Changing these settings is quick, relatively painless, and can result in…