Azure SQL DW – Let’s Shuffle?

Initially, the main focus of this post was going to be quick and about using the latest version of SSMS (SQL Server Management Studio) to check out execution plans for Azure SQL Data warehouse (DW). I got a little side tracked by a certain operation called – SHUFFLE, because, I like the name.

Continue reading

4 Improvements to Azure SQL Database

If I was a wizard I would make these 4 improvements to Azure SQL Database. They are in no particular order, in fact I could come up with about 10 features / improvements that I would like to see  but I think 4 will be suffice. Some are more realistic than others, I am sure for some of them I am more likely to get a tiger as a pet.

Continue reading

SQL Vulnerability Assessment for Azure SQL Database

Back in September 2017 Microsoft announced a new security feature for Azure SQL Database called the SQL Vulnerability Assessment (VA). It is currently in preview mode where it has the ability to find, you can guess, security based vulnerabilities for your database such as misconfigurations, excessive permissions, and exposed sensitive data.

Let’s setup a scan. You can find this feature within the settings section of your 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