SQL Server installation wizard

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

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…
Read More

Checking for Strange Client Settings with sys.dm_exec_sessions

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…
Read More

Ten Ways to Set MAXDOP

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,…
Read More