Your queries won’t tune themselves. My 2027 Live Class Season Pass is $995 (normally $4,395), with live Fundamentals and Mastering classes plus recordings. Register by October 31 to join Fundamentals Week, November 2–6. See the pass.
Somehow I missed this a few years ago, and I bet a lot of y'all did too. Note the new "Recommended" column in the memory settings documentation:
Your queries won’t tune themselves. My 2027 Live Class Season Pass is $995 (normally $4,395), with live Fundamentals and Mastering classes plus recordings. Register by October 31 to join Fundamentals Week, November 2–6. See the pass.
If you right-click on a database in SQL Server Management Studio, you get an option to set Compatibility Level at the database level:
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 Task Manager alongside setup so you can see what I mean:
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 empty, you'd say to yourself, "I think I'll come back later."
When you're building a new SQL Server, you're going to see a few intriguing checkboxes during setup.
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 like you have to put more coins in the front of the server. So what's the drawback? Why not collect 'em all? What are the pros and cons?
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 fun time once when I implemented an indexed view, and they called me back the next day screaming because their app was down. My bad. (sigh)
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 query buck. If a query ran for 5 seconds, meaning it cost 5 query bucks, the optimizer would start examining parallel plans.
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, scroll down into the Parallelism section, and set MAXDOP to 1. Click OK. (Be aware that this blows your plan cache instantly.)
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 all around stability and happiness.
