Things you shouldn't really do in SQL Server: Disabling Join Types Globally

A while ago I presented a session which covered transformation rules that are used by the query optimiser to produce our execution plans. I'm not feeling in the mood for relational algebra this morning so instead I'll introduce a command that can cause mayhem on an instance SQL Server: DBCC RULEOFF.

DBCC RULEOFF is an undocumented command, that alone makes me want to use it but in order to cover one's backside please don't do any of the following in any environment apart from a disposable sandbox that only you use for weird and wonderful experiments in SQL Server, because we are going to break it...

Let's take a simple query with a join:

SELECT e.BusinessEntityID, e.JobTitle, p.FirstName
FROM HumanResources.Employee e
INNER JOIN Person.Person p
ON e.BusinessEntityID = p.BusinessEntityID;

If I look at the estimated execution plan I can see that the optimiser has selected a nested loop join:

Now I could use the QUERYRULEOFF hint to disable that join for this particular query and for this particular join type I'd need two different hints to disable nested loop operators for the join:

OPTION (QUERYRULEOFF JNtoNL, QUERYRULEOFF JNtoIdxLookup);

This will force the optimiser to use a different join type and the estimated plan this time shows a hash match join but for this article we're not looking at QUERYRULEOFF, we're looking at DBCC RULEOFF which will still do the same thing only it's not for one query, it's for the entire instance.

MWAHAHAHAHAHAHA

That's right, running DBCC RULEOFF('JNtoNL') will essentially switch off the nested loop join for every single query running on my instance, well, sort of. After running the command I can check what rules are off on my instance by first running DBCC TRACEON(3604) to redirect DBCC output to my messages tab and then run DBCC SHOWOFFRULES:

Rules that are off globally:

JNtoNL

That's just one of the rules though, so just as when I used the query hint with without nested loop joins I'll also run DBCC RULEOFF('JNtoIdxLookup') to disable the other transformation rule that the optimiser can use for a nested loop join and verify again with DBCC SHOWOFFRULES. Now that they're both disabled the estimated plan looks a bit different and is using a hash match operator instead.

A different logical join type will not be affected. If I change the INNER JOIN to a LEFT JOIN and check the execution plan I will see a nested loop operator again.

SELECT e.BusinessEntityID, e.JobTitle, p.FirstName

FROM HumanResources.Employee e

LEFT JOIN Person.Person p

ON e.BusinessEntityID = p.BusinessEntityID;

This is how transformation rules work, essentially they are a substitution for a logical operation (INNER JOIN, LEFT JOIN etc) for a physical operation, that is the operator we see in the execution plan. So far we've only disabled the following rules JNtoNL and JNtoIdxLookup and they only affect the INNER JOIN logical operations. 

For left joins we'll need to switch off the corresponding transformation rules (LOJNtoNL and LeftSideJNtoIdxLookup) and when done we can see the same effect on the estimated execution plan as before where the optimiser has used a hash match join.

Now whilst I can make a case of disabling rules at a query level to check execution plans using different join types (or you could use join hints) I can't really make the same case for disabling them at an instance level. But this post is titled "Things you shouldn't really do..." so let's do something completely reckless.

If I run the following query I can see every transformation rule that contains 'JN' as in JOIN and I've also included the command to disable that rule:

SELECT [name], 'DBCC RULEOFF (''' + [name] + ''');' AS [DontDoIt!!!]

FROM sys.dm_exec_query_transformation_stats

WHERE [name] LIKE '%JN%';

For no other reason but for widespread chaos I can now disable every transformation rule that substitute joins by running each all of the commands returned by my query. Now things are so bad that if I try to run my query I get the following error:

Msg 8624, Level 16, State 1, Line 5

Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services.

I think that means it's broken, at least if I want to join any tables anyway!

So please, please, please don't do this. There's literally no reason to do so on any SQL system of any kind. I will put everything back by running the following so I can generate the commands to switch on all rules once again and verify with DBCC SHOWOFFRULES that nothing has remained disabled. 

SELECT [name], 'DBCC RULEON (''' + [name] + ''');' AS [DoIt!!!]

FROM sys.dm_exec_query_transformation_stats

WHERE [name] LIKE '%JN%';

Another reason why I stay well away from relational algebra.

SQL Server Management Studio: Database Reports - All Blocking Transactions

I've used SQL Server Management Studio for many years and whilst there's other options out there like Azure Data Studio, SQLCMD  or PowerShell it's mainly my go to option for doing stuff in SQL Server.

For most DBA I write T-SQL code; for example I haven't used Activity Monitor for a long time and instead prefer the use of Dynamic Management Objects to query out the bits of information that I need.  

But Management Studio does have some useful functionality in there. If I right click on a database, select Reports and Standard Reports I get a list of all kinds of different reports where I see what's 

I'll be honest, I've occasionally used the Disk Usage reports and apart from that I had no idea what else was in here so I've decided to look at the All Blocking Transactions report in action being that it's a fairly common monitoring task and in order to do that I'll run the following update statement in a transaction without committing it or rolling it back:

BEGIN TRAN

UPDATE Person.Person
SET FirstName = 'Dave'
WHERE FirstName = 'David' and LastName = 'Bartness';

When I run this code I will get a message saying that 1 row has been affected but as my transaction is still open my update has not been committed which means if I try to run a select query on that same table in another query window I will run into a classic case of blocking:

SELECT * FROM [AdventureWorks2019].[Person].[Person]

My select query is now in a bit of limbo, and in Management Studio I can see that it's stuck executing my query, now let's run the report:

In this report I am seeing the blocking transaction, often referred to as the header blocker. The Session ID is 62 which is my original update query and the report is showing that it's directly blocking one other transaction. 

In order to see what queries are being blocked I can expand the first column of the report, Transaction ID (in this case 151616) which returns the details of my blocked select statement:

Now as I mentioned earlier on in the post I don't often use Management Studio for much else other than writing queries. I've captured the query used by this particular report and it's, well here it is:

exec sp_executesql @stmt=N'begin try 

declare @tab_tran_locks as table( 

        database_id int   

,       l_resource_type nvarchar(60) collate database_default    

,       l_resource_subtype nvarchar(60) collate database_default

,       l_resource_associated_entity_id bigint   

,       l_blocking_request_spid int   

,       l_blocked_request_spid int   

,       l_blocking_request_mode nvarchar(60) collate database_default   

,       l_blocked_request_mode nvarchar(60) collate database_default   

,       l_blocking_tran_id bigint

,   l_blocked_tran_id bigint   

); 

declare @tab_blocked_tran as table (

        tran_id bigint 

,       no_blocked bigint

);  

declare @temp_tab table( 

        blocking_status int 

,       no_blocked int 

,       database_id int 

,       l_resource_type nvarchar(60) collate database_default  

,       l_resource_subtype nvarchar(60) collate database_default  

,   l_resource_associated_entity_id bigint 

,       l_blocking_request_spid int 

,       l_blocked_request_spid int

,       l_blocking_request_mode nvarchar(60) collate database_default 

,       l_blocked_request_mode nvarchar(60) collate database_default 

,       l_blocking_tran_id bigint

,   l_blocked_tran_id bigint

,   local1 int

,       local2 int

,       b_tran_id bigint

,       w_tran_id bigint

,       b_name nvarchar(128) collate database_default 

,       w_name nvarchar(128) collate database_default 

,       b_tran_begin_time datetime

,       w_tran_begin_time datetime

,       b_state nvarchar(60) collate database_default 

,       w_state nvarchar(60) collate database_default 

,       b_trans_type nvarchar(60) collate database_default 

,       w_trans_type nvarchar(60) collate database_default 

,       b_text nvarchar(max) collate database_default 

,       w_text nvarchar(max) collate database_default 

,       db_span_count1 int

,       db_span_count2 int 

);

insert into @tab_tran_locks 

select                          

        a.resource_database_id

,       a.resource_type

,       a.resource_subtype

,       a.resource_associated_entity_id

,       a.request_session_id as blocking 

,       b.request_session_id as blocked

,       a.request_mode

,       b.request_mode

,       a.request_owner_id

,       b.request_owner_id   

from sys.dm_tran_locks a 

join sys.dm_tran_locks b on   (a.resource_type = b.resource_type and a.resource_subtype = b.resource_subtype and a.resource_associated_entity_id = b.resource_associated_entity_id and a.resource_description = b.resource_description)  

where (a.request_status = ''GRANT'' and (b.request_status = ''WAIT'' or b.request_status = ''CONVERT'')) and a.request_owner_type = ''TRANSACTION'' and b.request_owner_type = ''TRANSACTION'';

insert into @tab_blocked_tran  

select ttl.l_blocking_tran_id

,       count(distinct ttl.l_blocked_tran_id) 

from @tab_tran_locks ttl   

group by ttl.l_blocking_tran_id 

order by count( distinct ttl.l_blocked_tran_id) desc 

insert into @temp_tab  

select  0 as blocking_status

,       tbt.no_blocked

,       ttl.*

,       st1.is_local as local1

,       st2.is_local as local2

,       st1.transaction_id as b_tran_id

,       ttl.l_blocked_tran_id as w_tran_id

,       at1.name as b_name

,       at2.name as w_name

,       at1.transaction_begin_time as b_tran_begin_time

,       at2.transaction_begin_time as w_tran_begin_time

,       case when at1.transaction_type <> 4 

                 then case at1.transaction_state 

                                when 0 then ''Invalid''

                                when 1 then ''Initialized''

                                when 2 then ''Active''

                                when 3 then ''Ended''

                                when 4 then ''Commit Started''

                                when 5 then ''Prepared''

                                when 6 then ''Committed''

                                when 7 then ''Rolling Back''

                                when 8 then ''Rolled Back''

                        end 

                 else case at1.dtc_state 

                                when 1 then ''Active''

                                when 2 then ''Prepared''

                                when 3 then ''Committed''

                                when 4 then ''Aborted''

                                when 5 then ''Recovered''

                        end 

        end b_state

,       case when at2.transaction_type <> 4 

                then case at2.transaction_state 

                                when 0 then ''Invalid''

                                when 1 then ''Initialized''

                                when 2 then ''Active''

                                when 3 then ''Ended''

                                when 4 then ''Commit Started''

                                when 5 then ''Prepared''

                                when 6 then ''Committed''

                                when 7 then ''Rolling Back''

                                when 8 then ''Rolled Back''

                        end 

                 else case at1.dtc_state 

                                when 1 then ''Active''

                                when 2 then ''Prepared''

                                when 3 then ''Committed''

                                when 4 then ''Aborted''

                                when 5 then ''Recovered''

                        end 

        end w_state

,       at1.transaction_type as b_trans_type

,               at2.transaction_type  as w_trans_type

,       case when r1.sql_handle IS NULL then ''--'' else ( select top 1 substring(text,(r1.statement_start_offset+2)/2, (case when r1.statement_end_offset = -1   then (len(convert(nvarchar(MAX),text))*2) else r1.statement_end_offset  end - r1.statement_start_offset) /2  ) from sys.dm_exec_sql_text(r1.sql_handle)) end as b_text

,       case when r2.sql_handle IS NULL then ''--'' else ( select top 1 substring(text,(r2.statement_start_offset+2)/2, (case when r2.statement_end_offset =-1 then len(convert(nvarchar(MAX),text))*2  when r2.statement_end_offset =0  then len(convert(nvarchar(MAX),text))*2  else r2.statement_end_offset  end - r2.statement_start_offset) /2  ) from sys.dm_exec_sql_text(r2.sql_handle)) end as w_text 

,       ( Select count(distinct database_id) from sys.dm_tran_database_transactions where transaction_id = st1.transaction_id ) as db_span_count1

,       ( Select count(distinct database_id) from sys.dm_tran_database_transactions where transaction_id = st2.transaction_id ) as db_span_count2  

from @tab_tran_locks ttl 

inner join sys.dm_tran_active_transactions at1 on(at1.transaction_id = ttl.l_blocking_tran_id) 

inner join @tab_blocked_tran tbt on(tbt.tran_id = at1.transaction_id)  

inner join sys.dm_tran_session_transactions st1 on(at1.transaction_id = st1.transaction_id) 

left outer join sys.dm_exec_requests r1 on(at1.transaction_id = r1.transaction_id ) 

inner join sys.dm_tran_active_transactions at2 on(at2.transaction_id = ttl.l_blocked_tran_id) 

left outer join sys.dm_tran_session_transactions st2  on(at2.transaction_id = st2.transaction_id)  

left outer join  sys.dm_exec_requests r2 on(at2.transaction_id = r2.transaction_id ) 

where st1.is_user_transaction = 1

order by tbt.no_blocked desc;

declare @db_blocking_tab table (

        database_id int

,       blocking_status int

,       no_blocked int

,       total_blocked int

,       l_resource_type nvarchar(60) collate database_default 

,       l_resource_subtype nvarchar(60) collate database_default 

,       l_resource_associated_entity_id bigint

,       l_blocking_request_spid int

,       l_blocked_request_spid int

,       l_blocking_request_mode nvarchar(60) collate database_default 

,       l_blocked_request_mode nvarchar(60) collate database_default 

,       local1 int

,       local2 int

,       b_tran_id bigint

,       w_tran_id bigint

,       b_name nvarchar(128) collate database_default 

,       w_name nvarchar(128) collate database_default 

,       b_tran_begin_time datetime

,       w_tran_begin_time datetime

,       b_state nvarchar(60) collate database_default 

,       w_state nvarchar(60) collate database_default 

,       b_trans_type nvarchar(60) collate database_default 

,       w_trans_type nvarchar(60) collate database_default 

,       b_text nvarchar(max) collate database_default 

,       w_text nvarchar(max) collate database_default 

,       db_span_count1 int

,       db_span_count2 int

,       lvl int

); 

declare @b_tran_id_tab table (  tran_id bigint);

WITH Blocking(

        database_id

,       blocking_status

,       no_blocked

,       total_blocked

,       l_resource_type

,       l_resource_subtype

,       l_resource_associated_entity_id

,       l_blocking_request_spid

,       l_blocked_request_spid

,       l_blocking_request_mode

,       l_blocked_request_mode

,       local1

,       local2

,       b_tran_id

,       w_tran_id

,       b_name

,       w_name

,       b_tran_begin_time

,       w_tran_begin_time

,       b_state

,       w_state

,       b_trans_type

,       w_trans_type

,       b_text

,       w_text

,       db_span_count1

,       db_span_count2

,       lvl)  

AS ( SELECT 

        database_id

,       blocking_status

,       no_blocked

,   no_blocked

,       l_resource_type

,       l_resource_subtype

,       l_resource_associated_entity_id

,       l_blocking_request_spid

,       l_blocked_request_spid

,       l_blocking_request_mode

,       l_blocked_request_mode

,       local1

,       local2

,       b_tran_id

,       w_tran_id

,       b_name

,       w_name

,       b_tran_begin_time

,       w_tran_begin_time

,       b_state

,       w_state

,       b_trans_type

,       w_trans_type

,       b_text

,       w_text

,       db_span_count1

,       db_span_count2

,       0       

from @temp_tab          

UNION ALL       

SELECT E.database_id

,       E.blocking_status

,       M.no_blocked

,       convert(int,E.no_blocked + total_blocked)

,       E.l_resource_type

,       E.l_resource_subtype

,       E.l_resource_associated_entity_id

,       M.l_blocking_request_spid

,       E.l_blocked_request_spid

,       M.l_blocking_request_mode

,       E.l_blocked_request_mode

,       M.local1

,       E.local2

,       M.b_tran_id

,       E.w_tran_id

,       M.b_name

,       E.w_name

,       M.b_tran_begin_time

,       E.w_tran_begin_time

,       M.b_state

,       E.w_state

,       M.b_trans_type

,       E.w_trans_type

,       M.b_text

,       E.w_text

,       M.db_span_count1

,       E.db_span_count2

,       M.lvl+1         

from @temp_tab AS E                     

JOIN Blocking AS M ON E.b_tran_id = M.w_tran_id )  

insert into @db_blocking_tab 

select * from Blocking  

insert into @b_tran_id_tab 

select top 20 b_tran_id from @db_blocking_tab 

where database_id = db_id() group by b_tran_id order by max(total_blocked) desc ; 

select  (dense_rank() over (order by dbt.no_blocked desc,dbt.b_tran_id))%2 as l1 

,       (dense_rank() over (order by dbt.no_blocked desc,dbt.b_tran_id,dbt.w_tran_id))%2 as l2

,       dbt.* 

from @b_tran_id_tab btid 

left outer join @db_blocking_tab dbt on (btid.tran_id = dbt.b_tran_id)  

order by dbt.no_blocked desc, dbt.b_tran_id,dbt.w_tran_id  

end try 

begin catch 

select -100 as l1

,       ERROR_NUMBER() as l2

,       ERROR_SEVERITY() as blocking_status

,       ERROR_STATE() as no_blocked

,       ERROR_MESSAGE() as total_blocked

,       1 as l_resource_type,1 as l_resource_subtype,1 as l_resource_associated_entity_id,1 as l_blocking_request_spid,1 as l_blocked_request_spid,1 as l_blocking_request_mode,1 as l_blocked_request_mode,1 as local1,1 as local2,1 as b_tran_id,1 as w_tran_id,1 as b_name,1 as w_name,1 as b_tran_begin_time,1 as w_tran_begin_time,1 as b_state,1 as w_state,1 as b_trans_type,1 as w_trans_type,1 as b_text,1 as w_text,1 as db_span_count1,1 as db_span_count2,1 as lvl 

end catch',@params=N''

That's a pretty big query and whilst it does return some useful query level information I can't see other information such as the login details or the host name of the blocked/blocking queries that I might need to determine who to blame, sorry I mean take the appropriate course of action for resolving the blocking problems. 

So is there a better way? 

Well in my opinion, absolutely yes! 

I could use a third party script such as sp_whoisactive or this one from Pinal Dave both of which I can honestly say I use all of the time when investigating blocking, but I also encourage people to spend time looking into SQL's Dynamic Management Objects and the DMV's such as sys.dm_exec_requests and sys.dm_tran_locks, both of which are used in the query from the All Blocking Transactions report above. It's a great way to start peeking into the internals of SQL Server and working with their information to create custom scripts for monitoring and diagnosing problems.

Index Column Order

In this post I'm going to demonstrate one of the important factors of index design, the index column order. I'm going to be using the AdventureWorks2019 sample database and we'll take a look at the IX_Person_LastName_FirstName_MiddleName non-clustered index which is on the Person.Person table.

By looking at the index properties in Management Studio I can see the columns that make up the index and they have been put in the column order of LastName, FirstName and MiddleName. It's also worth noting the name of the index matches perfectly with the columns and their order which is a great example of a great naming convention!  

So how does the column order affect our queries? Let's start with the following query:

SELECT FirstName, LastName FROM Person.Person

WHERE LastName = 'Stewart';

Here we're selecting the FirstName and LastName columns from the Person.Person table where the LastName is 'Stewart', which is commonly referred to as filtering the rows. If I run the query it returns 93 rows and if I look at the execution plan (or query plan as it is also known) I can see that the optimiser has used an index seek operator on the IX_Person_LastName_FirstName_MiddleName index:

Now we'll try another query and change the WHERE clause to return rows, 87 in this example, where the FirstName = 'David' (because it's a great name):

SELECT FirstName, LastName FROM Person.Person
WHERE FirstName = 'David';

Although FirstName is present in our index as the second column our query plan is a little bit different this time:

Although we can see the optimiser is still using our index the optimiser has decided to use an index scan operator instead this time. In order to understand why this is different we have to look into not the rows returned by the engine (in this case 99) but the number of rows the engine has had to read to return those rows:

In Plan Explorer I can see that the engine has had to read through 19972 rows to return 87 however our first query that utilised the index seek operator has only had to read through 93 rows (and returned the same number):

Its also worth noting that for our index scan query we also see a Reason for Early Termination: Good Enough Plan Found which means the optimiser has retrieved the execution plan from cache, we don't see this message for the plan using an index seek as the optimiser has used a Trivial Plan instead.

This shows how a column cannot be effectively used for filtering (using the where clause in our query) unless it is the first column in an index. In the second query the index has still been used by the optimiser but it is unable to perform a more efficient seek operation and instead has had to scan (or read) through the entire index to return our results.

Now we could create an additional non-clustered index that has the FirstName column as the first column but we also have to determine the suitability of that index. If we were to create indexes for every single query on our database we'd end up with a lot of surplus indexes that are barley used which will inevitably cause a lot of overhead for things like table update operations and index maintenance. 

It's much more likely for queries to be filtering on LastName so that's a perfect candidate for a non-clustered index, if we're never going to use the FirstName column in where clauses, joins etc then an index using that column isn't goign to provide any benefit which is why understanding how the columns will be used in our queries is the most important factor in designing index strategies.

SQL 2019: Physical reads are counted two times for read-aheads in sys.dm_exec_query_stats

Microsoft recently released Cumulative Update 15 for SQL Server 2019. It contains a bunch of fixes and some improvements, I get a bit geeky with updates like this and love to have a look through the different fixes to see 

"Physical reads for read-ahead reads are counted incorrectly (two times) when you run queries. Therefore, the information in sys.query_store_runtime_stats and sys.dm_exec_query_stats shows incorrect values."

So if you're using these management views to look at performance metrics for your queries you're going to get incorrect results and you might be thinking the queries are doing way more work than what they're actually doing.

In order to test this in a before and after update type scenario I'm going to first force SQL Server to clean out it's buffer pool using DBCC DROPCLEANBUFFERS and the procedure cache with DBCC FREEPROCCACHE (btw, don't do this on anything else other than a sandbox environment). This will ensure the next time I run a query it will have no pages in memory and will have to retrieve the pages from disk, and I'm clearing the procedure cache as I'm going to be querying the sys.dm_exec_query_stats DMV.

Test query: 

SELECT * FROM Person.Person  

I've set SET STATISTICS IO ON so I can see after my query has ran the logical, and in this case more importantly the physical reads of my query and it shows 3 physical reads and 3866 read-ahead reads (which are still from disk):

Table 'Person'. Scan count 1, logical reads 3821, physical reads 3, page server reads 0, read-ahead reads 3866

SELECT [text] AS QueryText, last_physical_reads FROM sys.dm_exec_query_stats

CROSS APPLY sys.dm_exec_sql_text(sql_handle)

WHERE [text] = 'SELECT * FROM Person.Person'

And I get the following results showing the last physical reads of my query was 7779, which is wrong:

I've now updated one of my test instances to CU15 and will run the same queries as before, and this time we have a much more accurate value returned from the DMV:

Now I should add there's still a difference between the statistics output and the sys.dm_exec_query_stats DMV for physical reads and to be totally honest I don't know exactly why but I've the question using #sqlhelp on Twitter and will update when I find out!

Query Simplification: Join Removal with Foreign Keys

Foreign keys are used in database design to enforce referential integrity but they also have some performance benefits as well that you might not necessarily notice unless you're looking into your execution plans.

Let's take the following query using the AdventureWorks2019 sample database where I'm selecting the BusinessEntityID and JobTitle from the HumanResources.Employee table and by using an inner join I'm only returning rows that have matching values (BusinessEntityID) in both tables:

SELECT e.BusinessEntityID, e.JobTitle

FROM HumanResources.Employee e

INNER JOIN Person.Person p 

ON e.BusinessEntityID = p.BusinessEntityID;

I might expect to see an operator that performs the inner join in the execution plan but when I look at the plan this is what I get:

Plan 1: Index Scan on HumanResources.Empoyee

Despite my query containing a join to the Person.Person table the optimiser has used an execution plan that only contains a clustered index scan of the HumanResources.Employee table. Even though the join is there in our query text it hasn't been used in the plan at all.

The reason why the join isn't present in our first query is partly down to the columns which the query is returning. I'm only selecting two columns which are both from the employee table, if I were to add a column from the Person.Person table such as in the following query then I will get a very different execution plan:

SELECT e.BusinessEntityID, e.JobTitle, p.FirstName

FROM HumanResources.Employee e

INNER JOIN Person.Person p 

ON e.BusinessEntityID = p.BusinessEntityID;

Plan 2: Query with column from Person.Person

By adding this column (or indeed any column from the Person.Person table or even worse if I had used SELECT *) the execution plan has two additional operators; an index seek on the Person.Person table and this time a join operator (Nested Loop) is present in the plan. This is also a great example of why only selecting columns that you actually need in a query is really important otherwise you might be causing needless overhead.

The column selection has clearly influenced the optimisers plan selection and when referencing columns in the Person.Person table in the select the optimiser has had to come up with a plan containing the join, but why for our first query has it not done the same thing? 

In this example the join removal is made possible due to a foreign key relationship between the two tables on the BusinessEntityID column. Which if you're playing along in Management Studio you can see under the HumanResources.Employee table under keys (FK_Employee_Person_BusinessEntityID).

As our inner join is essentially returning matching values from both tables and the foreign key exists on those the values the optimiser already knows that the values are matched and in turn as we're not returning any values from the Person.Person table it can avoid the cost of the join and the scan operation on the Person.Person table altogether, but we'll still get the same results.

In order to test this I'm going to remove the foreign key with the following T-SQL and then re-run the first query:

ALTER TABLE [HumanResources].[Employee] DROP CONSTRAINT [FK_Employee_Person_BusinessEntityID]

GO

SELECT e.BusinessEntityID, e.JobTitle

FROM HumanResources.Employee e

INNER JOIN Person.Person p 

ON e.BusinessEntityID = p.BusinessEntityID;

This time although we're only selecting records from the employee table we get a very different query plan that is unable to take advantage of join removal and as such has to perform an index scan on both tables which are joined using the Hash Match operator. 

Plan 3: Query with no foreign key

Before I carry on I'll add the foreign key back with the following T-SQL:

ALTER TABLE [HumanResources].[Employee]  WITH CHECK ADD  CONSTRAINT [FK_Employee_Person_BusinessEntityID] FOREIGN KEY([BusinessEntityID])

REFERENCES [Person].[Person] ([BusinessEntityID])

GO

ALTER TABLE [HumanResources].[Employee] CHECK CONSTRAINT [FK_Employee_Person_BusinessEntityID]

GO

This is a great example of how foreign keys not only force referential integrity but they do have some less obvious performance benefits too that the optimiser can take advantage during query optimisation phases. 

Personal Post: Burns Night

25th of January, Burns Night.

It's a very special day for me because today marks my dads birthday. He's not here to celebrate it but I will take a moment later today and raise a wee dram and have a chat with him.

My dad had lots of very close friends in Scotland. With his birthday being on this particular date he'd often be in Edinburgh today with them all and would be out celebrating into the early hours, and I guarantee would be nursing a slightly sore head tomorrow morning. 

He'd often take the opportunity of being in Scotland to tour the small town rugby grounds in the Borders. My dad was obsessed with rugby, he played well into his forties and then coached afterwards and lived and breathed every second of every game he ever watched. I remember him telling me about his first mini tour there and to me it sounded almost like a pilgrimage of some sort. He never really spoke about things he wanted to do in life but I know this was a big thing for him.

He had some wonderful times and special moments in Scotland but I have to say it was the people that would make him glow more than anything else. It seemed like he made new friends every time he went and he would always talk about the friendly welcome he received everywhere he visited. He went to the Tattoo one year and became pen pals (email was something he wasn't interested in) with the couple who were sat next to him, I absolutely love that.

So to you dad, Happy Birthday. Miss you. 

Today I will think about you surrounded by old and new friends in Edinburgh and I also think about the friends I have met through the community and to those of you in Scotland I wish you a Happy Burns Night - I'll possibly have a wee dram for you all as well.

Getting locked out of SQL Server: Cannot open user default database. Login Failed.

I love playing around with test instances. Recently I've been doing a lot of preparation for some forthcoming blog posts and when trying to log into SSMS this morning I received the following error message:

Unfortunately as it's one of my test instances I'm the only person using it, which also means I'm the only person responsible for creating this problem but we don't have a blame culture around here so let's get on with fixing it and I'll have a lessons learned with myself later over a glass of something.

This error isn't to do with my login as such, it's still there with sysadmin role membership so I don't have to do anything too drastic like restarting SQL Server with the -m or -f startup parameters and recreate it. The error message is telling me that my logins default database cannot be opened, which is more than likely because I've deleted it.

To resolve the issue I just need to connect to a different database but thing is it's not immediately obvious how to do that (and I don't have another login available to simply change my default database). What I need to do is click the Options >> button on the bottom right of the Connect to Server dialog box:

If I then go to the Connection Properties tab I can then use the Connect to database option and browse the server for another database to connect to:

But there's a problem, in order to browse the server I need to connect to it and as I can't do that right now I'm going to run into the same problem again:

Which sucks...

Fortunately I don't have to browse the server and choose a database to connect to. I can just type into the connect to database box and put in a valid database name such as master like in the box below:

Now I've put in a valid database I can successfully connect to my instance in Management Studio and the first thing that I will do is change my logins default database to master with the following T-SQL and the issue won't re-occur:

ALTER LOGIN [DESKTOP-MFKSHR7\David] WITH DEFAULT_DATABASE = master;

SQL Server on Ubuntu 20.04: System has not been booted with systemd as init system (PID 1). Can't operate.

I'm a Windows person. I know this because for two reasons; I've used Windows for a long, long time and am very comfortable with it but also when I venture to different worlds like Linux and encounter errors I literally have no clue what I'm looking at, at least with Windows I might have some sort of idea of where to start!

Earlier I was trying to install SQL Server on Ubuntu 20.04 on my Windows 10 laptop which is using Windows Subsystem for Linux 2.0 following this quickstart guide from Microsoft. 

I'm not going to go through the steps itself but the fun and games started when I got to this command: sudo /opt/mssql/bin/mssql-conf setup

Things originally went fine, and I got the following options for the edition of SQL Server I wanted to install, which I love because it saves having lots of installation media hanging around:

I chose the Developer edition, set my sa password then this happened:

ForceFlush is enabled for this instance.

ForceFlush feature is enabled for log durability.

System has not been booted with systemd as init system (PID 1). Can't operate.

Failed to connect to bus: Host is down

Attempting to start the Microsoft SQL Server service failed.

I'm going to cut a long story short because I'm not a Linux person and thankfully via this post on Github I found this excellent post which fully explained this issue (no support for systemd) and had a rather helpful resolution too which the author describes as a (dirty) trick!

That said, I still wasn't able to start SQL Server with sudo service mssql-server start ( as expected and instead used the following command:

sudo -u mssql /opt/mssql/bin/sqlservr -c -d/var/opt/mssql/data/master.mdf -l/var/opt/mssql/data/mastlog.ldf -e/var/opt/mssql/log/errorlog -x

Voila! SQL Server is now up and running and the solution has been saved to memory (well, the favourites section of my web browser).

Finding Invalid Domain Logins in SQL Server - Windows 10 laptop version

SQL Server provides a built in stored procedure that identifies invalid domain logins (or groups),  which are those that exist on the SQL instance as logins but don't exist on the domain - sp_validatelogins, here's the official description:

Reports information about Windows users and groups that are mapped to SQL Server principals but no longer exist in the Windows environment.

In order to test this on my laptop I'm going to setup a new local account in Windows (I'm using Windows 10 still btw) so to begin with I'll go to Settings and then Family & Other Users.

Then under Other Users at the bottom of the screen select + Add someone else to this PC

This will display a Microsoft sign-in box but as we're just creating a test user on my laptop I'll click the "I don't have this person's sign-in information" link which will give me another box asking me to create a Microsoft account, which I don't want to do, so I'll go ahead and click "Add a user without a Microsoft account" which will (finally) let me create a local user on my laptop, along with password and three security questions.

That's a really long way of doing something simple like creating a new local user, fortunately in PowerShell things are much more straightforward and I can just use the following code to quickly create the new TestUser local user on my laptop: 

New-LocalUser -Name "TestUser" -Description "Just a test" -NoPassword

I could set a password here too but I'm using -NoPassword as I'm going to delete the login once it's setup in SQL Server to test the sp_validatelogins stored procedure, after running the code the following message is displayed showing that the user has been created:

Name     Enabled Description

----     ------- -----------

TestUser True    Just a test

To create the corresponding login in SQL Server I'll run the following T-SQL in Management Studio (DESKTOP-MFKSHR7 is my machine name):

USE [master]

GO

CREATE LOGIN [DESKTOP-MFKSHR7\TestUser] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]

GO

And the login is created! It's worth adding at this point that if I tried to run the T-SQL code without creating the local user first then I'd receive the following error message: "Windows NT user or group 'DESKTOP-MFKSHR7\TestUser' not found. Check the name again."

If I run EXEC sp_validatelogins now then the stored procedure won't return any rows as my login is a valid account in Windows so I need to now delete the local Windows account I just created earlier. This time I'll go straight to PowerShell and run the following:

Remove-LocalUser -Name "TestUser"

This has removed the account from my machine but the login will remain in SQL Server, which is great because that's the whole point of this article! Now I can run the sp_validatelogins stored procedure again (EXEC sp_validatelogins) and this time a row containing the TestUser login name and it's SID are returned indicating it's an invalid account:

What we don't do straightaway is drop the login as we need to unsure that any potential object ownership issues are resolved first and that database users for that login are removed first, here's the advice from Microsoft:

A login cannot be dropped while it is logged in. A login that owns any securable, server-level object, or SQL Server Agent job cannot be dropped.

You can drop a login to which database users are mapped; however, this will create orphaned users. 

Sample Databases for learning SQL Server

You've installed SQL Server. You've installed Management Studio (and presumably Azure Data Studio) and now you're ready to learn SQL! 

But to learn about databases you're going to need some, well databases and whilst creating your own from scratch is a great exercise eventually you'll need something with some data to play around with!

Fortunately Microsoft provide a couple of sample databases you can download and use; namely AdventureWorks which was released way back in SQL Server 2005 and the newer WideWorldImporters (SQL Server 2016) and for both we can download backups of the databases directly from the samples page or use the GitHub SQL Server samples repository. 

AdventureWorks Sample Database

AdventureWorks has a couple of different flavours to choose from; OLTP (Online Transaction Processing) which is the "normal" database shall we say. DW for Data Warehousing workloads which has various Dimension and Fact tables and LT which is a lightweight version of the OLTP database (22Mb for the light version compared to 264Mb). Unless you're specifically learning about Data Warehousing (or your really, really short on disk space) then the OLTP version of the database is the best place to start. 

You can also download the database for previous versions of SQL Server going back to 2008 R2 although the lightweight version isn't available that far back. If you're not sure what version you're on then running the T-SQL statement SELECT @@VERSION will return that information for you.

Under Restore to SQL Server the samples page contains instructions of how to restore your downloaded sample database via the UI in Management Studio, via a T-SQL command or by using Azure Data Studio. There's also instructions on how to deploy the AdventureWorks sample database to Azure SQL Database.

WideWorldImporters Sample Database

Similarly to AdventureWorks the WideWorldImporters sample databases comes in an OLTP and Data Warehouse (OLAP) version with the exception of the lightweight version. There are also bacpac downloads available for use in the Standard and Premium tiers of Azure SQL Database.

For restoring the database the big difference is that WideWorldImporters is only available for SQL Server versions from 2016 (and above) so you won't be able to restore to older versions. It's also bigger too and takes up roughly 3.5Gb of disk space compared to AdventureWorks (OLTP) 264Mb. If you want to try restoring WideWorldImporters to an Azure SQL Managed Instance you can follow this link.

Which one should I use?

In short, both. There's a lot of content out there for learning SQL Server and you'll often find tutorials using one or the other sample database and although WideWorldImporters is newer most content in my experience are using the AdventureWorks database.

What about something a bit meatier?

Although they're great sample databases to use for learning I often see posts looking for more larger databases which are a bit more "real-world" (mainly because databases tend to be much bigger in real life). AdventureWorks does have a slightly larger version available (direct link to backup file) for SQL Server 2016 or above and there are scripts out there to create newer and larger tables too such as this one from Jonathan Kehayias at SQLSkills.

But it's still a sample database and for something much more realistic Stack Overflow regularly export their data and thankfully Brent Ozar has made that data available in SQL Server format and you can download 10Gb, 50Gb or 180Gb sized databases from here (you'll need a BitTorrent client for the larger sized database).