Configuration Settings

Three Reasons to (Temporarily) Change Max Worker Threads

January 8, 2020

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?

February 14, 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

March 31, 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

March 14, 2017

Erik Darling

Configuration Settings

dba, development, performance tuning, sql server

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

December 21, 2016

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

September 4, 2013

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…