February 20, 2017

This is one of the blog post in a series of posts about optimizing SQL Server queries. A list of blog posts about query tuning can be found here – https://jbswiki.com/2021/04/15/sql-server-query-optimization-introduction/

-> 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.

-> 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%’

-> 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.

-> 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,

-> 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,

->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

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

-> 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,

-> 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,

-> 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%’

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

-> 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,

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

-> 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.

-> 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.

JBs Wiki

Fall in love with SQL

Daily Archives: February 20, 2017

Tempdb contention

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

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

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

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

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

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

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

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

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

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

Tempdb contention

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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