Table Variable or Temp Table

-> Create the below objects. Initially we start with fewer rows.

create database JB_TableVariable
go
use JB_TableVariable
go
create table Table1(
Col1 int primary key identity (1,1)
, Col2 char(2000)
, Col3 int
, Col4 int
)
create table Table2(
Col1 int primary key identity (1,1)
, Col2 char(2000)
, Col3 int
, Col4 int
)
create table Table3(
Col1 int primary key identity (1,1)
, Col2 char(2000)
, Col3 int
, Col4 int
)
set nocount on
go
insert into Table1 values (SUBSTRING(CONVERT(varchar(255), NEWID()), 0, 8),CONVERT(INT,rand()*1000000),1)
GO 500
insert into Table1 values (SUBSTRING(CONVERT(varchar(255), NEWID()), 0, 8),CONVERT(INT,rand()*1000000),0)
GO 500
insert into Table2 values (SUBSTRING(CONVERT(varchar(255), NEWID()), 0, 8),CONVERT(INT,rand()*1000000),1)
GO 500
insert into Table2 values (SUBSTRING(CONVERT(varchar(255), NEWID()), 0, 8),CONVERT(INT,rand()*1000000),0)
GO 500
insert into Table3 values (SUBSTRING(CONVERT(varchar(255), NEWID()), 0, 8),CONVERT(INT,rand()*1000000),1)
GO 500
insert into Table3 values (SUBSTRING(CONVERT(varchar(255), NEWID()), 0, 8),CONVERT(INT,rand()*1000000),0)
GO 500

-> Create the below stored procedures. Stored procedure sp_tableVariable uses table variable and stored procedure sp_temptable uses temporary table.

— Stored procedure using Table variable
create proc sp_tableVariable
WITH RECOMPILE
as
begin
DECLARE @Table4 TABLE(
Col1 int primary key
, Col2 char(2000)
, Col3 int
, Col4 int
)
insert into @Table4
select a.col1, b.col2, a.col3,b.col4
from Table1 a INNER JOIN Table2 b
ON a.col1 = b.col1
select a.col1, b.col2, a.col3
from Table1 a INNER JOIN @Table4 b
ON a.col1 = b.col1
where b.col4 = 1
order by b.col3 desc
end
— Stored procedure using Temporary table
create proc sp_temptable
WITH RECOMPILE
as
begin
create TABLE #table5(
Col1 int primary key
, Col2 char(2000)
, Col3 int
, Col4 int
)
insert into #table5
select a.col1, b.col2, a.col3,b.col4
from Table1 a INNER JOIN Table2 b
ON a.col1 = b.col1
select a.col1, b.col2, a.col3
from Table1 a INNER JOIN #table5 b
ON a.col1 = b.col1
where b.col4 = 1
order by b.col3 desc
drop table #table5
end

-> Executing stored procedure sp_tableVariable that uses table variable. The query completed within 1 second.

blog17_1

-> Executing stored procedure sp_Temptable that uses temporary table. The query completed within 1 second.

Blog17_2.PNG

-> In this case, when we started initially with 500 rows. Stored procedure execution using temporary table and table variable seems same.

-> Now inserting 5000 more rows and updating the statistics.

use JB_TableVariable
go
set nocount on
go

insert into Table1 values (SUBSTRING(CONVERT(varchar(255), NEWID()), 0, 8),CONVERT(INT,rand()*1000000),1)
GO 5000
insert into Table2 values (SUBSTRING(CONVERT(varchar(255), NEWID()), 0, 8),CONVERT(INT,rand()*1000000),1)
GO 5000
insert into Table3 values (SUBSTRING(CONVERT(varchar(255), NEWID()), 0, 8),CONVERT(INT,rand()*1000000),1)
GO 5000

update statistics Table1 with fullscan
update statistics Table2 with fullscan
update statistics Table3 with fullscan

-> Executing stored procedure sp_tableVariable that uses table variable. The query completed in 3 seconds.

Blog17_3.PNG

-> Executing stored procedure sp_Temptable that uses temporary table. The query completed in 1 second.

Blog17_4.PNG

-> Now inserting 50000 more rows and updating the statistics.

use JB_TableVariable
go
set nocount on
go
insert into Table1 values (SUBSTRING(CONVERT(varchar(255), NEWID()), 0, 8),CONVERT(INT,rand()*1000000),1)
GO 50000
insert into Table2 values (SUBSTRING(CONVERT(varchar(255), NEWID()), 0, 8),CONVERT(INT,rand()*1000000),1)
GO 50000
insert into Table3 values (SUBSTRING(CONVERT(varchar(255), NEWID()), 0, 8),CONVERT(INT,rand()*1000000),1)
GO 50000
update statistics Table1 with fullscan
update statistics Table2 with fullscan
update statistics Table3 with fullscan

-> Executing stored procedure sp_tableVariable that uses table variable. The query completed in 1 minute 37 seconds.

Blog17_5.PNG

-> Not a good sign, the query this time took close to 97 seconds. From the execution plan, it is clear that we are spending most of our time in sorting the data and also it is evident that the sort is spilling to tempdb. Let us look at the sort and see why it is spilling to tempdb,

Blog17_5_1.PNG

-> The sort operator advises us that the optimizer estimated 1 row, but actually processed 55500 rows. Due to the bad estimates, sort spilled to tempdb to a greater extent. The warnings section updates us that the optimizer estimated and provided 1024KB granted memory and this made close to 33713 pages to be written to disk (Tempdb) as we did not have sufficient memory for the sort to process 55500 rows in memory.

-> Further we are seeing a nested loop join as the estimates for the clustered index scan on @table4 and subsequent sort operator as 1. The problem with table variables is that when used, the optimizer will always estimate the numbers of rows for table variable as 1 no matter how many millions of rows it contains.

Blog17_5_2.PNG

-> This resulted in the stored procedure performing very bad. Lets look at the other stored procedure that uses temporary table.

-> Executing stored procedure sp_Temptable that uses temporary table. The query completed in 11 seconds.

blog17_6

-> Stored procedure sp_Temptable comparably is quiet faster, the query took close to 11 seconds. From the execution plan, we are spending most of our time in scanning data from temporary table #table5 and table Table1. We are seeing a merge join and then a sort is employed. Lets look at the sort operator. We are seeing a warning symbol in the sort  operator indicating sort spilling to tempdb, but lets see how serious is that,

blog17_6_1

-> The sort operator advises us that the optimizer estimated 55546.3 rows and actually processed 55500 rows. The estimates seems pretty decent. The sort spilled to tempdb. The warnings section updates us that the optimizer estimated and provided 139040KB granted memory and this made close to 13875 pages to be written to disk (Tempdb). Sort spilling to tempdb is definitely an issue and should be resolved to best extent possible. But in our case the spill seems comparably less and the query completed fine. Removing the spill can make this query even faster.

-> We are seeing a merge join as the estimates for the clustered index scan on #table4 and subsequent sort operator are all fine.

Blog17_6_2.PNG

-> This resulted in the stored procedure sp_Temptable performing comparatively faster.

-> So does this tell us that we should always use temporary tables over table variables? No not at all. Each and every query should be tested with different combinations and we should come to a concluson based on the results.

-> Table variables and temporary tables work efficiently when used appropriately. Temporary tables allows you to add indexes after the temporary tables are created along with implicit indexes that are created as part of primary key or foreign keys. But table variables allow only implicit indexes that are created as part of primary key or foreign keys.

-> Table variables and temporary tables have their own disadvantages. You will see metadata contention when using temporary tables, but not with table variables.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

 

Optimize for ad hoc workloads

-> Create the required database using below query,

create database JB_AdHoc
go

-> Execute the below query. Copy the output and place it in a new query window and execute it to create the required objects,


set nocount on
declare @i int = 1
declare @j int = 1000
print 'set nocount on'
print 'use JB_AdHoc'
print 'go'
while (@i!=1001)
begin
print 'IF OBJECT_ID(''Products'+convert(varchar,@i)+''', ''U'') IS NOT NULL
drop table Products'+convert(varchar,@i)+'
go
create table Products'+convert(varchar,@i)+'(
ProductNo int primary key identity (1,1),
ProductArea int ,
ProductQuantity int)
go
insert into Products'+convert(varchar,@i)+' values ('+convert(varchar,@j)+',10000 * rand())
go 1001
--'+convert(varchar,@i)+'-'
set @i = @i + 1
set @j = @j - 1
end

-> The output of the above query contains table creation script for 1000 tables. It also contains insert queries to insert 1001 rows on each of the 1000 tables. The query took close to 15 minutes in my lab machine to complete.

-> Once the objects are created, execute the below query,

 


use JB_AdHoc
go
set nocount on
declare @i int = 1
declare @SQL varchar(max)
print 'set nocount on'
print 'use JB_AdHoc'
print 'go'
print 'DECLARE @ProductNo INT'
print 'DECLARE @ProductArea int'
print 'DECLARE @ProductQuantity int'
while (@i<=1000)
begin
set @SQL = 'select @ProductNo = ProductNo, @ProductArea = ProductArea, @ProductQuantity = ProductQuantity from Products'+convert(varchar,@i)+' where ProductNo in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20) --JB_AdHoc Workload'
print (@SQL)
set @i = @i + 1
end

-> Copy the output and place it in a new query window. Before executing the query, run “dbcc freeproccache”. The query took 3 seconds in my machine.

blog16_1

-> Execute the below query and check how many plan are created,

select text, query_plan, execution_count
from sys.dm_exec_query_stats
outer apply sys.dm_exec_sql_text(sql_handle)
outer apply sys.dm_exec_query_plan(plan_handle)
where text like '%DECLARE @ProductNo INT%'

blog16_2blog16_2_1

-> It is clear that the optimizer has created 1 unique plan for each of the select query executed. We are able to see 1000 plans created from the above screenshot with execution_Count of 1. So this means that the adhoc query ran just once.

-> Execute the below query and check the size of the plans created,


select text, query_plan, usecounts, size_in_bytes/1024.0 as 'Size_in_KB', cacheobjtype, objtype
from sys.dm_exec_cached_plans
outer apply sys.dm_exec_sql_text(plan_handle)
outer apply sys.dm_exec_query_plan(plan_handle)
where text like 'DECLARE @ProductNo INT%'

blog16_3

-> It is clear that close to 39 MB is used by these 1000 plans in the plan cache.

-> Let’s execute the SELECT query again and see the plan details again,

Blog16_4.PNG

-> This time the query completed within 1 second. This is because the optimizer did not create the plan again, instead used the plan created before.

-> Lets check the output of queries related to plans, the execution_count has changed from 1 to 2 for the plans created previously,

blog16_5blog16_5_1

-> The overall size of the plan did not change, since we reused the plan from the cache. But the usecounts this time is 2.

Blog16_6.PNG

-> There are scenarios where we may see adhoc queries plans in the cache that were run only once that may be using much of memory.

-> Sp_configure ‘optimize for ad hoc workloads’ can help us in those scenarios, wherein it creates a smaller size stubs during the initial run and then goes onto create a regular plan when the same query is run again.

-> Execute the below query,


sp_configure 'show advanced options',1
reconfigure
sp_configure 'optimize for ad hoc workloads',1
reconfigure

-> Run “dbcc freeproccache” to clear the cache and then run the select query again. the query takes 3 seconds again.

Blog16_7.PNG

-> Looking at the plan cache now. This time we dont see any plans created, We just see NULL for query_plan.

blog16_8_1blog16_8_1_1

-> This time cacheobjtype is “Compiled Plan Stub” and the size is just 242 KB and not 39 MB.

blog16_8_2

-> Let us run the select statement again. This time also it took 3 seconds.

blog16_7

-> Looking at the plan cache now,

blog16_2blog16_2_1
blog16_3

-> After the second run of the select query, it has created a regular plan on the cache and we dont see the stubs anymore.

-> If you run the select query again now, it will complete within 1 sec and will not take 3 seconds as the plans are not created again, but will be used from the cache.

-> “Optimize for ad hoc workloads” will ensure that the plans of adhoc queries that are executed only once will not occupy much memory.

-> The problem with “Optimize for ad hoc workloads” setting is that the optimizer will have to spend time in creating plan twice for adhoc queries that executes more than once. Optimizer will create a stub first time and then the regular plan when the same query executes again. Any other execution of the same query will use the plan from the cache after the regular plan is created. Hence there may be a slight performance hit for adhoc queries that are executed more than once.

-> It is important to check the cache to see if the cache has more number of singleton plans before enabling this setting. Carefull testing needs to be done before enabling this setting.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Tempdb contention

-> Create the required database and objects using below script. Execute each script individually and not once at a time,

create database JB_Tempdb_Contention
go
USE [JB_Tempdb_Contention]
GO
DROP TABLE [dbo].[Products]
GO
CREATE TABLE [dbo].[Products](
[ProductNo] [int] IDENTITY(1,1) NOT NULL,
[ProductTag] [int] NULL
PRIMARY KEY CLUSTERED
(
[ProductNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
insert into [Products] values (1)
go 500
create table OrderData(
sno int primary key identity(1,1)
,sname char(1000)
,sname1 char(1000)
,price int)
set nocount on
insert into OrderData values (replicate(‘a’,50),replicate(‘b’,60),(rand()*10000))
go 1000000

-> Create the below stored procedure,

USE [JB_Tempdb_Contention]
GO
CREATE procedure [dbo].[JB_Proc_orderDetails]
as
begin
set nocount on
create table #Temp_Table (ProductNo int, ProductTag int)
insert into #Temp_Table select ProductNo,ProductTag from Products
end
create proc JB_productDetails
as
begin
declare @sname char(1000)
declare @sname1 char(1000)
declare @price int
select top (100000) @sname=sname,@sname1=sname1,@price=price
from OrderData
where sno>1000
order by sname desc
end

->Executing stored procedure “JB_productDetails” from SSMS and it completes in 94 seconds.

blog15_6blog15_6_1

-> The query spends most of its time on SORT operator. The sort spills to tempb.

-> Using ostress utility, Iam executing the stored procedure “JB_Proc_orderDetails” with 25 connections and with 500 iterations for each connection.

C:\Program Files\Microsoft Corporation\RMLUtils>ostress -E -SJB\IN2014 -dJB_Tempdb_Contention -Q”EXEC [dbo].[JB_Proc_orderDetails]” -n25 -r500

-> While the ostress utility runs, I am executing the below query in SQL server management studio to see if there are any waiting queries,

select * from master..sysprocesses where program_name like ‘%ostress%’

blog15_1

Blog15_2_1.PNG

-> We could see that the lastwaittype is PAGELATCH_* and the waitresource is 2:1:1. OSTRESS took close to 144 seconds to execute the workload.

-> 2:1:1 can be explained as : : . As per https://msdn.microsoft.com/en-us/library/mt790177.aspx Page number 1 is a PFS page.

->  When there is a latch contention on tempdb you will see lot of sessions waiting on Pagelatch_* similar to the screenshot above.

PFS Page: A PFS page occurs once in 8088 pages. SQL Server will attempt to place a PFS page on the first page of every PFS interval(8088Pages). The only time a PFS page is not the first page in its interval is in the first interval for a file. File header page is first, and the PFS page is second. (Page ID starts from 0 so the first PFS page is at Page ID 1). If (page number)/8088 is round value then the page is PFS page.

GAM Page: GAM page on the second page in the data file, next GAM page is placed at 511230 Page after first GAM page (GAM interval). If (page number-1)/511230 is round value then the page is GAM page.

SGAM Page: SGAM page on the third page, next SGAM page is placed at 511230 Page after first SGAM page. If (page number-2)/511230 is round value then the page is GAM page.

blog15_2

-> Now that we know the queries are waiting on PFS page. We will follow the recommendations provided in https://support.microsoft.com/en-us/help/2154845/recommendations-to-reduce-allocation-contention-in-sql-server-tempdb-database by creating same number of data files for tempdb as the logical processors since we have less than 8 logical processor.

-> Lets look at the current tempdb configuration,

blog15_3

-> I have 4 logical processors, Hence adding another 3 data files and resizing the files to same size.

USE [master]
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N’tempdev’, SIZE = 614400KB )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N’tempdev1′, FILENAME = N’C:\SQL\MSSQL12.IN2014\MSSQL\DATA\tempdev1.ndf’ , SIZE = 614400KB , FILEGROWTH = 10% )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N’tempdev2′, FILENAME = N’C:\SQL\MSSQL12.IN2014\MSSQL\DATA\tempdev2.ndf’ , SIZE = 614400KB , FILEGROWTH = 10% )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N’tempdev3′, FILENAME = N’C:\SQL\MSSQL12.IN2014\MSSQL\DATA\tempdev3.ndf’ , SIZE = 614400KB , FILEGROWTH = 10% )
GO

-> After adding the files, the tempdb looks as below,

blog15_5

->As per details from the above article, I am adding trace flag 1118 from the startup parameter to avoid SGAM contention.

-> Using ostress utility, Iam executing the stored procedure “JB_Proc_orderDetails” with 25 connections and with 500 iterations for each connection again.

C:\Program Files\Microsoft Corporation\RMLUtils>ostress -E -SJB\IN2014 -dJB_Tempdb_Contention -Q”EXEC [dbo].[JB_Proc_orderDetails]” -n25 -r500

blog15_7

-> This time the query took close to 161 seconds. The workload took more time after following the best practices.

blog15_4

-> It seems like after adding additional tempdb files, we dont see PFS contention any more, but it seems like the queries from the workloads are waiting on system tables and this looks like some kind of metadata contention.

-> To check what objects these pages belongs to. I tried the below,

dbcc traceon (3605)
dbcc page (2,1,116,-1)
xp_readerrorlog

-> From the error log I was able to see “m_objId (AllocUnitId.idObj) = 34“. Running “select object_name(34)” shows the object as sysschobjs.

-> As per https://msdn.microsoft.com/en-us/library/ms179503.aspx. sysschobjs  “Exists in every database. Each row represents an object in the database.”.

-> Executing the stored procedure “JB_productDetails” after adding 3 more data files in tempdb. The query now takes 117 seconds,

blog15_8blog15_8_1

-> It seems like both stored procedure JB_Proc_orderDetails and JB_productDetails executions are slow after adding additional tempdb files.

-> Stored procedure JB_Proc_orderDetails is slow due to tempdb metadata contention and stored procedure JB_productDetails is slow due to the sort spilling to tempdb. The sort spill to tempdb becomes worse with each additional tempdb data file.

-> There is no way to resolve the tempdb metadata contention by adding more data files to tempdb or by adding any other trace flags that i am aware of.

-> Let me try modifying the stored procedure JB_Proc_orderDetails in such a way that it uses a table variable instead of temp table as below,

USE [JB_Tempdb_Contention]
GO
Alter procedure [dbo].[JB_Proc_orderDetails]
as
begin
set nocount on
declare @Temp_Table table (ProductNo int, ProductTag int)
insert into @Temp_Table select ProductNo,ProductTag from Products
end

-> I will remove the additional tempdb files and leave it to the default. Tempdb configuration after removing additional files,

Blog15_9.PNG

-> Using ostress utility, Iam executing the stored procedure “JB_Proc_orderDetails” with 25 connections and with 500 iterations for each connection again.

C:\Program Files\Microsoft Corporation\RMLUtils>ostress -E -SJB\IN2014 -dJB_Tempdb_Contention -Q”EXEC [dbo].[JB_Proc_orderDetails]” -n25 -r500

-> While the ostress utility runs, I am executing the below query in SQL server management studio to see if there are any waiting queries,

select * from master..sysprocesses where program_name like ‘%ostress%’

Blog15_10.PNG

-> The PFS contention is back and the workload this time took 182 seconds.

blog15_11

-> I am adding back the additional data files to see if it makes any difference,

USE [master]
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N’tempdev’, SIZE = 614400KB )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N’tempdev1′, FILENAME = N’C:\SQL\MSSQL12.IN2014\MSSQL\DATA\tempdev1.ndf’ , SIZE = 614400KB , FILEGROWTH = 10% )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N’tempdev2′, FILENAME = N’C:\SQL\MSSQL12.IN2014\MSSQL\DATA\tempdev2.ndf’ , SIZE = 614400KB , FILEGROWTH = 10% )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N’tempdev3′, FILENAME = N’C:\SQL\MSSQL12.IN2014\MSSQL\DATA\tempdev3.ndf’ , SIZE = 614400KB , FILEGROWTH = 10% )
GO

-> Starting the workload again using the ostress and checking if we have PFS or metadata contention,

Blog15_12.PNG

-> I dont see PFS or metadata contention anymore and the workload completed in 119 secs.

Blog15_13.PNG

-> But the stored procedure “JB_productDetails” is still slow when additional tempdb data files are added. This can be resolved by adding an index to sname column in table OrderData, but we cannot keep on adding indexes if we have different queries that uses different columns in order by clause. We can make the sort operator not to spill in tempdb  by providing more memory to the sort operator using optimize for hint or alter the resource Governor. But we cannot ensure that these changes will work for all stored procedure executions with different parameters.

-> We have a solution for this in SQL server 2014 and above without adding additional tempdb files or without making any schema changes.

-> We can use memory optimized table variables. Let me modify the database and stored procedure JB_Proc_orderDetails in such a way that it uses memory optimized table as below,

ALTER DATABASE JB_Tempdb_Contention ADD FILEGROUP JB_Tempdb_Contention_FG CONTAINS MEMORY_OPTIMIZED_DATA
go
ALTER DATABASE JB_Tempdb_Contention ADD FILE (name=’JB_Tempdb_Contention_FG1′, filename=’C:\SQL\MSSQL12.IN2014\MSSQL\DATA\JB_Tempdb_Contention_FG1′) TO FILEGROUP JB_Tempdb_Contention_FG
go
USE [JB_Tempdb_Contention]
GO
CREATE TYPE Temp_Table as TABLE (
ProductNo int NOT NULL, ProductTag int,
INDEX IX_ProductNo NONCLUSTERED (ProductNo)
) WITH (MEMORY_OPTIMIZED = ON)
USE [JB_Tempdb_Contention]
GO
Alter procedure [dbo].[JB_Proc_orderDetails]
as
begin
set nocount on
DECLARE @Temp_Table Temp_Table;
insert into @Temp_Table select ProductNo,ProductTag from Products
end

-> Once this is put in place, the workload completed in 12 seconds and i could not see major waits.

blog15_14

-> This ensures that the stored procedure “JB_productDetails” doesn’t have any impact and it runs normally.

-> We have different solutions to solve PFS, metdata contention. But the actual resolution will require thorough testing to see any changes to solve the PFS or meta data contention doesn’t adversely affect any other queries.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.