A SQL DBA New Year’s Resolutions- Habits we won’t be bringing with us into 2023

The New Year is upon us, can you believe it? I love this time of year. Thinking about my goals, setting up my 2023 calendar, and of course setting my resolutions for this year. Good habits to develop like going to bed earlier, and eating more vegetables. And of course, resolutions for bad old habits to leave behind, like over-scheduling myself, or staying up way too late watching TV.

It got me thinking, what would be the best SQL DBA New Year’s Resolutions to bring into 2023? What habits should we leave behind?

So here are 3 of our top “What NOT to do’s” as a SQL DBA:

1. Do NOT leave Query Store turned off.
   • In previous versions of SQL Server (2016-2019) the default setting for Query Store is off, so you may not be in the habit of using it, but it is an incredibly helpful tool. It automatically captures a history of queries, plans, and stats. It’s a like flight data recorder, and will help simplify your performance troubleshooting saving you loads of time.
   • The Query Store feature is phenomenal, but many people don’t use it. There are two main reasons in our opinion why- 1 is because it was automatically shut off in the most recent versions of SQL Server, so users may not even know it exists! The 2nd reason is because they think it will slow down their system. But no worries! It only captures meta-data so the overhead is extremely low and won’t cause any lag or other issues.
2. Do NOT leave Accelerated Database Recovery (ADR) turned off.
   • If you’re using SQL Server 2019 and newer this is for you! It’s another feature that is off by default, but we cannot recommend enough turning it on. It’s such a worthwhile tool because it’s all about system recovery. If your power goes out or your system crashes, how do you recover all of those transactions? Turing back on the server after the crash and waiting for it to roll back the transactions (especially on a particularly large server) could take a very long time. 
   • ADR solves that problem. It improves database availability with fast and consistent database recovery, and you also get instantaneous transaction rollback, regardless of the size or quantity of transactions in the server. Turning this on, you’ll also get aggressive log truncation, even in the presence of active long-running transactions helping them not get out of control.
3. Do NOT leave memory options on default. Adjust it based on your workload.
   • Max Memory : Sets the upper bound for the memory used in the buffer pool. It won’t allow your server to take up too much of the memory and mess things up for the OS and other services. SQL Server is a memory hog and will take up as much memory as it can, so it’s very important to make sure the Max Memory setting is configured correctly.
   • Lock Pages in Memory: This is a Windows policy that determines which accounts can use a process to keep data in the physical memory instead of paging it to disk. So one of the most helpful and important habits to keep this year is always making sure to add the SQL Server Service account to the lock pages in memory Windows policy. That will tell Windows that this specific SQL Server account can keep data in the physical memory.
   • Optimize for Ad Hoc Workloads: This improves efficiency of the plan cash for ad hoc query workloads- definitely want to turn that setting on!
   • Cost Threshold of Parallelism: Every query has a cost, so this setting specifies the threshold with which SQL Server can create and run parallel plans for queries. Typically the default setting is around 5, and that won’t get you far.
   • Max Degree of Parallelism:  determines how many processors SQL Server can use to parallelize a query, based on the number of logical processes that are on the system. This feature goes hand-in-hand with the cost threshold, helping you maintain control of the maximum cost and degree thresholds when parallelizing queries.