Performance Tuning · Uncategorized

SQL Server Execution Plan Operator – Table Scan

In my previous blog we discussed about the different format of SQL Server execution plan. Now in upcoming blogs we will discussed about the most commonly seen operators we see when execution plan generates. In this post I will start the easy one that is Table Scan operator. As with the name of operator Table… Continue reading SQL Server Execution Plan Operator – Table Scan

Development · Performance Tuning · Uncategorized

SQL Server Execution Plan formats

Execution plan helps in knowing how a query gets executed and troubleshooting a query which is performing poor. In SQL Server execution plan can be of 2 types: Estimated Execution plan Actual Execution plan Execution plan can be viewed in 3 different formats: Text format XML format Graphical format We can choose the format on… Continue reading SQL Server Execution Plan formats

Development · Uncategorized

Limitation of In-Memory OLTP in SQL Server 2016 – Merge not supported with memory tables as target

Hi friends, in this post we will discussed another limitation of In-Memory OLTP that in-memory tables as a Target in Merge statement is not supported. Let’s first create two table: In-memory and standard table. CREATE TABLE S_Merge ( [Name] varchar(32) not null PRIMARY KEY , [City] varchar(32) null, [State_Province] varchar(32) null, [LastModified] datetime not null,… Continue reading Limitation of In-Memory OLTP in SQL Server 2016 – Merge not supported with memory tables as target

Development · Uncategorized

Limitation of In-Memory OLTP in SQL Server 2016 – Seed and Increment value other than 1 not supported

Hi friends, in this blog we will discussed another limitation of In-Memory OLTP that seed and increment value other than 1 while defining identity property to a column is not supported. Let’s quickly see a demo on this – First we will create a table defining identity property with seed 1 and incremental value 1.… Continue reading Limitation of In-Memory OLTP in SQL Server 2016 – Seed and Increment value other than 1 not supported

Development · Uncategorized

Limitation of In-Memory OLTP in SQL Server 2016 – TRUNCATE table not supported

Hi friends, another limitation that we will discussed is TRUNCATE table command is not supported with memory optimized tables. Let’s see a demo on this. First we will create a memory optimized table. CREATE TABLE T2 ( [Name] varchar(32) not null PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 100000), [City] varchar(32) not null, [State_Province] varchar(32)… Continue reading Limitation of In-Memory OLTP in SQL Server 2016 – TRUNCATE table not supported

Development · Uncategorized

Limitation of In-Memory OLTP in SQL Server 2016 – LOB datatypes not supported

Hi friends, Wish you all a very Happy New Year. This is my first blog of 2016 and starting it with SQL 2016 J . Now a days I am exploring In-Memory features and changes done in SQL 2016 CTP versions. I have installed CTP 3.0 currently and comparing changes done in SQL 2016 from… Continue reading Limitation of In-Memory OLTP in SQL Server 2016 – LOB datatypes not supported