SQL Server – Worktable – Index Intersection and Missing Entries

Background

I was reviewing a simple update query and was bothered, more so perturbed, as to why a worktable was included in the Statistics IO.

Let us see if we can find why.

LAB

Create Schema


if schema_id('worktable') is null
begin

	print 'Create Schema - worktable ....'

	exec('create schema [worktable] authorization [dbo]' );

	print 'Created Schema - worktable '

end
go

 

 

Create Table


set noexec off
go

if object_id('[worktable].[friendAndFamily]') is not null
begin

	set noexec on

end
go

--drop table [worktable].[friendAndFamily]
create table [worktable].[friendAndFamily]
(
	  [id] bigint not null
	, [firstname] varchar(60)
	, [lastname]  varchar(60)

	, [UniqueId]  varchar(40) null     

	, [dateAdded] datetime not null
		constraint FANDFDateAdded default getdate()

	, [dateUpdated] datetime null

)
go

alter table  [worktable].[friendAndFamily]
	add constraint PK_FF_ID
	primary key
	(
		[ID]
	)
go

create index INDX_Lastname
on  [worktable].[friendAndFamily]
(
	[lastname]
)
;

-- drop index [worktable].[friendAndFamily].INDX_Firstname
create index INDX_Firstname
on  [worktable].[friendAndFamily]
(
	[firstname]
)
;

create unique index INDX_UNIQUE_UniqueId
on  [worktable].[friendAndFamily]
(
	[UniqueId]
)
with ignore_dup_key;

set noexec off
go

 

 

Add Data


set nocount on
go

truncate table [worktable].[friendAndFamily]
go

insert into [worktable].[friendAndFamily]
([id],[firstname], [lastname], [UniqueId], [dateAdded])
select 1, 'Tom', 'Smith', 'TSmith', getdate()
union
select 2, 'Beverly', 'Johnson', 'BevJohnson', getdate()
union
select 3, 'Blake', 'Thomas', 'BlThomas', getdate()
go

insert into [worktable].[friendAndFamily]
([id], [firstname], [lastname], [UniqueId], dateAdded)
select
		  rand() * 1E10
		, replace(newid(), '-', '')
		, replace(newid(), '-', '')
		, cast(newid() as varchar(60))
		, getdate()
go 10000

Query

Query Strategy

Here is our query strategy.

Query Condition Record Found Worktable
 Clustered Primary Key  ID=1000  Yes  No
 Clustered Primary Key  ID=-1000 No  No
 First Name  First Name=’Blake’  Yes  No
 Firstname  Firstname=’no name’ No  Yes
 UniqueID  UniqueID=’BevJohnson’  Yes  No
 UniqueID  UniqueID=’no name’ No  Yes

Actual Queries


/*
	set statistics io on;
	set nocount on;
*/
print 'Query on ID - Clustered Index'
update [worktable].[friendAndFamily]  -- 'Query on Clustered Index'
set    lastname = 'N2'
where  id = 1000 

print replicate('*', 120)
go

print 'Query On FirstName (Present)/Update Lastname'
update [worktable].[friendAndFamily]  -- Query On FirstName (Present)/Update Lastname
set  lastname = 'B2'
where firstname = 'Blake' 

print replicate('*', 120)

go

print 'Query On First(Missing)/Update Lastname'
update [worktable].[friendAndFamily]  -- Query on Missing First Name/Update Lastname
set    lastname = 'BE2'
where  firstname = 'No Name'

print  replicate('*', 120)
go

print 'Query On Unique ID (Present)/Update Lastname'
update tblFF  -- Unique ID/Matching Unique ID
set   lastname = 'BE2'
from  [worktable].[friendAndFamily] tblFF
where [uniqueID] = 'BevJohnson'

print replicate('*', 120)
go

print 'Query On Unique ID (Missing)'
update tblFF  -- Unique ID/Missing Unique ID
set   lastname = 'BE2'
from  [worktable].[friendAndFamily] tblFF
where [uniqueID] = 'BevThomas'

print replicate('*', 120)
go

Query Plan

QueryPlan

Statistics IO

clusteredIndex

LAB – Heap

Heap

Drop Clustered Index

Let us drop the Clustered Index, by dropping the Primary key, and see what happens.


	alter table [worktable].[friendAndFamily]
		drop constraint PK_FF_ID

Query Strategy

Here is our query strategy.

Query Condition Record Found Worktable
 ID  ID=1000  Yes  No
 First Name [ Record Found]  First Name=’Blake’  Yes  No
 Firstname [ Record Not Found]  Firstname=’no name’  No  Yes

Query

Query against id, keep in mind, no longer the primary key, as table is now an Heap.

And, also Query against firstname.


	print 'Query on ID - Clustered PK Index'
	update [worktable].[friendAndFamily]  -- 'Query on Clustered PK Index'
	set    lastname = 'N2'
	where  id = 1000 

	print replicate('*', 120)

	print 'Query On First(Match)/Update Lastname'
	update [worktable].[friendAndFamily]  -- Query on Missing First Name/Update Lastname
	set    lastname = 'T.2'
	where  firstname = 'Tom'

	print  replicate('*', 120)

	print 'Query On First(Missing)/Update Lastname'
	update [worktable].[friendAndFamily]  -- Query on Missing First Name/Update Lastname
	set    lastname = 'BE2'
	where  firstname = 'No Name'

	print  replicate('*', 120)

Query Plan

QueryPlan

Statistics IO

Heap


Commentary

What we found is that when our query utilizes an additional Index along with the Clustered Index, we encountered worktable references.

This occurrence is noted when Heaps are referenced with helpful Indexes, as well.

It is not noted for Table Scans; Table Scans go against the table and do not use additional Indexes.

This is not shown in the Query Plan, nor the Statistics Profile, but in the Statistics IO.

Keep in mind that queries that do not reference your Clustered Index also have Nested Loops. The Nested Loops use indexes that target the filtering columns and joins them to the Clustered Index to get the Row ID (RID).

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s