[SQL Server]Fascinating Natively compiled stored procedures

RiCo 技術農場
Jul 30, 2017 · 3 min read

In recently, I tried to enhance performance of some operator api on our company system.

I found some operator api so fast on our system.

All queries using index seeks and no more than 3ms.

This post , I will show you how to improve its query performance.

We had known natively compiled stored procedures are TSQL stored procedures compiled to native code

that access memory table and produce DLLs.

Natively compiled stored procedures allow efficient query execution, faster data access

and business logic than the disk base(traditional) TSQL.

So I think maybe I can use natively compiled stored procedures to improve query performance.

Take a look at my performance test as below

Note: As I see it, the migration Natively compiled stored procedures wasn’t easy on the real world.

— Disk Base

CREATE TABLE myEmail_disk
(
c1 Uniqueidentifier DEFAULT newsequentialid() NOT NULL PRIMARY KEY NONCLUSTERED,
c2 int NOT NULL index cidx clustered(c2)with(data_compression=page) ,
c3 VARCHAR(20) NOT NULL,
c4 nvarchar(100) NOT NULL
)
GO
create index idx_c3 on dbo.myEmail_disk(c3)
include(c4)
with(data_compression=page)
set nocount on
declare @i int =0
while(@i<10000)
begin
insert into myEmail_disk
values(default,@i,'ricoisme'+cast(@i as varchar(10)),N'rico'+cast(@i as varchar(10))+@ricostudio.co">N'@ricostudio.co');
set @i+=1;
end
create proc usp_getemail(@c3 VARCHAR(20))
as
begin
set nocount on
select c3,c4 from dbo.myEmail_disk where c3=@c3
end
exec usp_getemail 'ricoisme2'
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Table 'myEmail_disk'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0

You saw numbers of logical reads because I accessed disk base.

Alright , let’s jump to In-Memory

— In-Memory

CREATE TABLE myEmail_mem
(
c1 Uniqueidentifier DEFAULT newsequentialid() NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 10000),
c2 int NOT NULL ,
c3 VARCHAR(20) NOT NULL INDEX ix_c3 NONCLUSTERED,
c4 nvarchar(100) NOT NULL
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)
GO
INSERT INTO myEmail_mem
( c1 ,
c2 ,
c3 ,
c4
)
SELECT c1 ,
c2 ,
c3 ,
c4
FROM myEmail_disk
CREATE PROCEDURE usp_getemail_nac (@c3 VARCHAR(20)='')
WITH NATIVE_COMPILATION, SCHEMABINDING,execute as owner
AS
BEGIN ATOMIC
WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'english')
select c3,c4 from dbo.myEmail_mem where c3=@c3
END
exec usp_getemail_nac 'ricoisme2'

No any logical reads when I accessed memory table.

Let’s start SQLQueryStress tools for multiple threads(I make concurrently running 10 threads and each thread running the runnable 100 times).

You will see the same result(no logical reads IO and faster than old stored procedure).

exec usp_getemail 'ricoisme2'
exec usp_getemail_nac 'ricoisme2'

Finally, Enjoy Performance Improvement of In-Memory OLTP on the SQL 2016 and Good luck. :)

參考

Natively Compiled Stored Procedures

Supported Features for Natively Compiled T-SQL Modules

Creating Natively Compiled Stored Procedures

Query and Stored Procedure Optimization

Native Compilation of Tables and Stored Procedures

Migration Issues for Natively Compiled Stored Procedures

CREATE INDEX (Transact-SQL)

Guidelines for Using Indexes on Memory-Optimized Tables

Indexes for Memory-Optimized Tables

Originally published at dotblogs.com.tw on July 30, 2017.

RiCo’s Note

C#/SQL Server/Azure/NetCore/Angular/Kafka….

RiCo 技術農場

Written by

分享工作上實戰經驗,如SQL Server,NetCore,C#,WEBApi,Kafka,Azure…等,Architect,Software Engineer, Technical Manger,Writer and Speaker

RiCo’s Note

C#/SQL Server/Azure/NetCore/Angular/Kafka….

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade