I still remember the day when I was interviewed for my job with Microsoft. It was kind of a discussion about SQL Sever internals rather than interview. Of course, we had discussion about SQL Server Performance Tuning as well. Interviewer asked – “If there is a query taking very long time to execute, what would you do?” I tried few answers like blocking, resource bottleneck and also said that we can look at the execution plan. I was aware of few bad operators in execution plan and I explained those. Anyways, after 2 hours of discussion, the interviewer was satisfied that I know SQL Server features. I also got feedback that more than current knowledge, I have passion to learn, help someone and share my knowledge. End result – I was hired! After joining Microsoft SQL Support team, I am learning since day 1 and learning and sharing never stopped for me. Performance tuning has been my favorite and I would still love to see execution plans to learn more about query execution.

In this blog of A-Z of In-Memory OLTP Series, we would talk about execution plan related changes with respect to In-Memory OLTP.

Limitation on Natively Compiled Stored Procedure

If we try to get execution plan of any natively complied stored procedure via SSMS using “Include Actual Execution Plan” and executing it; it won’t work. There is no plan in procedure cache for natively compiled stored procedures. Even if we try set statistics profile on, it won’t work.

Only way to get query plan for natively compiled procedure is to get estimated plan. That can be done using TSQL “set showplan_xml on” statement or via SSMS “Display Estimated Execution Plan”. This restriction is not applicable for TSQL statements touching in-memory tables and we can get actual plan as well.

How to Identify which In-Memory Index is being used?

We have earlier created table called MyFirstMemporyOptimizedTable with Primary Key (with hash index) and another Hash Index. There is another type of non-clustered index in-memory world called range index, which we would cover later in the series. We would create MySecondMemporyOptimizedTable with range index and I’ve highlighted below.

CREATE TABLE [dbo].[MyFirstMemporyOptimizedTable]
(
    [iID] [int] NOT NULL,
    [vFName] [varchar](20) COLLATE Latin1_General_100_BIN2 NOT NULL,
    [vLName] [varchar](20) COLLATE Latin1_General_100_BIN2 NOT NULL

INDEX [imo_idx_vFname] NONCLUSTERED HASH 
(
    [vFName]
)WITH ( BUCKET_COUNT = 1048576),
CONSTRAINT [imo_pk_iID] PRIMARY KEY NONCLUSTERED HASH 
(
    [iID]
)WITH ( BUCKET_COUNT = 1048576)
)WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA )

GO

CREATE TABLE [dbo].[MySecondMemporyOptimizedTable]
(
    [iID] [int] NOT NULL,
    [vFName] [varchar](20) COLLATE Latin1_General_100_BIN2 NOT NULL,
    [vLName] [varchar](20) COLLATE Latin1_General_100_BIN2 NOT NULL

INDEX [imo2_idx_vFname] NONCLUSTERED  
(
    [vFName]
),
CONSTRAINT [imo2_pk_iID] PRIMARY KEY NONCLUSTERED HASH 
(
    [iID]
)WITH ( BUCKET_COUNT = 1048576)
)WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA )

Hash Index

Below query is going to use Hash index

select count(*) 
from MyFirstMemporyOptimizedTable
where iID = 0

And we would see Index Seen with “NonClusteredHash” keyword, which denoted hash index being used.

If we look at “storage” for the “Index Seek” operator, it would be “MemoryOptimized”

Range Index

Here is a query for Range Index for second table

select count(*) 
from MySecondMemporyOptimizedTable
where vFName= 'Balmukund'

Estimated I/O Cost is zero

We can also notice that Estimated I/O Cost is zero. Of course it would be zero as table is residing in memory.

NoParallelForMemoryOptimizedTables is a reason for non-parallelized plan

While looking at XML plan of the query, I noticed something new and I have drawn a box.

NonParallelPlanReason="NoParallelForMemoryOptimizedTables" This would mean that in-memory table operations would not go for parallelism.

Hope you have learned something new today.