MS SQL Server - Kiến trúc

Chúng tôi đã phân loại kiến trúc của SQL Server thành các phần sau để dễ hiểu:

Kiến trúc chung
Kiến trúc bộ nhớ
Kiến trúc tệp dữ liệu
Kiến trúc tệp nhật ký

Kiến trúc chung

Client - Nơi bắt đầu yêu cầu.

Query - Truy vấn SQL là ngôn ngữ cấp cao.

Logical Units - Từ khóa, biểu thức và toán tử, v.v.

N/W Packets - Mã liên quan đến mạng.

Protocols - Trong SQL Server chúng ta có 4 giao thức.

Bộ nhớ dùng chung (cho các kết nối cục bộ và mục đích khắc phục sự cố).
Đường ống được đặt tên (cho các kết nối trong kết nối LAN).
TCP / IP (đối với các kết nối có trong kết nối WAN).
VIA-Bộ điều hợp giao diện ảo (yêu cầu phần cứng đặc biệt do nhà cung cấp thiết lập và cũng không được chấp nhận từ phiên bản SQL 2012).

Server - Nơi Dịch vụ SQL được cài đặt và cơ sở dữ liệu nằm ở đâu.

Relational Engine- Đây là nơi thực thi thực sự sẽ được 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 và trình thực thi truy vấn.

Query Parser (Command Parser) and Compiler (Translator) - Thao tác này sẽ kiểm tra cú pháp của truy vấn và nó sẽ chuyển truy vấn sang ngôn ngữ máy.

Query Optimizer - Nó sẽ chuẩn bị kế hoạch thực thi dưới dạng đầu ra bằng cách lấy truy vấn, thống kê và cây Algebrizer làm đầu vào.

Execution Plan - Nó giống như một lộ trình, chứa thứ tự của tất cả các bước sẽ được thực hiện như một phần của việc thực thi truy vấn.

Query Executor - Đây là nơi truy vấn sẽ được thực hiện từng bước với sự trợ giúp của kế hoạch thực thi và cũng có thể liên hệ với bộ máy lưu trữ.

Storage Engine - 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, v.v.,), thao tác dữ liệu, khóa và quản lý các giao dịch.

SQL OS- Điều này nằm giữa máy chủ (Windows OS) và SQL Server. Tất cả các hoạt động được thực hiện trên cơ sở dữ liệu đều do SQL OS đảm nhận. SQL OS cung cấp các 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 nhật ký và phát hiện khóa chết bằng cách sử dụng cấu trúc khóa và khóa.

Checkpoint Process- Checkpoint là một quá trình nội bộ ghi tất cả các trang bẩn (trang đã sửa đổi) từ Bộ đệm đệ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 nhật ký vào tệp vật lý. Việc ghi các trang bẩn từ bộ đệm cache vào tệp dữ liệu còn được gọi là Làm cứng các trang bẩn.

Nó là một quá trình chuyên dụng và chạy tự động bởi SQL Server trong 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ẻ. Checkpoint giúp giảm thời gian khôi phục cho SQL Server trong trường hợp tắt máy đột xuất hoặc hệ thống gặp sự cố \ Failure.

Các điểm kiểm tra trong SQL Server

Trong SQL Server 2012, có bốn loại checkpoints -

Automatic - Đây là điểm kiểm tra phổ biến nhất chạy như một quá trình trong nền để đảm bảo Cơ sở dữ liệu SQL Server có thể được khôi phục trong thời hạn được xác định bởi Khoảng thời gian khôi phục - Tùy chọn cấu hình máy chủ.
Indirect- Tính năng này mới trong SQL Server 2012. Tính năng này cũng chạy ở chế độ nền nhưng để đáp ứng thời gian khôi phục mục tiêu do người dùng chỉ định cho cơ sở dữ liệu cụ thể nơi tùy chọn đã được cấu hình. Khi Target_Recovery_Time cho một cơ sở dữ liệu nhất định đã được chọn, điều này sẽ ghi đè Khoảng thời gian khôi phục được chỉ định cho máy chủ và tránh điểm kiểm tra tự động trên DB đó.
Manual- Câu lệnh này chạy giống như bất kỳ câu lệnh T-SQL nào khác, khi bạn đưa ra lệnh checkpoint, nó sẽ chạy đến khi hoàn thành. Điểm kiểm tra thủ công chỉ chạy cho cơ sở dữ liệu hiện tại của bạn. Bạn cũng có thể chỉ định Checkpoint_Duration là tùy chọn - khoảng thời gian này chỉ định thời gian bạn muốn điểm kiểm tra của mình hoàn thành.
Internal- Là người dùng, bạn không thể kiểm soát trạm kiểm soát nội bộ. Được phát hành trên các hoạt động cụ thể như
- Việc tắt máy bắt đầu hoạt động điểm kiểm tra trên tất cả các cơ sở dữ liệu ngoại trừ khi quá trình tắt máy không sạch (tắt máy ngay bây giờ).
- Nếu mô hình khôi phục được thay đổi từ Full \ Bulk-log thành Simple.
- Trong khi sao lưu cơ sở dữ liệu.
- Nếu DB của bạn đang ở trong mô hình khôi phục đơn giản, thì quá trình điểm kiểm tra sẽ tự động thực thi khi nhật ký trở nên đầy 70% hoặc dựa trên Server option-Recovery Interval.
- Lệnh Alter cơ sở dữ liệu để thêm hoặc xóa tệp data \ log cũng khởi tạo một điểm kiểm tra.
- Checkpoint cũng diễn ra khi mô hình khôi phục của DB được ghi nhật ký hàng loạt và một hoạt động được ghi nhật ký tối thiểu được thực hiện.
- Tạo ảnh chụp nhanh DB.
Lazy Writer Process- Người viết lười biếng sẽ đẩy các trang bẩn vào đĩa vì một lý do hoàn toàn khác, vì nó cần giải phóng bộ nhớ trong vùng đệm. Điều này xảy ra khi máy chủ SQL bị áp lực bộ nhớ. Theo như tôi được biết, điều này được kiểm soát bởi một quy trình nội bộ và không có cài đặt nào cho nó.

Máy chủ SQL liên tục giám sát việc sử dụng bộ nhớ để đánh giá sự tranh chấp tài nguyên (hoặc tính khả dụng); công việc của nó là đảm bảo rằng luôn có một lượng không gian trống nhất định. Là một phần của quá trình này, khi nó nhận thấy bất kỳ tranh chấp tài nguyên nào như vậy, nó sẽ kích hoạt Lazy Writer để giải phóng một số trang trong bộ nhớ bằng cách ghi các trang bẩn ra đĩa. Nó sử dụng thuật toán Ít được Sử dụng Gần đây (LRU) để quyết định những trang nào sẽ được chuyển vào đĩa.

Nếu Lazy Writer luôn hoạt động, nó có thể chỉ ra sự tắc nghẽn bộ nhớ.

Kiến trúc bộ nhớ

Sau đây là một số 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ế chính của tất cả các phần mềm cơ sở dữ liệu là giảm thiểu I / O đĩa vì đọc và ghi đĩa là một trong những hoạt động sử dụng nhiều tài nguyên nhất.
Bộ nhớ trong cửa sổ có thể được gọi bằng Không gian địa chỉ ảo, được chia sẻ bởi chế độ Kernel (chế độ OS) và chế độ người dùng (Ứng dụng như SQL Server).
"Không gian địa chỉ người dùng" của SQL Server được chia thành hai vùng: MemToLeave và Buffer Pool.
Kích thước của MemToLeave (MTL) và Buffer Pool (BPool) được xác định bởi SQL Server trong khi khởi động.
Buffer managementlà một thành phần quan trọng trong việc đạt được I / O hiệu quả cao. Thành phần quản lý bộ đệm bao gồm hai cơ chế: bộ quản lý bộ đệm để truy cập và cập nhật các trang cơ sở dữ liệu và vùng đệm để giảm I / O tệp cơ sở dữ liệu.
Vùng đệm được chia thành nhiều phần. Những thứ quan trọng nhất là bộ đệm đệm (còn gọi là bộ đệm dữ liệu) và bộ đệm thủ tục.Buffer cachegiữ các trang dữ liệu trong bộ nhớ để dữ liệu được truy cập thường xuyên có thể được truy xuất từ bộ nhớ cache. Giải pháp thay thế sẽ là đọc các trang dữ liệu từ đĩa. Việc đọc các trang dữ liệu từ bộ nhớ đệm sẽ tối ưu hóa hiệu suất bằng cách giảm thiểu số lượng các hoạt động I / O bắt buộc vốn đã chậm hơn so với việc truy xuất dữ liệu từ bộ nhớ.
Procedure cachegiữ thủ tục đã lưu trữ và kế hoạch thực thi truy vấn để giảm thiểu số lần kế hoạch truy vấn phải được tạo. Bạn có thể tìm hiểu thông tin về kích thước và hoạt động trong bộ đệm thủ tục bằng cách sử dụng câu lệnh DBCC PROCCACHE.

Các phần khác của vùng đệm bao gồm -

System level data structures - Lưu trữ dữ liệu cấp phiên bản SQL Server về cơ sở dữ liệu và khóa.
Log cache - Dành cho việc đọc và ghi các trang nhật ký giao dịch.
Connection context- Mỗi kết nối đến cá thể có một vùng nhớ nhỏ để ghi lại 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 tham số chức năng do người dùng xác định, vị trí con trỏ và hơn thế nữa.
Stack space - Windows phân bổ không gian ngăn xếp cho mỗi luồng được khởi động bởi SQL Server.

Kiến trúc tệp dữ liệu

Kiến trúc Tệp dữ liệu có các thành phần sau:

Nhóm tệp

Các tệp cơ sở dữ liệu có thể được nhóm lại với nhau trong các nhóm tệp cho mục đích cấp phát và quản trị. Không có tệp nào có thể là thành viên của nhiều nhóm tệp. Các tệp nhật ký không bao giờ là một phần của một nhóm tệp. Không gian nhật ký được quản lý tách biệt với không gian dữ liệu.

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

Một nhóm tệp trong mỗi cơ sở dữ liệu hoạt động như nhóm tệp mặc định. Khi SQL Server phân bổ một trang cho một bảng hoặc chỉ mục mà không có nhóm tệp nào được chỉ định khi chúng được tạo, các trang được phân bổ từ nhóm tệp mặc định. Để chuyển nhóm tệp mặc định từ nhóm tệp này sang nhóm tệp khác, nó phải có vai trò db cố định db_owner.

Theo mặc định, nhóm tệp chính là nhóm tệp mặc định. Người dùng phải có vai trò cơ sở dữ liệu cố định db_owner để sao lưu các tệp và nhóm tệp riêng lẻ.

Các tập tin

Cơ sở dữ liệu có ba loại tệp - Tệp dữ liệu chính, tệp dữ liệu thứ cấp và tệp Nhật ký. Tệp dữ liệu chính là điểm bắt đầu của cơ sở dữ liệu và trỏ đến các tệp khác trong cơ sở dữ liệu.

Mỗi cơ sở dữ liệu đều có một tệp dữ liệu chính. Chúng tôi có thể cung cấp bất kỳ phần mở rộng nào cho tệp dữ liệu chính nhưng phần mở rộng được đề xuất là.mdf. Tệp dữ liệu thứ cấp là tệp khác với tệp dữ liệu chính trong cơ sở dữ liệu đó. Một số cơ sở dữ liệu có thể có nhiều tệp dữ liệu thứ cấp. Một số cơ sở dữ liệu có thể không có một tệp dữ liệu thứ cấp. Phần mở rộng được đề xuất cho tệp dữ liệu phụ là.ndf.

Tệp nhật ký giữ tất cả thông tin nhật ký được sử dụng để khôi phục cơ sở dữ liệu. Cơ sở dữ liệu phải có ít nhất một tệp nhật ký. Chúng tôi có thể có nhiều tệp nhật ký cho một cơ sở dữ liệu. Phần mở rộng được đề xuất cho tệp nhật ký là.ldf.

Vị trí của tất cả các tệp trong cơ sở dữ liệu được ghi lại trong cả cơ sở dữ liệu chính và tệp chính cho cơ sở dữ liệu. Hầu hết thời gian, cơ sở dữ liệu sử dụng vị trí tệp từ cơ sở dữ liệu chính.

Tệp có hai tên - Logical và Physical. Tên logic được sử dụng để chỉ tệp trong tất cả các câu lệnh T-SQL. Tên vật lý là OS_file_name, nó phải tuân theo các quy tắc của OS. Các tệp Dữ liệu và Nhật ký có thể được đặt trên hệ thống tệp FAT hoặc NTFS, nhưng không thể được đặt trên hệ thống tệp nén. Có thể có tới 32.767 tệp trong một cơ sở dữ liệu.

Phạm vi

Extents là đơn vị cơ bản trong đó không gian được phân bổ cho các bảng và chỉ mục. Một phạm vi là 8 trang liền kề hoặc 64KB. SQL Server có hai loại phạm vi - Đồng nhất và Hỗn hợp. Các phạm vi thống nhất chỉ được tạo thành từ một đối tượng duy nhất. Các phạm vi hỗn hợp được chia sẻ bởi tối đa tám đối tượng.

Các trang

Nó là đơn vị lưu trữ dữ liệu cơ bản trong MS SQL Server. Kích thước của trang là 8KB. Phần đầu của mỗi trang là tiêu đề 96 byte được sử dụng để lưu trữ thông tin hệ thống như loại trang, dung lượng trống trên trang và id đối tượng của đối tượng sở hữu trang. Có 9 loại trang dữ liệu trong SQL Server.

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

Kiến trúc tệp nhật ký

Nhật ký giao dịch SQL Server hoạt động hợp lý như thể nhật ký giao dịch là một chuỗi các bản ghi nhật ký. Mỗi bản ghi nhật ký được xác định bằng Số thứ tự nhật ký (LSN). Mỗi bản ghi nhật ký chứa ID của giao dịch mà nó thuộc về.

Bản ghi nhật ký cho việc sửa đổi dữ liệu ghi lại thao tác logic được thực hiện hoặc chúng ghi lại hình ảnh trước và sau của dữ liệu được sửa đổi. Hình ảnh trước là bản sao của dữ liệu trước khi hoạt động được thực hiện; hình ảnh sau là một bản sao của dữ liệu sau khi hoạt động đã được thực hiện.

Các bước để khôi phục một thao tác phụ thuộc vào loại bản ghi nhật ký -

Hoạt động logic được ghi lại.
- Để chuyển hoạt động logic về phía trước, hoạt động được thực hiện lại.
- Để đảo ngược hoạt động logic, hoạt động logic ngược được thực hiện.
Trước và sau khi hình ảnh được ghi lại.
- Để chuyển hoạt động về phía trước, hình ảnh sau sẽ được áp dụng.
- Để quay lại thao tác, hình ảnh trước đó sẽ được áp dụng.

Các loại hoạt động khác nhau được ghi lại trong nhật ký giao dịch. Các hoạt động này bao gồm -

Bắt đầu và kết thúc mỗi giao dịch.
Mọi sửa đổi dữ liệu (chèn, cập nhật hoặc xóa). Điều này bao gồm các thay đổi bởi các thủ tục được hệ thống lưu trữ hoặc các câu lệnh ngôn ngữ định nghĩa dữ liệu (DDL) đối với bất kỳ bảng nào, bao gồm cả các bảng hệ thống.
Mọi mức độ và phân bổ trang hoặc phân bổ.
Tạo hoặc giảm một bảng hoặc chỉ mục.

Hoạt động khôi phục cũng được ghi lại. Mỗi giao dịch dự trữ không gian trên nhật ký giao dịch để đảm bảo rằng tồn tại đủ không gian nhật ký để hỗ trợ quá trình khôi phục được gây ra bởi một câu lệnh khôi phục rõ ràng hoặc nếu gặp lỗi. Không gian dành riêng này được giải phóng khi giao dịch hoàn tất.

Phần của tệp nhật ký từ bản ghi nhật ký đầu tiên phải có để khôi phục thành công trên toàn cơ sở dữ liệu đến bản ghi nhật ký được ghi cuối cùng được gọi là phần hiện hoạt của nhật ký hoặc nhật ký hoạt động. Đây là phần của nhật ký cần thiết để khôi phục đầy đủ cơ sở dữ liệu. Không có phần nào của nhật ký hoạt động có thể bị cắt bớt. LSN của bản ghi nhật ký đầu tiên này được gọi là LSN khôi phục tối thiểu (Min LSN).

Công cụ cơ sở dữ liệu SQL Server chia nội bộ từng tệp nhật ký vật lý thành một số tệp nhật ký ảo. Tệp nhật ký ảo không có kích thước cố định và không có số tệp nhật ký ảo cố định cho tệp nhật ký vật lý.

Công cụ Cơ sở dữ liệu chọn kích thước của tệp nhật ký ảo một cách động trong khi tạo hoặc mở rộng tệp nhật ký. Cơ sở dữ liệu cố gắng duy trì một số lượng nhỏ các tệp ảo. Kích thước hoặc số lượng tệp nhật ký ảo không thể được định cấu hình hoặc thiết lập bởi quản trị viên. Lần duy nhất các tệp nhật ký ảo ảnh hưởng đến hiệu suất hệ thống là nếu các tệp nhật ký vật lý được xác định bởi kích thước nhỏ và giá trị growth_increment.

Giá trị kích thước là kích thước ban đầu cho tệp nhật ký và giá trị growth_increment là lượng không gian được thêm vào tệp mỗi khi yêu cầu không gian mới. Nếu tệp nhật ký phát triển đến kích thước lớn do nhiều gia số nhỏ, chúng sẽ có nhiều tệp nhật ký ảo. Điều này có thể làm chậm quá trình khởi động cơ sở dữ liệu và cũng ghi nhật ký các hoạt động sao lưu và khôi phục.

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