Setting Auto Create Statistics dùng để làm gì

Định nghĩa : Setting Auto create statistics dùng để điều khiển khả năng tạo Statistics tự đông, gồm có 2 giá trị sau :

Setting Auto Create Statistics set ON

• Query Optimizer tạo statistics cho những cột đơn nằm trong query predicates khi nó biên dịch câu truy vấn, một lưu ý là với những cột là cột đầu tiên trong những đối tượng statistics đang tồn tại thì Query Optimizer sẽ sử dụng chúng thay vì tạo thêm statistics
• Mặc định Auto Create Statistics được set ON và microsoft cũng khuyên người dùng bật On cho setting này

Setting Auto Create Statistics set OFF  

• Query Optimizer không tạo statistics cho cột đơn nằm trong query predicates khi nó biên dịch câu truy vấn

Để hiểu hơn ý nghĩa của setting trên, chúng ta cùng qua ví dụ cụ thể sau

trước hết ta sẽ tạo 1 database và các bảng liên quan để có thể tạo môi trường cho việc khảo sát bằng scripts sau đây : 

CREATE DATABASE TestAutoCreateStats 
GO

USE TestAutoCreateStats
GO

CREATE TABLE TestTableStat
(
	Id INT
	,Name VARCHAR(8000)
)
GO

INSERT INTO TestTableStat (Id, NAME)
VALUES (1, 'Name 1'), (2, 'Name 2'), (3, 'Name 3')

Giờ chúng ta bắt đầu khảo sát với trường hợp Auto Create Statistics = OFF

Để điều chỉnh Setting này bạn có 2 cách :

• Cách 1 : dùng TSQL

ALTER DATABASE TestAutoCreateStats
SET AUTO_CREATE_STATISTICS  OFF
GO

• Cách 2: Update trực tiếp bằng giao diện SSMS

Bạn chọn database TestAutoCreateStats sau đó click vào mục properties, tiếp theo bạn chọn vào mục Options, tiếp đến chúng ta xem ở mục Automatic sau đó tìm đến Auto Create Statistics chuyển giá trị từ True (giá trị mặc định) sang False, sau đó ấn OK như hình bên dưới

quá trình setting đã xong

để kiểm tra rõ điều gì xảy ra khi setting này OFF mình sẽ sử dụng những Extended Events sau đây : 

• auto_stats :  mỗi khi statistics được tạo, update hoặc load cho optimizer sử dụng thì sẽ xuất hiện event này
• query_post_compilation_showplan : event này xảy ra khi statement đã được biên dịch, dựa vào event này chúng ta có thể dễ dàng biết được câu query nào được biên dịch và câu query nào thực chất chỉ sử dụng lại Query Plan đã được biên dịch lần trước
• sql_statement_starting : được phát sinh khi bắt đầu thực thi một câu truy vấn
• sql_statement_completed : được phát sinh khi câu truy vấn đã hoàn thành

đây là scripts mà mình sử dụng :  

CREATE EVENT SESSION [AutoCreateStats] ON SERVER 
ADD EVENT sqlserver.auto_stats(SET collect_database_name=(0)
    ACTION(sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.transaction_id,sqlserver.tsql_frame,sqlserver.tsql_stack)),
ADD EVENT sqlserver.query_post_compilation_showplan(SET collect_database_name=(1)
    ACTION(sqlserver.database_id)),
ADD EVENT sqlserver.sql_statement_completed(SET collect_statement=(1)
    ACTION(sqlserver.sql_text)),
ADD EVENT sqlserver.sql_statement_starting(SET collect_statement=(1)
    ACTION(sqlserver.sql_text))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO

Giờ chúng ta xem Statistics hiện tại có trong bảng ‘TestTableStat’

SELECT *
FROM sys.stats 
WHERE object_id = OBJECT_ID('TestTableStat')

hiện tại chưa có bất kỳ Statistics nào

giờ ta sẽ chạy câu query đầu tiên : 

SELECT *
FROM TestTableStat
WHERE Id = 1

Những gì Extended Events bắt được 

có 3 event xuất hiện

• Dòng số 1: cho biết câu truy vấn đã được biên dịch, khi ấn vào Query Plan tab ở phần kết quả ta có thể xem được Query Plan đã được biên dịch

ở đây xuất hiện một cảnh báo cho biết cột Id không có statistics và Query optimizer đang thực hiện tối ưu câu truy vấn mà không có Statistics trên cột Id, một điểm cần lưu ý ở đây là thuộc tính Estimated Number of Rows for All Executions thể hiện thông tin ước lượng số dòng mà toán tử Table Scan trả ra là 1.73, khác với số dòng thực sự trả ra là 1 ứng với Id = 1

• Dòng (2) : Sau khi Query Optimizer đã biên dịch xong Query Plan nó bắt đầu thực thi
• Dòng (3) : báo cho ta biết câu truy vấn đã thực thi xong

sau cùng chúng ta kiểm tra lại Statistics của bảng TestTableStat lần nữa bằng việc chạy lại câu lệnh đầu tiên :  

SELECT *
FROM sys.stats 
WHERE object_id = OBJECT_ID('TestTableStat')

ở đây không có thống kê nào được tạo ra vì setting AUTO_CREATE_STATISTICS  đang OFF

chúng ta tiếp tục khảo sát với AUTO_CREATE_STATISTICS  = ON bằng cách chạy câu lệnh sau:

ALTER DATABASE TestAutoCreateStats
SET AUTO_CREATE_STATISTICS ON
GO

Chúng ta lặp lại khảo sát ban đầu bằng việc chạy câu lệnh

SELECT *
FROM TestTableStat
WHERE Id = 1

kết quả ở extend event thu được

• Dòng số 1, khi click vào query plan tab

ấn vào khối Stream Aggregate, ta sẽ thấy

[Expr1003] = Scalar Operator(STATMAN([TestAutoCreateStats].[dbo].[TestTableStat].[Id]))

để tạo được thống kê, sql server cần gọi hàm statman, trước khi gọi hàm statman, data cần được sort, nên ở đây ta thấy xuất hiện toán tử Sort, trong thực tế để quá trình tạo statistics tựu động  diễn ra nhanh thì tùy vào số dòng cần sort, nếu số dòng quá lớn thì sql server chỉ lấy một số phần trăm dòng nhất định của cột cần sort  ở bảng nguồn (như trên hình là bảng TestTableStats) để tạo thống kê chứ không lấy tất cả các dòng

–  Dòng thứ 2 : xuất hiện event : auto_stats với statistics_list là Created Id, dòng này cho biết thống kê trên cột Id đã được tạo ra

– Dòng thứ 3 : vẫn là event : auto_stats nhưng ở mục statistics_list lại hiển thị khác đi

Loading without updating: TestTableStat._WA_Sys_00000001_22AA2996

đúng như ý nghĩa đã được thể hiện, trước khi thực hiện biên dịch, Query optimizer cần load thống kê để nó có thể sử dụng, những thống kê này mới được tạo ra nên ko cần update vì vậy có thêm “ without updating” ở phía sau, và ở đây ta cũng biết thêm thông tin tên statistics vừa được tạo ra có tên là _WA_Sys_00000001_22AA2996, tất cả thống kê được tạo ra tự động đều có tên bắt đầu bằng _WA_Sys, WA là viết tắt cho Washington, một bang của mỹ nơi mà nhóm phát triển sql server làm việc

– Dòng thứ 4: sau khi Query Optimizer đã thực hiện tối ưu dựa trên những thông tin đã thu thập được, ở đây ta có Query Plan sau cùng, để xem ta xem ấn vào Query Plan tab

không có bất kỳ cảnh báo nào được đề cập và ở đây ta cũng có một lưu ý Estimated Number Of Rows for All Execution có giá trị bằng 1, giá này đúng bằng số dòng trả ra thực tế

Và 2 dòng cuối cùng (6) và (7) lần lượt là cho biết câu query bắt đầu thực thi, và câu query đã được thực thi xong

Vậy là ta đã hiểu khi chức năng của Setting Auto Create Statistics, vậy câu hỏi được đặt ra là chúng ta cần bật ON hay OFF ?, như khảo sát trên ta thấy Query Optimizer luôn cần những Statistics cần thiết để tạo ra  Query Plan tối ưu nhất vì vậy Setting này luôn được đề xuất là bật ON

Tổng kết :

Setting Auto Create Statistics dùng để tạo statistics cho những cột dùng ở Query Predicate mà chưa tồn tại statistics cho cột này trước đó và trong setting này được khuyến khích bật vì Query Optimizer cần thông tin Statistics để tạo ra câu query tối ưu nhất