SQL Server 2017 – SELECT INTO [A SPECIFIC FILEGROUP]

Forget about Adaptive Query Processing for a minute, what other feature have I been waiting for? SELECT INTO a specific filegroup, not the default filegroup! I have needed this feature many times in the past. Let’s take a look at it using the WideWorldImportersDW database.

Continue reading

The world of Automatic Tuning – SQL Server 2017

Times are changing, 10 years ago I would never have thought that self-tuning databases would be available as a packaged product. I was testing out SQL Server 2017 Automatic Tuning recently and I ended up with the following situation. Below shows an image from the query store.

Continue reading

SQL Server DBCC CHECKDB – Going Parallel

DBCC CHECKDB has the ability to perform parallel checking of objects. However, it absolutely depends on the edition of SQL Server, it only happens when using enterprise edition.

Let’s see this in action. I propose the following tests for this blog post:

  • Test on a SQL Server Enterprise Edition.
  • Test on a non-enterprise edition of SQL Server.

Continue reading

SQL Server Failed to Open Loopback Connection – What?

I could not read my error log on one of my local SQL Servers, when I executed the following code:

EXEC sp_readerrorlog

I received the below:

Msg 22004, Level 16, State 1, Line 2 Failed to open loopback connection. Please see event log for more information. Msg 22004, Level 16, State 1, Line 2 Error log location not found.

Continue reading

SQL Server – DBCC CHECKTABLE

If you know about DBCC CHECKDB then most likely you will know about DBCC CHECKTABLE. Quite simply this command performs primitive system-catalog consistency checks, per-table consistency checks on the single table specified, and cross-table consistency checks on indexed views that refer­ence the specified table. (Page 899 Microsoft SQL Server Internals 2012, Chapter 14, Page 899, Paul Randal)

Continue reading

SQL Server 2017 – Interleaved Execution

I worked on testing interleaved execution with Microsoft back in January, I didn’t do much, just tested the functionality against some in-house code we had. (If you need a detailed primer on the subject, please see https://blogs.msdn.microsoft.com/sqlserverstorageengine/2017/04/19/introducing-interleaved-execution-for-multi-statement-table-valued-functions/)

Continue reading

Azure SQL Database Compatibility Levels

Let’s start off with a quick overview of SQL Server versions and compatibility levels.

  • 100 = SQL Server 2008 and Azure SQL Database
  • 110 = SQL Server 2012 and Azure SQL Database
  • 120 = SQL Server 2014 and Azure SQL Database
  • 130 = SQL Server 2016 and Azure SQL Database
  • 140 = SQL Server 2017 and Azure SQL Database

So with SQL Server 2017 now available to the public what level is a newly created Azure SQL Database set at?

Continue reading