KIẾN TRÚC SQL SERVER

Giới thiệu

SQL Server hay Microsoft SQL Server là một hệ thống quản trị cơ sở dữ liệu quan hệ (Relational Database Management System – RDBMS) được phát triển bởi Microsoft vào năm 1988. Nó được sử dụng để tạo, duy trì, quản lý và triển khai hệ thống RDBMS. Được thiết kế để quản lý và lưu trữ dữ liệu, SQL Server cho phép người dùng truy vấn, thao tác và quản lý dữ liệu một cách hiệu quả và an toàn. SQL Server là một trong những hệ quản trị cơ sở dữ liệu phổ biến nhất trên thế giới và được sử dụng rộng rãi trong các doanh nghiệp. Phần mềm SQL Server được sử dụng khá rộng rãi vì nó được tối ưu để có thể chạy trên môi trường cơ sở dữ liệu rất lớn lên đến Tera – Byte cùng lúc phục vụ cho hàng ngàn user. Bên cạnh đó, ứng dụng này cung cấp đa dạng kiểu lập trình SQL từ ANSI SQL (SQL truyền thống) đến SQL và cả T-SQL (Transaction-SQL) được sử dụng cho cơ sở dữ liệu quan hệ nâng cao.

Kiến trúc của SQL Server được phân thành những phần:

Kiến trúc chung - General
Kiến trúc bộ nhớ - Memory
Kiến trúc file dữ liệu - Data file
Kiến trúc file nhật ký - Log file

1. Kiến trúc chung - General

Phần này trình bày kiến trúc tổng thể của SQL Server, mô tả cách các thành phần chính phối hợp với nhau để xử lý truy vấn và quản lý dữ liệu. SQL Server hoạt động theo mô hình Client – Server, trong đó Client gửi các yêu cầu (request) đến Server.

Client : Nơi bắt đầu các request.
Query: Truy vấn SQL ngôn ngữ bậc cao.
Logical Units: Từ khóa, Biểu thức, toán tử,…
N/W Packets: Code liên quan đến mạng.
Protocols: Trong SQL có 4 giao thức.
Shared memory: Cho các kết nối cục bộ và xử lí dự cố.
Named pipes : Cho các kết nối trong kết nối LAN.
TCP/IP: Cho các kết nối trong kết nối WAN.
VIA-Virtual Interface Adapter: Yêu cầu phần cứng đặc biệt để thiết lập bởi nhà cung cấp và bị bỏ từ phiên bản SQL 2012.
Server: Nơi dịch vụ SQL được cài đặt và lưu trú cơ sở dữ liệu.
Relational Engine: Đây là nơi thực thi thực tế sẽ thực hiện. Nó chứa trình phân tích cú pháp truy vấn, trình tối ưu hóa truy vấn, trình thực thi truy vấn.
Query Parser (Command Parser) and Compiler (Translator): kiểm tra cú pháp của truy vấn và sẽ chuyển đổi truy vấn sang ngôn ngữ máy.
Query Optimizer: Nó sẽ chuẩn bị kế hoạch thực thi như đầu ra bởi câu truy vấn đưa ra, số liệu thống kê, cây Algebrizer như đầu vào.
Kế hoạch thực thi: Nó như bản đồ đường đi, nó chứa yêu cầu của tất cả các bước để thực hiện như một phần của thực thi câu lệnh.
Query Executor: Đây là nơi các truy vấn sẽ được thực thi từng bước với sự giúp đỡ của kế hoạch thực thi và cả công cụ lưu trữ sẽ được tiếp xúc.
Storage Engine: Nó chịu trách nhiệm lưu trữ và truy xuất dữ liệu trên hệ thống lưu trữ ( đĩa, SAN, …), thao tác dữ liệu, khóa và quản lí giao dịch.
SQL OS: Nó nằm giữa máy chủ ( Windows OS) và SQL Server. Tất cả các thực thi trên cơ sở dữ liệu được quản lí bởi SQL OS. SQL OS cung cấp dịch vụ hệ điều hành khác nhau, chẳng hạn như các giao dịch quản lí bộ nhớ với vùng đệm, bộ đệm log, phát hiện deadlock bằng các sử dụng cấu trúc chặn và khóa.
Checkpoint Process: Checkpoint là một quá trình nội bộ ghi tất cả các trang dirty( các trang đã sửa đổi) từ Bộ nhớ đệm vào Đĩa vật lí. Ngoài ra, nó cũng ghi các bản ghi nhật ký từ bộ đệm log vào tệp vật lý. Hành động viết trang dirty từ bộ đệm cache vào file dữ liệu còn được gọi là “ Hardening of dirty pages” ( Cứng hóa các trang dirty). Nó là một quá trình chuyên dụng và tự động chạy bởi SQL Server tại các khoảng thời gian cụ thể. SQL Server chạy quy trình điểm kiểm tra cho từng cơ sở dữ liệu riêng lẻ. Điểm kiểm tra giúp giảm thời gian khôi phục cho SQL Server trong trường hợp tắt máy bất ngờ hoặc lỗi hệ thống \ Không thành công.
Lazy Writer: Lazy Writer sẽ đẩy các Dirty Page và ổ cứng vì một lý do hoàn toàn khác, đó là giải phóng bộ nhớ trong Buffer Pool. Điều này xảy ra khi SQL Server đang bị thiếu bộ nhớ. Tiến trình này được kiểm soát bởi một tiến trình Internal và không có thiết lập cho nó.

2. Kiến trúc bộ nhớ - Memory

Những tính năng nổi bật của kiến trúc bộ nhớ:

Một trong những mục tiêu thiết kế cơ bản của tất cả phần mềm cơ sở dữ liệu là giảm thiểu I/O ổ đĩa vì quá trình đọc và ghi đĩa là một trong những hành động sử dụng nhiều tài nguyên nhất.
Bộ nhớ trong Windows có thể được gọi với Virtual Address Space, được chia sẻ bởi chế độ Kernel (chế độ OS) và User (ứng dụng như SQL Server).
User address space của SQL Server được chia thành 2 phần: MemToLeave và Buffer Pool.
Kích thước của MemToLeave (MTL) và Buffer Pool (BPool) được quyết định bởi SQL Server trong quá trình khởi động.
Buffer Management là một thành phần quan trọng nếu muốn đạt được hiệu suất I/O cao. Nó bao gồm 2 cơ chế: Buffer Manager để truy cập và cập nhật các trang cơ sở dữ liệu và Buffer Pool để cắt giảm I/O file vào database.
Buffer Pool được chia thành nhiều phần, quan trọng nhất là Buffer Cache và Procedure Cache. Buffer Cache giữ các trang dữ liệu trong bộ nhớ để những dữ liệu thường xuyên truy cập có thể trích xuất từ bộ nhớ cache. Quá trình thay thế sẽ đọc các trang dữ liệu từ ổ đĩa. Đọc dữ liệu từ cache sẽ tối ưu hóa hiệu suất bằng cách giảm thiểu số lượng các thao tác I/O, vốn chậm hơn so với truy xuất dữ liệu từ bộ nhớ.
Procedure Cache giữ các thủ tục được lưu trữ và các Excecution Plan để tối ưu hóa số lần Excecution Plan được tạo. Bạn có thể tìm thấy thông tin về dung lượng và hoạt động trong Procedure Cache sử dụng lệnh DBCC PROCCACHE.
Các phần khác của Buffer Pool bao gồm:
- Các cấu trúc dữ liệu mức hệ thống: Chứa dữ liệu mức Instance về cơ sở dữ liệu, khóa.
- Log Cache: Dành riêng cho việc đọc và ghi các trang transaction.
- Connection Context: Mỗi kết nối với Instance có một vùng nhỏ bộ nhớ để ghi trạng thái hiện tại của kết nối. Thông tin này bao gồm thủ tục được lưu trữ và các thông số hàm do người dùng xác định, vị trí con trỏ chuột và nhiều hơn nữa.
- Stack Space: Windows phân bổ stack space cho mỗi luồng được bắt đầu với SQL Server.

3. Kiến trúc file dữ liệu - Data file

Kiến trúc này có các thành phần sau:

File Group:

Các file cơ sở dữ liệu có thể nhóm lại với nhau thành các nhóm file để phân bổ và quản lý theo mục đích. Một file chỉ có thể là thành viên của một nhóm file. Các file log không thể nhóm vào File Group vì dung lượng file log được quản lý riêng biệt với dung lượng dữ liệu.

Có hai loại File Group trong SQL Server là Primary và User-defined. Primary chứa các file dữ liệu chính và bất kỳ file nào không được gán cụ thể cho File Group khác. Tất cả các trang cho bảng hệ thống được cấp phát trong Primary. User-defined là các nhóm file do người dùng định nghĩa, nó được chỉ định bằng cách sử dụng từ khóa file group trong lệnh tạo cơ sở dữ liệu hoặc xóa cơ sở dữ liệu.

Một File Group trong mỗi cơ sở dữ liệu hoạt động như nhóm file mặc định. Khi SQL Server chỉ định một trang cho bảng hoặc chỉ mục (không nằm trong File Group nào khi tạo) thì trang đó sẽ nằm trong nhóm file mặc định. Để chuyển đổi nhóm file mặc định từ File Group này sang File Group khác, cần có db_owner fixed database role.

Primary là nhóm tệp mặc định. User cần có db_owner fixed database role để sao lưu tập tin và những nhóm file riêng lẻ.

2. File

Cơ sở dữ liệu có 3 loại file Primary (file dữ liệu chính), Secondary (file dữ liệu phụ) và Log (file nhật ký). Primary là điểm bắt đầu của cơ sở dữ liệu và trỏ đến các file khác trong cơ sở dữ liệu.

Mỗi cơ sở dữ liệu có một Primary. Bạn có thể đặt phần mở rộng cho các file dữ liệu chính là gì cũng được, nhưng khuyến nghị là nên để .mdf. File dữ liệu phụ là file khác file dữ liệu chính. Một cơ sở dữ liệu có thể có nhiều hoặc chỉ có một file dữ liệu phụ. Phần mở rộng cho file dữ liệu phụ nên đặt là .ndf.

Các file log giữ tất cả thông tin được sử dụng để phục hồi cơ sở dữ liệu. Cơ sở dữ liệu phải có ít nhất một file log. Chúng ta có thể có nhiều file log cho một cơ sở dữ liệu. Phần mở rộng nên đặt là .ldf.

Vị trí của tất cả các file trong cơ sở dữ liệu được ghi lại trong cả cơ sở dữ liệu tổng thể và file Primary của cơ sở dữ liệu. Trong hầu hết trường hợp, công cụ cơ sở dữ liệu sử dụng vị trí file từ cơ sở dữ liệu tổng thể.

File có 2 tên là Logical và Physical. Logical được sử dụng để tham chiếu đến file trong tất cả các lệnh T-SQL. Tên Physical là OS_file_name, nó phải tuân theo quy tắc của hệ điều hành. File dữ liệu và file log có thể được đặt trên hệ thống file FAT hoặc NTFS, nhưng không thể đặt trên các hệ thống file nén. Có thể có tối đa 32.767 file trong một cơ sở dữ liệu.

3. Extent

Extent là một đơn vị cơ bản trong đó không gian được phân bổ cho mỗi bảng, chỉ mục. Mỗi Extent là 8 trang liền kề hoặc 64KB. SQL Server có 2 loại Extent là Uniform và Mixed. Uniform được tạo thành từ một object duy nhất, Mixed được tạo thành từ tối đa 8 object.

4. Page

Page (trang) là đơn vị cơ bản trong lưu trữ dữ liệu của SQL Server. Kích thước của một trang là 8KB. Bắt đầu mỗi trang là 96byte tiêu đề, được sử dụng để lưu trữ thông tin hệ thống như loại trang, số lượng không gian trống trên trang và ID của đối tượng sở hữu trang. Có 9 loại trang dữ liệu trong SQL Server:

Data: Các hàng dữ liệu với tất cả dữ liệu từ text, ntext và ảnh.
Index: Các mục chỉ mục.
Tex\Image: Dữ liệu text, ntext và ảnh.
GAM: Thông tin về extent được chỉ định.
SGAM: Thông tin về extent được cấp phát ở mức hệ thống.
Page Free Space (PFS): Thông tin về không gian trống hiện có trên các trang.
Index Allocation Map (IAM): Thông tin về extent được sử dụng bởi bảng hoặc chỉ mục.
Bulk Changed Map (BCM): Thông tin về extent được sử đổi bởi hoạt động hàng loạt kể từ lệnh ghi sao lưu cuối cùng.
Differential Changed Map (DCM): Thông tin về extent đã thay đổi kể từ lệnh sao lưu cơ sở dữ liệu cuối cùng.

4. Kiến trúc file nhật ký - Log file

Các log transaction trên SQL Server hoạt động hợp lý khi nó là chuỗi các bản ghi log. Mỗi bản ghi log được xác định bởi Log Sequence Number (LSN), chứa ID của transaction mà nó thuộc về.

Log ghi lại những sửa đổi dữ liệu hoặc các hoạt động được thực hiện hay lấy hình ảnh trước và sau khi dữ liệu bị chỉnh sửa. Hình ảnh trước là bản sao của dữ liệu trước khi thao tác được thực hiện, hình ảnh sau là bản sao của dữ liệu sau khi thao tác đã được thực hiện.

Các bước để phục hồi một hoạt động phụ thuộc vào loại bản ghi log:

Thao tác logic được log.
Để đi tới thao tác logic ở trước, thao tác sẽ được thực hiện lại.
Để quay lại thao tác logic phía sau, thao tác logic đảo ngược sẽ được thực hiện.
Ảnh trước và sau được log.
Để đi tới thao tác ở trước, ảnh sau sẽ được áp dụng.
Để quay lại thao tác phía sau, ảnh trước sẽ được áp dụng.
Các thao tác khác nhau đã được ghi lại trong bản log transaction.

Bắt đầu và kết thúc mỗi transaction:

Mọi sửa đổi dữ liệu (chèn, cập nhật, xóa), bao gồm các thay đổi các thủ tục lưu trữ hệ thống hoặc lệnh ngôn ngữ định nghĩa dữ liệu (DDL) đến bảng, bao gồm cả bảng hệ thống.
Mọi extent và phân bổ, hủy phân bổ trang.
Tạo hoặc xóa bảng, chỉ mục.
Các thao tác rollback cũng được log lại. Mỗi transaction sẽ giữ một khoảng không gian trong bản log để chắc chắn rằng có đủ không gian log cần thiết cho rollback thực hiện lệnh hoặc thông báo lỗi. Không gian này sẽ được giải phóng khi transaction hoàn tất.

Phần của file log từ bản log đầu tiên (bắt buộc phải có để khôi phục lại toàn bộ cơ sở dữ liệu thành công) đến bản log cuối cùng được gọi là phần hoạt động của log hay log hoạt động. Đây là phần bản log bắt buộc để có thể phục hồi cơ sở dữ liệu đầy đủ. Không có phần nào trong log hoạt động được cắt xén. LSN của bản ghi log đầu tiên được gọi là LSN phục hồi tối thiểu (Min LSN).

SQL Server Database Engine chia mỗi file log Physical thành một số file log ảo. File log ảo không có kích thước cố định và không có số lượng file log ảo cố định cho mỗi file log Physical.

Database Engine chọn dung lượng cho file log ảo một cách tự động khi nó tạo hoặc mở rộng file log. Database Engine cố duy trì số lượng file ảo nhỏ. Kích thước của file log ảo không thể cấu hình hay thiết lập bởi quản trị viên. Duy nhất chỉ có khi file log Physical được xác định kích thước nhỏ và giá trị growth_increment thì file log ảo mới ảnh hưởng đến hiệu suất hệ thống.

Giá trị kích thước là kích thước khởi tạo cho file log và growth_increment là lượng không gian được thêm cho file mỗi khi file yêu cầu thêm không gian mới. Khi file log đạt đến kích thước lớn vì có nhiều sự gia tăng nhỏ, chúng sẽ có nhiều file log ảo. Điều này có thể làm chậm quá trình khởi động database và các hoạt động sao lưu, phục hồi log.

Lời khuyên là bạn nên gán cho file log giá trị kích thước gần với kích thước cuối cùng được yêu cầu và giá trị growth_increment tương đối lớn. SQL Server sử dụng write-ahead log (WAL), đảm bảo rằng không có sự sửa đổi dữ liệu nào được ghi vào ổ đĩa trước khi bản log liên quan được ghi vào ổ đĩa. Điều này giúp duy trì các thuộc tính ACID cho transaction.

5. Algebrizer trong SQL

Về Algebrizer một chút: Algebrizer là một tiến trình trong quá trình thực hiện truy vấn. Nó bắt đầu làm việc sau Parser. Khi Query Parser tìm thấy một truy vấn đúng cú pháp, nó sẽ chuyển đến cho Algebrizer và công việc của Algebrizer bắt đầu:. Algebrizer chịu trách nhiệm xác minh các object và tên cột (mà bạn đã cung cấp trong truy vấn hoặc đang được tham chiếu bởi truy vấn). Ví dụ, nếu tên cột bị viết sai trong truy vấn, Algebrizer phải có trách nhiệm xác nhận điều đó và tạo ra lỗi. Algebrizer cũng xác định tất cả các loại dữ liệu đang được xử lý trong một truy vấn nhất định. Algebrizer xác minh xem GROUP BY và những cột đã gộp có được đặt đúng nơi hay không. Ví dụ, nếu bạn viết truy vấn sau và chỉ nhấn Ctrl + F5 để phân tích cú pháp thì không xuất hiện lỗi. Nhưng nếu nhấn F5 để chạy truy vấn thì Algebrizer sẽ làm việc và trả về lỗi.

6. Checkpoint trong SQL Server

Trong SQL Server có 4 loại checkpoint:

Automatic: Loại này là checkpoint phổ biến nhất, chạy dưới dạng một tiến trình nền để chắc chắn SQL Server Database có thể được phục hồi trong khoảng thời gian được xác định bởi Recovery Interval trong Server Configuration Option.
Indirect: Loại checkpoint này mới có trên SQL Server 2012. Nó cũng là tiến trình chạy nền nhưng chỉ dành cho một số user cụ thể xác định thời gian phục hồi cho database cụ thể trong tùy chọn cấu hình. Khi Target_Recovery_Time cho database cụ thể được chọn, nó sẽ ghi đè lên Recovery Interval được chỉ định cho máy chủ, tránh Automatic checkpoint trên database đó.
Manual: Checkpoint này chạy giống như bất kỳ lệnh SQL nào khác, khi bạn tạo lệnh checkpoint, nó sẽ chạy để hoàn thành. Checkpoint này chỉ chạy trên database hiện tại. Bạn có thể xác định Checkpoint_Duration trong tùy chọn để chỉ ra khoảng thời gian bạn muốn checkpoint được hoàn thành.
Internal: Khi là một user, bạn không thể kiểm soát loại checkpoint này trong những hành động cụ thể như:

Ngắt khởi động một hành động checkpoint trên tất cả các database trừ khi việc shutdown bị lỗi, không bình thường (dùng lệnh Shutdown with nowith).

Khi mô hình phục hồi bị thay đổi từ Full\Bulk-logged sang Simple.

Trong khi đang sao lưu cơ sở dữ liệu:

Nếu database đang ở trong kiểu phục hồi Simple, quá trình checkpoint sẽ tự động thực hiện hoặc khi bản log đã đầy 70%, hoặc dựa trên tùy chọn Recovery Interval của Server.
Lệnh ALTER DATABASE để thêm hoặc xóa file log/dữ liệu cũng khởi tạo một checkpoint.
Checkpoint cũng diễn ra khi mô hình phục hồi của database là Bulk-logged và hoạt động ghi tối thiểu được thực hiện.

Kiến Trúc SQL Server