Posted by

Posted on

March 28, 2024

Posted under

Azure Managed Instance, Azure SQL Database, Performance

Comments

Elevating SQL Server Performance with In-Memory OLTP Table Variables

In the realm of database management, optimizing performance while minimizing resource contention is a perennial challenge. One of the critical areas where contention can be notably reduced is in the utilization of temporary storage mechanisms like tempdb. SQL Server’s In-Memory OLTP feature offers a compelling solution to this challenge, particularly through the use of in-memory optimized table variables. This post explores how leveraging In-Memory OLTP table variables can significantly improve performance by alleviating tempdb allocation contention, especially when compared to traditional table variables, temporary tables, or common table expressions (CTEs).

Understanding tempdb Contention

Before diving into the solution, it’s crucial to understand the problem. The tempdb system database in SQL Server is a shared resource used for various temporary storage activities, including user-created temporary tables, temporary stored procedures, table variables, and CTEs. However, heavy tempdb usage, particularly in high-concurrency environments, can lead to contention issues, impacting overall database performance.

Enter In-Memory OLTP Table Variables

In-Memory OLTP, introduced in SQL Server 2014, revolutionized how data is stored and accessed by storing designated tables and table variables in memory rather than on disk. This feature not only speeds up data access but also significantly reduces contention by bypassing traditional disk-based storage mechanisms.

Defining an In-Memory OLTP Table Variable

Unlike regular table variables, an in-memory OLTP table variable is defined as a user-defined table type with the MEMORY_OPTIMIZED = ON option. Here’s how to create one:

CREATE TYPE JB_InMem_TableType AS TABLE (
    col1 INT,
    col2 DATETIME,
    col3 VARCHAR(255),
    INDEX IX_JB_InMem_TableType_Col1 NONCLUSTERED (Col1)
) WITH (MEMORY_OPTIMIZED = ON);
GO

Utilizing In-Memory OLTP Table Variables in Stored Procedures

In-memory OLTP table variables can be seamlessly integrated into stored procedures. Here’s a simple example:

CREATE PROCEDURE sp_proc1
    @Col1 INT
AS
BEGIN 
    DECLARE @jb_table1 JB_InMem_TableType;
    INSERT INTO @jb_table1 
    SELECT col1, col2, col3 FROM Table1
    SELECT col1, col2, col3 FROM @jb_table1 WHERE col1 = @Col1;
END
GO

In this example, @jb_table1 is populated from Table1 and then queried, all while residing entirely in memory.

Benefits Over Traditional Methods

The use of in-memory OLTP table variables offers several advantages over traditional table variables, temporary tables, or CTEs:

Reduced tempdb Contention: Since in-memory objects do not rely on tempdb, they avoid contributing to tempdb contention, a common bottleneck in high-concurrency scenarios.
Performance Improvement: Accessing data in memory is significantly faster than disk-based access, leading to improved query performance.
Transactional Consistency: In-memory OLTP supports fully ACID-compliant transactions, ensuring data integrity without compromising on performance.

Conclusion

In-memory OLTP table variables represent a powerful tool in the SQL Server performance tuning arsenal. By leveraging in-memory storage, developers and DBAs can achieve significant performance improvements and reduce contention issues associated with tempdb. Whether you’re building high-performance applications or optimizing existing database workloads, the use of in-memory OLTP table variables is certainly worth considering.

For more tutorials and tips on SQL Server, including performance tuning and database management, be sure to check out our JBSWiki YouTube channel.

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.

Posted by

Vivek Janakiraman

Posted on

August 17, 2022

Posted under

Performance

Comments

SQL SERVER – Index Hints – Force Index – Query Hints

-> Lets create required objects for this test,

create table Table1(
Col1 int primary key identity(1,1),
Col2 varchar(255),
Col3 datetime,
Col4 char(10))

create table Table2(
Col1 int primary key identity(1,1),
Col2 varchar(255),
Col3 datetime,
Col4 char(10))

create index ix_test on Table1(Col2) INCLUDE(Col4)
create index ix_test on Table2(Col2) INCLUDE(Col4)

set nocount on


insert into Table1 values ('test',getdate(),'test1')
go 100000

insert into Table2 values ('test',getdate(),'test1')
go 100000

-> Once above objects are created. Lets execute below query,

select Col1,Col2,Col3,Col4 from Table1 where Col2='Test'

-> We are getting a Clustered Index Scan. Lets check Table1 properties and see what indexes we have,

-> We have a clustered index on Col1 that is created as part of the clustered index and then a non-clustered index on column Col2.

-> Below query we executed has a predicate on Col2, so not sure why the optimizer selected a clustered index scan instead of an Index seek using Index ix_test. Let us try forcing this index now,

select Col1,Col2,Col3,Col4 from Table1 with (index(ix_test)) where Col2='Test'

-> We see an Index seek using Index ix_test and then a key lookup after we forced the Index Ix_test. It seems like optimizer went for a clustered index scan instead of an Index seek + Key lookup as the plan with Clustered Index scan is comparatively cheap than an Index seek + Key lookup.

-> Lets verify this by running the query with and without the hint. As expected, query without hint has overall cost of just 3% and the query with the index hint is close to 97%.

-> It is always good to go with the plans created by optimizer rather than adding hints. Adding hints can help when there is an optimizer timeout. Since this article is about forcing an index, lets look at another method using which we can force an index without worrying much on the performance.

-> Execute below query and get the index id of Ix_Test on Table1.



select object_name(object_id) TableName,name,index_id,type,type_desc from sys.indexes where object_id=OBJECT_ID('Table1')

-> In my case Index id 3 is Ix_test. We can use below query to force the index Ix_Test,

select Col1,Col2,Col3,Col4 from Table1 with (index(3)) where Col2='Test'

-> Lets see how can we force an index when join between tables are performed. Lets execute below query,

select a.Col1,a.Col2,a.Col3,b.col4 
from Table1 a 
INNER JOIN Table1 b on a.col1=b.col1  
where a.Col2='Test'

-> We see an cluster Index scan for both tables. Now lets force index ix_test for both tables using below query,


select a.Col1,a.Col2,a.Col3,b.col4 
from Table1 a with (index(ix_test))
INNER JOIN Table1 b with (index(ix_test)) on a.col1=b.col1  
where a.Col2='Test'

Thank You,
Vivek Janakiraman

Posted by

Vivek Janakiraman

Posted on

August 17, 2022

Posted under

Performance

Comments

Forcing a query to perform Table Scan on a heap table

-> Create below table and supporting index,

create table Table1(
Col1 int,
Col2 varchar(255),
Col3 datetime,
Col4 char(10)
)

create index ix_test on Table1(Col2) INCLUDE(Col1,Col3,Col4)

-> Lets check the table properties. There is no clustered index and Table Table1 is a heap.

-> Insert few rows,

insert into Table1 values (1,'test',getdate(),'test1')
go 1000

-> Lets execute below query with execution plan enabled,

select Col1,Col2,Col3,Col4 from Table1 where Col2='Test'

-> Lets execute below query and see if we can get a table scan,

select Col1,Col2,Col3,Col4 from Table1 with (FORCESCAN) where Col2='Test'

-> From above screenshot, we see that we have an index scan. But we were looking for a table scan.

-> Lets us check the sysindexes system table using below query and see what we have,

select object_name(object_id) TableName,name,index_id,type,type_desc from sys.indexes where object_id=OBJECT_ID('Table1')

-> Lets force Index_id 0 and see if we can get a table scan,

select Col1,Col2,Col3,Col4 from Table1 with (index(0)) where Col2='Test'

-> There you go!!! we have a table scan.

Thank You,
Vivek Janakiraman

JBs Wiki

Category Archives: Performance

Elevating SQL Server Performance with In-Memory OLTP Table Variables

Understanding tempdb Contention

Enter In-Memory OLTP Table Variables

Defining an In-Memory OLTP Table Variable

Utilizing In-Memory OLTP Table Variables in Stored Procedures

Benefits Over Traditional Methods

Conclusion

SQL SERVER – Index Hints – Force Index – Query Hints

Forcing a query to perform Table Scan on a heap table