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)

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) 

);

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) 

) 

    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) 

);

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,

    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)

);

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)

)

    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  ( 1 ),

        ( 2 );

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() < 5

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:

• https://msdn.microsoft.com/library/dn465869.aspx
• https://azure.microsoft.com/documentation/articles/sql-database-in-memory-oltp-monitoring/

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.