SQL 2016 – Tăng hiệu suất bảng tạm (temporary table) và biến bảng (table variable) sử dụng tính năng tối ưu bộ nhớ (memory optimization)

Posted on by mtoplistChuyên mục:Top List

Tăng hiệu suất bảng tạm (temporary table) và biến bảng (table variable)

sử dụng tính năng tối ưu bộ nhớ (memory optimization)

Nếu Database của bạn sử dụng các bảng tạm (temporary table), các biến bảng (table variable) hoặc các tham số có giá trị bảng (table-valued parameters hay còn được gọi là TVP), bạn hãy cân nhắc việc chuyển sang sử dụng tính năng “bảng tối ưu bộ nhớ” (memory-optimized table). Bạn đừng lo lắng về việc phải thay đổi quá nhiều phần code của bạn, việc thay đổi sẽ là rất ít.

Bài viết này sẽ đề cập tới:

  • Các kịch bản về sự chuyển đổi sang bảng In-memory.

  • Các kỹ thuật để thực hiện chuyển đổi sang bảng In-memory.

  • Các điều kiện bắt buộc phải thực hiện trước khi chuyển đổi sang bảng In-memory.

  • Một ví dụ để cho thấy các lợi ích của tính năng tối ưu bộ nhớ (memory optimization).

A. Khái niệm cơ bản của các biến bảng dùng tính năng tối ưu hóa bộ nhớ

Trước đây, khi bạn tạo một bảng tạm (

#Table

hoặc

##Table

) hoặc một biến bảng (

@Table

), các bảng này sẽ được database lưu trữ trên

Tempdb

datatabase.

Việc thao tác với các loại bảng tạm trên sẽ đều được thực hiện trên ổ đĩa nơi lưu trữ datafile của

Tempdb

.

Nếu database của bạn sử dụng nhiều bảng tạm thì việc Tempdb trở thành một nút cổ chai (bottle neck) về hiệu năng là không thể tránh khỏi.

Hiện tại, nếu bạn sử dụng bảng tạm với tính năng tối ưu bộ nhớ, các vấn đề nêu trên sẽ có thể được giải quyết.

Biến bảng loại này:

– Được lưu trữ hoàn toàn ở trên bộ nhớ memory, và không có thành phần nào được lưu ở trên ổ đĩa cứng.

– Không bị liên quan tới hoạt động IO của ổ đĩa.

– Không liên quan tới hoạt động của Tempdb do đó không bị tranh chấp bởi các hoạt động khác sử dụng Tempdb (sorting hoặc bảng tạm …).

– Có thể được sử dụng ở trong một thủ tục (Store procedure) như một tham số có giá trị bảng (TVP).

– Phải có ít nhất một chỉ mục (index), hoặc là Hash index hoặc là nonclustered index.

Các loại đối tượng:

In-Memory OLTP cung cấp các đối tượng sau đây có thể được sử dụng để tối ưu hóa bộ nhớ bảng tạm và các biến bảng:

– Bảng được tối ưu hóa bộ nhớ:

Với lựa chọn Durability = SCHEMA_ONLY

– Biến bảng được tối ưu hóa bộ nhớ:

Bắt buộc phải sử dụng 2 bước như sau:

  • CREATE

    TYPE

    my_type

    AS

    TABLE

    …;

  • DECLARE

    @mytablevariable

    my_type

    ;

B.1 Kịch bản 1: Thay thế bảng tạm toàn cục ##Table

Thay thế một bảng tạm toàn cục bằng một bảng được tối ưu hóa bằng lựa chọn SCHEMA_ONLY là khá đơn giản.

Sự thay đổi lớn nhất là bảng tạm được tạo ra ở thời gian triển khai code chứ không phải ở lúc dịch vụ được chạy.

Việc tạo bảng tối ưu hóa bộ nhớ mất nhiều thời gian hơn so với tạo bảng bằng phương pháp truyền thống do sự chênh lệch về thời gian biên dịch (compile time).

Giả sử ta có một bảng tạm toàn cục như sau:

CREATE

TABLE

##tempGlobalB

 

(

Column1

  

INT

  

NOT

NULL

,

 

Column2

  

NVARCHAR

(

4000

)

 

4000

);

Ta có thể thay thế biến bảng toàn cục trên bằng một bảng tối ưu hóa bộ nhớ với lựa chọn DURABILITY = SCHEMA_ONLY. Cụ thể như sau:

CREATE

TABLE

dbo

.

soGlobalB

(

Column1

  

INT

  

NOT

NULL

  

INDEX

ix1

NONCLUSTERED

,

 

Column2

  

NVARCHAR

(

4000

)

 

4000

)

WITH

 

       

(

MEMORY_OPTIMIZED

=

ON

,

 

       

DURABILITY

       

=

SCHEMA_ONLY

);

B.2 Các bước thực hiện

Để có thể chuyển đổi từ một bảng tạm toàn cục thành một bảng tối ưu hóa bộ nhớ SCHEMA_ONLY, ta thực hiện như sau:

  • Tạo bảng dbo.soGlobalB

    , bảng này sẽ giống với bảng truyền thống được lưu trữ trên ổ đĩa.

  • Trên các khối lệnh Transact-SQL của bạn, xóa các câu lệnh tạo bảng ##tempGlobalB

    . Điều quan trọng ở đây là: Chúng ta tạo bảng trong thời gian triển khai code chứ không phải tạo bảng lúc chạy dịch vụ, như vậy ta có thể giảm được thời gian biên dịch.

  • Trong T-SQL, bạn hãy thay thế tất cả ##tempGlobalB

    bằng

    dbo.soGlobalB

    .

C.1 Kịch bản 2: Thay thế bảng tạm theo phiên #table

Các công việc phải chỉnh sửa trên T-SQL để thay thế một bảng tạm theo phiên sẽ nhiều hơn so với kịch bản thay thế bảng tạm toàn cục. Tuy nhiên, thêm T-SQL không có nghĩa là chúng ta sẽ gặp khó khăn hơn trong việc chuyển đổi.

Giống với kịch bản chuyển đổi bảng temp toàn cục, sự thay đổi lớn nhất là tạo bảng trong thời gian triển khai thay vì trong thời gian chạy dịch vụ.

Giả sử bạn có một bảng tạm theo phiên như sau:

CREATE

TABLE

#tempSessionC

 

(

Column1

  

INT

  

NOT

NULL

,

 

Column2

  

NVARCHAR

(

4000

)

 

4000

);

C.2 Các bước thực hiện

Trước tiên, bạn cần phải tạo một hàm dạng table-value funtion để lọc các @@spid. Hàm này sẽ được sử dụng bởi tất cả các bảng SCHEMA_ONLY mà bạn chuyển đổi từ bảng tạm theo phiên.

CREATE

FUNCTION

dbo

.

fn_SpidFilter

(

@SpidFilter

smallint

)

RETURNS

TABLE

WITH

SCHEMABINDING

,

NATIVE_COMPILATION

AS

RETURN

SELECT

1

AS

fn_SpidFilter

WHERE

@SpidFilter

=

@@spid

;

Thứ hai, tạo ra bảng SCHEMA_ONLY, cùng với một chính sách an ninh trên bảng (SECURITY POLICY).

Lưu ý rằng: mỗi bảng dạng tối ưu hóa bộ nhớ cần phải có ít nhất một chỉ mục (Index).

CREATE

TABLE

dbo

.

soSessionC

(

Column1

    

INT

        

NOT

NULL,

Column2

    

NVARCHAR

(

4000

)

 

NULL,

4000

SpidFilter

 

SMALLINT

   

NOT

NULL

  

DEFAULT

(

@@spid

),

INDEX

ix_SpidFiler

NONCLUSTERED

(

SpidFilter

),

–INDEX ix_SpidFilter HASH

—    (SpidFilter) WITH (BUCKET_COUNT = 64),

CONSTRAINT

CHK_soSessionC_SpidFilter

CHECK

(

SpidFilter

=

@@spid

),

)

WITH

       

(

MEMORY_OPTIMIZED

=

ON

,

DURABILITY

=

SCHEMA_ONLY

);

GO

CREATE

SECURITY

POLICY

dbo

.

soSessionC_SpidFilter_Policy

ADD

FILTER

PREDICATE

dbo

.

fn_SpidFilter

(

SpidFilter

)

ON

dbo

.

soSessionC

WITH

(

STATE

=

ON

);

GO

Thứ ba, trong các khối T-SQL của bạn:

– Thay đổi tất cả các đoạn lệnh có liên quan tới bảng tạm theo phiên thành bảng tạm được tối ưu hóa bộ nhớ mới:

Old: #tempSessionC

New: dbo.soSessionC

– Thay thế mệnh đề ‘

CREATE

TABLE

#tempSessionC

‘ trong T-SQL thành ‘

DELETE

FROM

dbo

.

soSessionC

‘. Việc này sẽ đảm bảo rằng nội dung của bảng tạm hiện tại không phải là nội dung của phiên làm việc trước đó.

Lại một lần nữa chúng ta tạo trước bảng tạm tối ưu bộ nhớ trong thời gian triển khai code chứ không phải là trong thời gian chạy dịch vụ, nhằm giảm chi phí biên dịch khi tạo bảng.

– Xóa mệnh đề ‘

DROP

TABLE

#tempSessionC

‘ khỏi T-SQL của bạn. Bạn cũng có thể thêm một mệnh đề ‘

DELETE

 

FROM

 

dbo

.

soSessionC

‘ ở đây để làm giảm kích thước bộ nhớ phải lưu trữ (giảm được memory nhưng lại phải trả giá bằng CPU để thực hiện lệnh DELETE).

D. Kịch bản: Biến bảng có thể được tạo với lựa chọn MEMORY_OPTIMIZED=ON

Một biến bảng truyền thống khi được tạo sẽ được đại diện bằng một bảng trong cơ sở dữ liệu Tempdb. Để thực hiện thao tác với biến bảng nhanh hơn, bạn có thể sử dụng tối ưu bộ nhớ cho biến bảng.

Dưới đây là là T-SQL dùng để tạo một biến bảng truyền thống, phạm vi của nó kết thúc khi khối lệnh được chạy hết hoặc hết phiên.

DECLARE

@tvTableD

TABLE

(

Column1

INT

  

NOT

NULL,

Column2

CHAR

(

10

)

10

);

D.1 Các bước chuyển đổi để sử dụng biến bảng với lựa chọn MEMORY_OPTIMIZED=ON

– Một biến bảng tối ưu hóa bộ nhớ không được đặt trong Tempdb, nó hoàn toàn được đặt trên bộ nhớ memory, tốc độ của biến bảng loại này thường nhanh gấp 10 lần biến bảng thông thường. Việc chuyển đổi sang biến bảng tối ưu bộ nhớ được thực hiện chỉ trong một bước.

– Tạo một TYPE và sau đó tạo thêm ít nhất một INDEX đồng thời có sử dụng lựa chọn MEMORY_OPTIMIZED = ON. Cụ thể như sau:

CREATE

TYPE

dbo

.

typeTableD

AS

TABLE

(

Column1

INT

  

NOT

NULL

  

INDEX

ix1

,

Column2

CHAR

(

10

)

10

)

WITH

       

(

MEMORY_OPTIMIZED

=

ON

);

– Việc tạo TYPE phải được thực hiện từ trước khi khai báo biến bảng để đảm bảo nguyên tắc bảng tạm tối ưu bộ nhớ được tạo trong thời gian triển khai code chứ không phải trong thời gian chạy dịch vụ.

– Sau khi TYPE được tạo, bạn chỉ việc khai báo biến bảng để sử dụng:

SET

NOCOUNT

ON

;

DECLARE

@tvTableD

dbo

.

typeTableD

;

INSERT

INTO

@tvTableD

       

(

Column1

)

VALUES 

(

),

       

(

);

SELECT

*

FROM

@tvTableD

;

GO

– Kết thúc việc chuyển đổi.

E. Các điều kiện bắt buộc phải thực hiện trước khi chuyển đổi sang bảng tạm tối ưu bộ nhớ:

E.1 Phiên bản hoặc License của SQL Server phải được hỗ trợ tính năng In-memory

  • Bạn có thể kiểm tra bằng đoạn T-SQL sau:

IF

(

SERVERPROPERTY

(

N’IsXTPSupported’

)

=

0

)

OR

(

SERVERPROPERTY

(

N’IsXTPSupported’

)

IS

NULL)

BEGIN

   

PRINT

N’Error: Hệ thống của bạn không được hỗ trợ chức năng In-Memory OLTP.’

     

RETURN

END

IF

DB_ID

()

<

BEGIN

   

PRINT

N’Error: Chức năng In-Memory OLTP không được hỗ trợ trên database hệ thống. Hãy truy cập tới một Database của người dùng.’

     

RETURN

END

ELSE

BEGIN

     

PRINT

N’Database được hỗ trợ chức năng In-Memory OLTP’

     

RETURN

END

GO

E.2 Bạn cần chuẩn bị FILEGROUP cho SQL Server

  • Trên SQL Server, để có thể sử dụng tính năng tối ưu bộ nhớ, database của bạn bắt buộc phải có một FILEGROUP được tạo mới với lựa chọn MEMORY_OPTIMIZED_DATA (riêng với Azure SQL Database, bạn không cần thiết phải tạo FILEGROUP).

  • T-SQL dùng để tạo FILEGROUP và thêm một datafile vào FILEGROUP

— Tạo Filegroup dành cho tính năng memory-optimized

ALTER

DATABASE

LabDB

     

ADD

FILEGROUP

FgMemOptimized

                 

CONTAINS

MEMORY_OPTIMIZED_DATA

;

GO

— Add datafile vào Filegroup FgMemOptimized

ALTER

DATABASE

LabDB

     

ADD

FILE

     

(

           

NAME

=

N’FileMemOptimized’

,

           

FILENAME

=

N’E:\SQLData\LabDB\FileMemOptimized_01′

     

)

     

TO

FILEGROUP

FgMemOptimized

;

GO

F. Một ví dụ để cho thấy các lợi ích của tính năng tối ưu bộ nhớ (memory optimization)

— Tạo TYPE kiểu truyền thống trên Database Tempdb

CREATE

TYPE

dbo

.

test_disk

AS

TABLE

(

Column_1

INT

NOT

NULL,

Column_2

CHAR

(

10

)

)

GO

— Tạo TYPE với tham số ‘MEMORY_OPTIMIZED=ON’

CREATE

TYPE

dbo

.

test_memory

AS

TABLE

(

Column_1

INT

NOT

NULL

INDEX

IX_Column_1

,

Column_2

CHAR

(

10

)

)

WITH

(

MEMORY_OPTIMIZED

=

ON

)

GO

— Chạy các đoạn lệnh sau để lấy kết quả và thực hiện so sánh

SET

NOCOUNT

ON

GO

DECLARE

@tv dbo

.

test_disk

INSERT

  @tv

VALUES 

(

1

,

‘n’

)

INSERT

  @tv

VALUES 

(

2

,

‘m’

)

DELETE

 

FROM

@tv

GO

10000

DECLARE

@tv dbo

.

test_memory

INSERT

  @tv

VALUES 

(

1

,

‘n’

)

INSERT

  @tv

VALUES 

(

2

,

‘m’

)

DELETE

 

FROM

@tv

GO

10000

  • Chú ý: Mệnh đề ‘

    GO

     10000

    ‘ sẽ lặp lại việc thực hiện khối lệnh T-SQL 10000 lần. Các bạn có thể điều chỉnh con số 10000 và chạy lại khối lệnh

Sau đây là hình ảnh về kết quả:

Hình 1: Khi sử dụng bảng tạm trên ổ đĩa (Tempdb)

Hình 2: Khi sử dụng bảng tạm tối ưu hóa bộ nhớ (Memory)

Khi sử dụng bảng tạm trên ổ đĩa, ta có thể thấy IO ổ đĩa tăng lên rất nhiều so với khi sử dụng bảng tạm tối ưu hóa bộ nhớ.

Thời gian thực thi đoạn lệnh cũng đã được cải thiện một cách rõ rệt (1:23 so với 0:27).

Chốt lại bài viết: Các biến bảng sử dụng tối ưu hóa bộ nhớ và bảng SCHEMA_ONLY cần phải được sử dụng cho phù hợp với lượng bộ nhớ (Memory) của hệ thống. Các bài viết sau đây trên MSDN và Azure.com có cung cấp một cách chi tiết phương án giám sát lượng bộ nhớ được sử dụng:

Lời khuyên ở đây là: nếu các bảng tạm và biến bảng không quá lớn hoặc bạn có thể đảm bảo đủ bộ nhớ của hệ thống, không có lý do gì để bạn không sử dụng tính năng mới này của SQL Server. Thời gian và công sức để chuyển đổi là không nhiều so với lợi ích mà nó đem lại cho Database của bạn.

mtoplist