Why Bookmark Lookup is BAD?

-> As per article “https://technet.microsoft.com/en-us/library/ms180920(v=sql.105).aspx“. The Bookmark Lookup operator uses a bookmark (row ID or clustering key) to look up the corresponding row in the table or clustered index. The Argument column contains the bookmark label used to look up the row in the table or clustered index. The Argument column also contains the name of the table or clustered index in which the row is looked up. If the WITH PREFETCH clause appears in the Argument column, the query processor has determined that it is optimal to use asynchronous prefetching (read-ahead) when looking up bookmarks in the table or clustered index.

-> Create below database and objects,

create database JB_BookMark
go
use JB_BookMark
go
create table Table1(
Col1 int primary key identity(1,1),
Col2 int,
Col3 int,
Col4 varchar(100))
CREATE NONCLUSTERED INDEX IX_Table1 ON dbo.Table1(Col2)

-> Populate the table using below tsql query,

set nocount on
declare @i int=1
declare @j int=65
while (@i<=100000)
begin
if(@j=91)
begin
set @j=65
end
insert into Table1 values(@i,rand(@i*9)*1000,replicate(CHAR(@j),5))
set @i=@i+1
set @j=@j+1
end

-> Rebuild the indexes to remove any fragmentation,

USE [JB_BookMark]
GO
ALTER INDEX [PK__Table1__A259EE54D34C8103] ON [dbo].[Table1] REBUILD
GO
USE [JB_BookMark]
GO
ALTER INDEX [IX_Table1] ON [dbo].[Table1] REBUILD
GO

-> Execute the below query with actual execution plan enabled,

set statistics time on
set statistics IO on
select col1,col2,col3,col4 from table1
where col2 <=1
set statistics time off
set statistics IO off

Blog20_3.png

Blog20_4.PNG

-> The query cost is at 0.0065704. The operator “Index seek” returns 1 row(s).  The operator “Key Lookup” scans through the cluster index for each row that comes from the “Index seek” operator. In this case it is just 1 time. Logical reads done is around 4. The query overall took 49 MS to complete.

-> Execute the below query with actual execution plan enabled,

set statistics time on
set statistics IO on
select col1,col2,col3,col4 from table1
where col2 <=10
set statistics time off
set statistics IO off

Blog20_1.png

Blog20_2.PNG

-> The query cost is at 0.0327403. The operator “Index seek” returns 10 rows.  The operator “Key Lookup” scans through the cluster index for each row that comes from the “Index seek” operator. In this case it is 10 times. Logical reads done is around 22. The query overall took 62 MS to complete.

-> Execute the below query with actual execution plan enabled,

set statistics time on
set statistics IO on
select col1,col2,col3,col4 from table1
where col2 <=100
set statistics time off
set statistics IO off

Blog20_5.png

Blog20_6.PNG

-> The query cost is at 0.291569. The operator “Index seek” returns 100 rows.  The operator “Key Lookup” scans through the cluster index for each row that comes from the “Index seek” operator. In this case it is 100 times. Logical reads done is around 217. The query overall took 370 MS to complete.

-> It seems like whenever the execution plan for a query has key lookup, its performance degrades with the number of rows that it has to process. In our case, below is the final test result,

Blog20_7

-> We can create below index to avoid key lookup,

USE [JB_BookMark]
GO
CREATE NONCLUSTERED INDEX [IX_Table1_1]
ON [dbo].[Table1] ([Col2])
INCLUDE ([Col1],[Col3],[Col4])
GO

-> Execute the below query after the above index is created with actual execution plan enabled,

set statistics time on
set statistics IO on
select col1,col2,col3,col4 from table1
where col2 <=100
set statistics time off
set statistics IO off

Blog20_8.png

Blog20_9

-> Once we remove the key lookup, the query completes in 1 MS with fewer cost and logical reads.

-> So this means, we can create covering index for all queries that has key lookup? Creating covering index will remove the key lookup and the performance of the query will definitely increase, but it will also increase the space used by the index, database maintenance time, backup size. So it is wise to test things before taking a decision.

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.

Advertisements

Column data may be deleted when you update another variable-length column in a table of a database upgraded from SQL Server 2005

-> <Copied from https://support.microsoft.com/en-us/help/3120595/fix-column-data-may-be-deleted-when-you-update-another-variable-length-column-in-a-table-of-a-database-upgraded-from-sql-server-2005>

When you update a value in a variable-length column in a table of a database that was upgraded from Microsoft SQL Server 2005, data in another variable-length column in the same row is deleted.

This issue occurs in Microsoft SQL Server 2014 and Microsoft SQL Server 2012. The following scenario may expose a database to this issue. This list represents a sequence of events:

-> A database is created in SQL Server 2005.
-> In SQL Server 2005, you alter a table, and then you drop one or more variable-length columns at the end of the table definition.
-> You add new variable-length columns that have a similar data type and length of the dropped columns.
-> The database is upgraded from SQL Server 2005 to SQL Server 2014 or SQL Server 2012.
-> You later update the value of another variable-length column, and the updated data has to overflow to a new page.

-> </Copied from https://support.microsoft.com/en-us/help/3120595/fix-column-data-may-be-deleted-when-you-update-another-variable-length-column-in-a-table-of-a-database-upgraded-from-sql-server-2005>

-> Lets follow the below demo to reproduce the issue. Create the below database and objects in SQL server 2005.

use master
go
create database test_dropCol
go

use test_dropCol
go

CREATE TABLE [dbo].test(
       [Id] [int] IDENTITY(1,1) NOT NULL,
       col1 [nvarchar](18) NOT NULL,
col2 [datetime] NOT NULL,
col3 [varchar](max) NULL,
col4 [varchar](max) NULL,
col5 [varchar](max) NULL,
CONSTRAINT[PK_ID]PRIMARY KEYCLUSTERED
(
[Id] ASC
)
) ON [PRIMARY]

— Drop the column
BEGIN TRANSACTION
GO
ALTER TABLE dbo.test
DROP COLUMN col5
GO
COMMIT

— Looking at the leaf_offset
SELECT DISTINCT OBJECT_NAME(sp.[object_id]) AS TableWithDroppedCols
FROM sys.system_internals_partition_columns sipc1 WITH (NOLOCK)
INNER JOIN sys.system_internals_partition_columns sipc2 WITH (NOLOCK)
ON sipc1.[partition_id] = sipc2.[partition_id] AND
sipc1.leaf_offset = sipc2.leaf_offset AND
sipc1.leaf_offset< 0 AND
sipc1.is_dropped < sipc2.is_dropped
INNER JOIN sys.partitions sp WITH (NOLOCK) ON sipc1.[partition_id] = sp.[partition_id];
go
select sipc.partition_column_id, c.name,c.max_length, c.system_type_id,sipc.is_dropped, sipc.leaf_offset
from sys.system_internals_partition_columns sipc left outer join sys.partitions p on sipc.partition_id=p.partition_id
left outer join sys.columns c on c.object_id = p.object_id and c.column_id = sipc.partition_column_id
where p.object_id=object_id(‘test’)
go

 Blog19_1
— Creating another column
BEGIN TRANSACTION
GO

ALTER TABLE dbo.test ADD
col6 varchar(MAX) NULL
GO
COMMIT

— Looking at the leaf_offset again
SELECT DISTINCT OBJECT_NAME(sp.[object_id]) AS TableWithDroppedCols
FROM sys.system_internals_partition_columns sipc1 WITH (NOLOCK)
INNER JOIN sys.system_internals_partition_columns sipc2 WITH (NOLOCK)
ON sipc1.[partition_id] = sipc2.[partition_id] AND
sipc1.leaf_offset = sipc2.leaf_offset AND
sipc1.leaf_offset< 0 AND
sipc1.is_dropped < sipc2.is_dropped
INNER JOIN sys.partitions sp WITH (NOLOCK) ON sipc1.[partition_id] = sp.[partition_id];
go
select sipc.partition_column_id, c.name,c.max_length, c.system_type_id,sipc.is_dropped, sipc.leaf_offset
from sys.system_internals_partition_columns sipc left outer join sys.partitions p on sipc.partition_id=p.partition_id
left outer join sys.columns c on c.object_id = p.object_id and c.column_id = sipc.partition_column_id
where p.object_id=object_id(‘test’)
go
— I could see -4 leaf offset for the newly created column and also the deleted column. The select query listed in “More Information” in KB3120595 returns that table test now.

Blog19_3

-> Execute the below insert (we are also populating the last column which is having same offset as dropped column)


insert into test values(replicate (‘a’, 18),getdate(),replicate (‘a’, 8000),replicate (‘a’, 8000), replicate (‘a’, 8000))
go
select * from test
go

Blog19_2

2) Take backup of this database and restore in SQL server 2012 or SQL server 2014  (on the build that doesn’t have the fix mentioned in 3120595)

3) Now execute the below query to reproduce data loss issue in SQL server 2012 or SQL server 2014– (Note – we are updating column before the new column that has same offset as dropped column)

begin tran
select * from test
go

update test
set col4 = replicate(‘a’, 8000)
go

select * from test
go

Blog19_4

-> <Copied from https://support.microsoft.com/en-us/help/3120595/fix-column-data-may-be-deleted-when-you-update-another-variable-length-column-in-a-table-of-a-database-upgraded-from-sql-server-2005>

Workaround
Important Apply this workaround when you upgrade the database from SQL Server 2005 to SQL Server 2014 or SQL Server 2012 and before you let UPDATE statements be run in the upgraded database.
To work around this issue if you drop columns in a table, make sure that you do either of the following before you update or delete rows:

-> Rebuild the clustered index by using ALTER INDEX (…) REBUILD
-> Rebuild the heap by using ALTER TABLE (…) REBUILD

Resolution
Note This fix only prevents future occurrences of the issue.

Important If you are upgrading the database from SQL Server 2005 to SQL Server 2014 or SQL Server 2012, make sure that you refer to the “Workaround” section.

This issue was first fixed in the following cumulative update for SQL Server:

-> Cumulative Update 13 for SQL Server 2014
-> Cumulative Update 6 for SQL Server 2014 Service Pack 1
-> Cumulative Update 10 for SQL Server 2012 Service Pack 2
-> Cumulative Update 2 for SQL Server 2012 Service Pack 3

-> </Copied from https://support.microsoft.com/en-us/help/3120595/fix-column-data-may-be-deleted-when-you-update-another-variable-length-column-in-a-table-of-a-database-upgraded-from-sql-server-2005>

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.

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,

use JB_AdHoc
go
set nocount on
declare @i int = 1
declare @j int = 1000
print ‘set nocount on’
print ‘use JB_AdHoc’
print ‘go’
while (@i<=1000)
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.

Trace Flag 7471

-> Execute the below script to create the required database, objects for the demo.

create database JB_Update_Stats
go
use JB_Update_Stats
go
create table Table1(
sno int primary key identity (1,1),
sname1 char(800),
sname2 char(800),
sname3 char(800),
sname4 char(800),
sname5 char(800),
sname6 char(800))
CREATE NONCLUSTERED INDEX [Ix_Table1] ON [dbo].[Table1]([sname1] ASC)
CREATE NONCLUSTERED INDEX [Ix_Table2] ON [dbo].[Table1]([sname2] ASC)
CREATE NONCLUSTERED INDEX [Ix_Table3] ON [dbo].[Table1]([sname3] ASC)
CREATE NONCLUSTERED INDEX [Ix_Table4] ON [dbo].[Table1]([sname4] ASC)
CREATE NONCLUSTERED INDEX [Ix_Table5] ON [dbo].[Table1]([sname5] ASC)
CREATE NONCLUSTERED INDEX [Ix_Table6] ON [dbo].[Table1]([sname6] ASC)
GO
set nocount on
declare @i int = 1
while (@i<300000)
begin
insert into Table1 values (‘a’,’b’,’c’,’d’,’e’,’f’)
set @i = @i + 1
end

-> The query to populate “Table1” took close to 45 mins in my lab machine.

-> I am using SQL server 2014 with Service pack 2 in my lab machine.

-> I dont have any trace flags enabled on the SQL server instance.

blog14_1

-> Open a new query window, consider this as session1 and note down the SPID for this session.Execute the below query,

update statistics table1 ([Ix_Table1]) with fullscan

-> Open another query window, consider this as session2 and note down the SPID for this session. Execute the below query,

update statistics table1 ([Ix_Table2]) with fullscan

-> Open another query window and execute the below,

select * from master..sysprocesses where blocked>0
go
sp_lock
go
sp_lock
go

blog14_2

-> The output above reveals that SPID 57 is blocked by SPID 54 and the wait type indicates an Exclusive lock.

-> The sp_lock output provides more details. SPID 54 which is executing update statistics on “table1 ([Ix_Table1])” holds an exclusive lock on the table level on object id 245575913, which is “Table1”.

blog14_3

-> SPID 57 in the sp_lock output shows that it is waiting for an exclusive lock on the table level on object id 245575913, which is “Table1”.

-> The above behavior prevents concurrent updates of multiple statistics against the same table.

Effects of Trace flag 7471

-> I am turning trace flag 7471 now,

blog14_4

-> Open a new query window, consider this as session3 and note down the SPID for this session.Execute the below query,

update statistics table1 ([Ix_Table1]) with fullscan

-> Open another query window, consider this as session4 and note down the SPID for this session. Execute the below query,

update statistics table1 ([Ix_Table2]) with fullscan

-> Open another query window and execute the below,

select * from master..sysprocesses where blocked>0
go
sp_lock
go
sp_lock
go

blog14_5

-> After enabling trace flag 7471 we dont see any blockings. Concurrent updates of multiple statistics against the same table seems possible.

-> Trace flag 7471 can be added to the startup parameters to make sure it is active even after SQL server restarts.

-> Check https://support.microsoft.com/en-us/help/3156157/running-multiple-update-statistics-for-different-statistics-on-a-single-table-concurrently-is-available  for more details on trace flag 7471. As per the article this trace flag is first introduced in SQL server 2014 SP1 CU6 and SQL server 2016 CU6.

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.

Install and Configure SQLNexus

-> Install “Microsoft System CLR types for SQL server 2012” from https://www.microsoft.com/en-in/download/details.aspx?id=29065. Once you open the link, Click on “Install Instructions”. Search for “SQLSysClrTypes.msi” and Install the file  as per your platform.

-> Install “MICROSOFT REPORT VIEWER” from https://www.microsoft.com/en-us/download/details.aspx?id=35747

-> SQLNexus can be downloaded from https://sqlnexus.codeplex.com/.

-> Save and extract the file. In my case the file is “SQLNexus5.5.01.zip”.

-> Navigate to the extracted location and double click “sqlnexus.exe”.

blog11_3

1) Enter the SQL server instance name where you need to load the PSSDIAG output data and click connect. Usually SQL Nexus should be installed on a test server or on your desktop. It should never be installed on production server and used for loading the data.

2) Create a new database from SQL server management studio and select it in the “Current DB” combo box or select “<New Database>” from “Current DB” combo box and enter proper database name where the PSSDIAG output should be loaded.

3) Click on “Import” which is marked as 3 in the above screenshot and provide the PSSDIAG output folder location. For  example, f:\temp\pssdiag\output. Wait for the import to complete. Once the import completes you can follow the below,

blog11_4

Analyzing the data from SQL Nexus

1) Once the import completes. Click on “SQL Perf Main” and you will see all the available report on the Right side.

2) Click on the required reports and analyze further. You an also look at the PSSDIAG  database directly from the SQL server management studio.

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.