Episode 30

The thirtieth episode is up, a.k.a the “presentation remoote” episode!

We talk about what’s behind the scenes of Power BI desktop, SQL Saturday Vienna (which still was held on a Friday, by the way), new builds of InTune, Windows and Windows Server, the Nordic Infrastructure conference and some presentation technique tips thrown in for good measure.

Knee-deep in Tech on iTunes

Episode 30

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.

    Episode 29

    The twentyninth (and first episode of 2018!) episode is up, a.k.a the “on the ball” episode!

    This time we talk shenanigans with PowerBI and ODBC connections, WANem for WAN emulation, Meltdown and Spectre, yet another insider build and what’s in store for 2018.

    We’ve switched over to Pippa.io for file hosting. Expect some small technical glitches while we get settled in 🙂

    Knee-deep in Tech on iTunes

    Episode 29