Installation of the Cumulative Update is similar to the installation of a Service Pack.
You will see even more benefit when using memory-optimized table variables in concurrency scenarios, where you have many connections that use these table variables at the same time.
To improve performance of your temp tables, replace traditional global (##SET NOCOUNT ON; GO CREATE PROCEDURE sp_temp AS BEGIN IF NOT EXISTS (SELECT * FROM objects WHERE name=N'##temp1') CREATE TABLE ##temp1 ( c1 INT NOT NULL , c2 NVARCHAR(4000) ); BEGIN TRAN DECLARE @i INT = 0; WHILE @i Execution of this procedure using a traditional ##temp table takes 0 seconds on my machine, according to Management Studio, so it’s pretty quick. Let’s execute this procedure using 100 concurrent connections, with each connection executing the procedure 1,000 times, for a total of 100,000 executions of the procedure.
Workloads that intensively use table-valued parameters (TVPs), table variables and temp tables can cause contention on things like metadata and page allocation, and result in a lot of IO activity that you would rather avoid.
What if TVPs and temp tables could live just in memory, in the memory space of the user database? Memory-optimized table types and SCHEMA_ONLY memory-optimized tables can be used to replace traditional table types and traditional temp tables, bypassing tempdb completely, and providing additional performance improvements through memory-optimized data structures and data access methods.
I’m using the following ostress command, which connects to the default instance, database Adventure Works2016CTP3: CREATE TABLE dbo.temp1 (c1 INT NOT NULL INDEX ix_1 , c2 NVARCHAR(4000)) WITH (MEMORY_OPTIMIZED=ON, DURABILITY=SCHEMA_ONLY); GO DROP PROCEDURE IF EXISTS sp_temp GO CREATE PROCEDURE sp_temp AS BEGIN BEGIN TRAN DECLARE @i INT = 0; WHILE @i If you read the previous section in this blog, you may think it looks familiar.
Execution of this procedure actually takes a little longer than the procedure with the global temp table, as a new temp table needs to be created with each execution.
At the bottom of the post there are the prerequisites for using memory-optimized tables and table types, as well as further reading, for those of you not as familiar with In-Memory OLTP.
It is easy to start using memory-optimized table variables and table-valued parameters: just replace your table types with memory-optimized table types.
The memory-optimized table variable and global temp table scenarios are support in SQL Server 2014, although parallel plans are not supported in 2014, so you would not see perf benefits for large table variables or large temp tables in SQL Server 2014.
SQL 2016 and Azure DB do support parallel plans with memory-optimized tables and table variables, so no concerns there.
In the remainder of this post you see how you can easily replace traditional tempdb-based table variables and temp tables with memory-optimized table variables and tables.