Basic settings for SQL Server 2012/2014/2016, part 5

Part 5: updating the basic settings for SQL Server 2016/2017

It’s been quite a while since I penned my best practices posts for SQL Server 2012/2014 and I’ve been saying for ages that I should update them for SQL Server 2016. Well, it would seem that hell finally froze over as here we are!

Let’s startout with the Query Store. This is a new contraption introduced in SQL Server 2016. It is turned off by default, but in my opinion you should always turn it on. This is done for every database, either via the GUI or

ALTER DATABASE [DatabaseName] SET QUERY_STORE = ON;

The query store needs to be monitored, and I highly recommend that you read this post about Query Store best practices.

For previous versions I highly recommend the Open Query Store - this is an open source alternative (run by Enrico van de Laar and William Durkin) that enables Query Store-style functionality for versions below 2016.

SQL Server 2016 does away with many of the previously more or less “standard” trace flags. 1117, 1118, 2371 and 4199 are all folded into COMPATIBILITY_LEVEL 130. The behavior of 1117 and 1118 should now be handled using ALTER DATABASE per database, and 2371 is automatically enabled for databases with COMPATIBILITY_LEVEL 130. That means that if you have older compatibility levels on the same server you might still want to keep it on, though.

References: https://blogs.msdn.microsoft.com/sql_server_team/sql-server-2016-changes-in-default-behavior-for-autogrow-and-allocations-for-tempdb-and-user-databases/

SQL Server 2016 also introduced something called “scoped database parameters”, enabling setting things like MAXDOP per database. This does away with the clunky need to set potential far-reaching settings on a global level (MAXDOP comes to mind). At the time of writing the following settings can be scoped:- Clear procedure cache

  • MAXDOP
  • Cardinality optimization model regardless of compatibility level
  • Parameter sniffing
  • Query hotfix settings
  • Optimize for ad-hoc workloads
  • Identity cache

Reference: https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-scoped-configuration-transact-sql

I’ve taken these blog posts and done a presentation that cover them. I will be presenting “Boring stable, stable is good - best practices in practice” at the Nordic Infrastructure Conference in Oslo as well as SQL Saturday #704 in Reykjavik, Iceland.