Tạo instance SQL Server hiệu suất cao

Hướng dẫn này chỉ ra cách tạo một instance SQL Server được tối ưu hóa cho hiệu suất.

Hướng dẫn này sử dụng SQL Server Standard Edition 2014, vì vậy không phải mọi tùy chọn cấu hình được trình bày trong hướng dẫn này đều phù hợp với tất cả mọi người và không phải tất cả chúng đều mang lại lợi ích hiệu suất đáng chú ý cho mọi workload.

Trước khi bắt đầu

Hướng dẫn này sử dụng các thành phần sau:

• Máy ảo Windows Server 2012.
• 01 SSD local hiệu suất cao để lưu trữ tempdb và pagefile Windows.
• 01 SSD persistent hiệu suất cao để lưu trữ database của bạn.
• File image cài đặt SQL Server Standard 2014.

Cấu hình Windows

Thiết lập ổ đĩa

Tạo và định dạng các ổ đĩa:

• Từ menu Start, tìm kiếm “Server Manager” và mở nó.
• Chọn File and Storage Services, sau đó chọn Disks. SSD local được đặt tên là Google EphemeralDisk. Cả SSD local và SSD persistent đều được đánh dấu là phân vùng Unknown:

• Bấm chuột phải vào ổ đĩa SSD local 375 GB có tên “Google Ephemeral Disk”, rồi chọn New Volume.
• Tiếp tục với các giá trị mặc định và chọn nhãn ổ đĩa là P:, vì đây sẽ là ổ đĩa pagefile.
• Khi bạn đến bước File System Settings, hãy thay đổi Allocation unit size thành 8192 và nhập pagefile cho Volume label.

Lặp lại các bước tương tự ở trên cho ổ đĩa SSD persistent thứ hai, ngoại trừ ba thay đổi sau:

• Chọn nhãn ổ đĩa là D:.
• Thiết lập Allocation unit size thành 64k.
• Nhập sqldata cho Volume label.

Sửa lỗi “Failed to mount path – Invalid Parameter”

Nếu bạn gặp phải lỗi này, hãy làm theo các bước sau:

• Nhấp vào Close.
• Nhấp vào biểu tượng làm mới đĩa ở phía trên bên phải.
• Nhấp vào đĩa liên tục 500 GB trong danh sách.
• Trong bảng Volumes, bấm chuột phải vào tập rồi chọn Manage Drive Letter and Access Paths.

• Chọn D: cho nhãn ổ đĩa.
• Bấm OK.

Di chuyển pagefile Windows

Bây giờ các ổ đĩa mới đã được tạo và kết nối, hãy di chuyển tệp pagefile Windows vào ổ SSD local, điều này giải phóng IOPS ổ đĩa lưu trữ và cải thiện thời gian truy cập bộ nhớ ảo của bạn.

• Từ menu Start, tìm kiếm View advanced system settings, sau đó mở hộp thoại.
• Nhấp vào tab Advanced và trong phần Performance, hãy nhấp vào Settings.
• Trong phần Virtual memory, nhấp vào nút Change.
• Bỏ chọn hộp Automatically manage paging file size for all drives. Hệ thống đã thiết lập tệp pagefile của bạn trên ổ đĩa C: và bạn cần phải di chuyển tệp đó.
• Bấm C: rồi bấm vào nút radio No paging file.
• Nhấp vào nút Set.
• Để tạo tệp pagefile mới, hãy nhấp vào ổ P:, sau đó nhấp vào nút radio System managed size.
• Nhấp vào nút Set.
• Bấm OK ba lần để thoát khỏi advanced system properties.

Bộ phận Hỗ trợ của Microsoft đã xuất bản một mẹo bổ sung cho cài đặt bộ nhớ ảo.

Thiết lập cấu hình nguồn

Thiết lập cấu hình nguồn thành High-Performance thay vì Balanced.

• Từ menu Start, hãy tìm kiếm “Choose a Power Plan”, sau đó mở nó.
• Chọn nút radio High Performance.
• Thoát khỏi hộp thoại.

Cấu hình SQL Server

Sử dụng SQL Server Management Studio để thực hiện hầu hết các tác vụ quản trị. Image được cấu hình trước cho SQL Server 2014 đi kèm với Management Studio đã được cài đặt, nhưng nếu bạn đang sử dụng image SQL Server 2016, bạn cần tải xuống và cài đặt nó theo cách thủ công. Sau khi cài đặt, khởi chạy Management Studio và sau đó nhấp vào Connect để kết nối với cơ sở dữ liệu mặc định.

Di chuyển dữ liệu và tệp nhật ký

Image được cấu hình trước cho SQL Server đi kèm với mọi thứ được cài đặt trên ổ đĩa C:, bao gồm cả cơ sở dữ liệu hệ thống. Để tối ưu hóa thiết lập của bạn, hãy chuyển các tệp đó sang ổ đĩa D: mới mà bạn đã tạo. Bạn cũng nên nhớ tạo tất cả cơ sở dữ liệu mới trên ổ đĩa D:. Vì bạn đang sử dụng đĩa SSD persistent, bạn không cần phải lưu trữ các tệp dữ liệu và tệp nhật ký trên các phân vùng ổ đĩa riêng biệt.

Có hai cách để di chuyển cài đặt sang ổ đĩa khác: sử dụng trình cài đặt hoặc di chuyển các tệp theo cách thủ công.

Sử dụng trình cài đặt

Để sử dụng trình cài đặt, hãy chạy tệp cài đặt SQL Server và chọn một đường dẫn cài đặt mới trên ổ đĩa mới của bạn.

Di chuyển các tệp theo cách thủ công

Di chuyển cơ sở dữ liệu hệ thống và cấu hình SQL Server để lưu dữ liệu và tệp nhật ký trên cùng một ổ đĩa:

• Tạo một thư mục mới có tên D:SQLData.
• Mở Cửa sổ Lệnh.
• Nhập lệnh sau để cấp quyền truy cập đầy đủ cho NT ServiceMSSQLSERVER:

icacls D:SQLData /Grant "NT ServiceMSSQLServer":(OI)(CI)F

• Sử dụng Management Studio và các hướng dẫn sau để di chuyển cơ sở dữ liệu hệ thống của bạn và thay đổi vị trí tệp mặc định cho cơ sở dữ liệu mới.
• Nếu bạn có kế hoạch sử dụng tính năng Report Server, di chuyển các file ReportServer và ReportServerTempDB là được.

Quan trọng: Đừng sửa đổi tệp TempDB vì trước tiên bạn phải thay đổi một số cài đặt quyền và sau đó di chuyển DB sang ổ SSD local.

Sau khi bạn di chuyển các tệp cơ sở dữ liệu cấu hình chính và khởi động lại, bạn cần cấu hình hệ thống để trỏ đến vị trí mới cho mô hình và cơ sở dữ liệu MSDB. Đây là một tập lệnh trợ giúp để chạy trong Management Studio:

ALTER DATABASE model MODIFY FILE ( NAME = modeldev , FILENAME = 'D:SQLDatamodel.mdf' )
ALTER DATABASE model MODIFY FILE ( NAME = modellog , FILENAME = 'D:SQLDatamodellog.ldf' )
ALTER DATABASE msdb MODIFY FILE ( NAME = MSDBData , FILENAME = 'D:SQLDataMSDBData.mdf' )
ALTER DATABASE msdb MODIFY FILE ( NAME = MSDBlog , FILENAME = 'D:SQLDataMSDBLog.ldf' )

Sau khi bạn thực hiện các lệnh này:

• Sử dụng snap-in services.msc để dừng dịch vụ cơ sở dữ liệu SQL Server.
• Sử dụng trình khám phá tệp của Windows để di chuyển các tệp vật lý từ ổ đĩa C: chứa cơ sở dữ liệu Master vào thư mục D:SQLData.
• Khởi động dịch vụ cơ sở dữ liệu SQL Server.

Thiết lập quyền hệ thống

Sau khi di chuyển cơ sở dữ liệu hệ thống, hãy sửa đổi một số cài đặt bổ sung, bắt đầu với quyền cho tài khoản người dùng Windows được tạo để chạy tiến trình SQL Server của bạn, được đặt tên NT ServiceMSSQLSERVER.

Cấp quyền “Lock Pages in Memory”

Quyền chính sách nhóm Lock Pages in Memory ngăn Windows di chuyển các trang trong bộ nhớ vật lý sang bộ nhớ ảo. Để giữ cho bộ nhớ vật lý luôn trống và có tổ chức, Windows cố gắng hoán đổi các trang cũ, hiếm khi được sửa đổi thành tệp pagefile bộ nhớ ảo trên đĩa.

SQL Server lưu trữ thông tin quan trọng trong bộ nhớ, chẳng hạn như cấu trúc bảng, kế hoạch thực thi và truy vấn được lưu trong bộ nhớ cache. Một số thông tin này hiếm khi thay đổi, vì vậy nó trở thành mục tiêu cho tệp pagefile. Nếu thông tin này được chuyển đến tệp pagefile, hiệu suất của SQL Server có thể giảm. Việc cấp quyền chính sách nhóm Lock Pages in Memory cho tài khoản dịch vụ của SQL Server sẽ ngăn chặn việc hoán đổi này.

Làm theo các bước sau:

• Nhấp vào Start và sau đó tìm kiếm Edit Group Policy để mở bảng điều khiển.
• Truy cập Local Computer Policy > Computer Configuration > Windows Settings > Local Policies > User Rights Assignment.
• Tìm kiếm và sau đó bấm đúp vào Lock pages in memory.
• Nhấp vào Add User or Group.
• Tìm kiếm “NT ServiceMSSQLSERVER”.
• Nếu bạn thấy nhiều tên, hãy bấm đúp vào tên MSSQLSERVER.
• Bấm OK hai lần.
• Giữ bảng điều khiển Group Policy Editor luôn mở.

Quan trọng: Sau khi hoàn thành các bước này và khởi động lại SQL Server, bạn có thể xác minh cài đặt đang hoạt động bằng cách xem nhật ký SQL Server. Nhật ký chứa một mục chẳng hạn như, “Using locked pages in the memory manager.”

Cấp quyền “Perform volume maintenance tasks”

Theo mặc định, khi một ứng dụng yêu cầu một phần không gian đĩa từ Windows, hệ điều hành sẽ định vị một phần không gian đĩa có kích thước thích hợp, sau đó xóa toàn bộ phần đĩa trước khi đưa nó trở lại ứng dụng. Bởi vì SQL Server giỏi trong việc phát triển tệp và lấp đầy không gian đĩa, hành vi này không phải là tối ưu.

Có một API riêng để phân bổ không gian đĩa cho một ứng dụng, thường được gọi là khởi tạo tệp tức thì. Thật không may, cài đặt này chỉ hoạt động cho các tệp dữ liệu, nhưng bạn sẽ tìm hiểu trong phần sắp tới về sự phát triển tệp nhật ký. Khởi tạo tệp tức thì yêu cầu tài khoản dịch vụ chạy tiến trình SQL Server có quyền chính sách nhóm khác, được gọi là Perform volume maintenance tasks.

• Trong Group Policy Editor, hãy tìm kiếm “Perform volume maintenance tasks”.
• Thêm tài khoản “NT ServiceMSSQLSERVER” như bạn đã làm trong phần trước.
• Khởi động lại tiến trình SQL Server để kích hoạt cả hai cài đặt.

Thiết lập TempDB

Nó từng là phương pháp hay nhất để tối ưu hóa việc sử dụng CPU SQL Server bằng cách tạo một tệp TempDB cho mỗi CPU. Tuy nhiên, vì số lượng CPU đã tăng lên theo thời gian, việc tuân theo hướng dẫn này có thể khiến hiệu suất giảm.

Một thiết lập tối ưu là sử dụng 4 tệp TempDB. Khi bạn đo lường hiệu suất của hệ thống, trong một số trường hợp hiếm hoi, bạn có thể cần phải tăng dần số lượng tệp TempDB lên tối đa 8 tệp.

Bạn có thể chạy tập lệnh Transact-SQL (T-SQL) bên trong SQL Server Management Studio để di chuyển các tệp TempDB vào một thư mục trong ổ đĩa P:.

• Tạo thư mục p:tempdb.
• Cấp quyền truy cập bảo mật đầy đủ cho tài khoản người dùng “NT ServiceMSSQLSERVER”:

icacls p:tempdb /Grant "NT ServiceMSSQLServer":(OI)(CI)F

• Chạy tập lệnh sau bên trong SQL Server Management Studio, để di chuyển tệp dữ liệu TempDB và tệp nhật ký:

USE Master
GO
ALTER DATABASE [tempdb] MODIFY FILE (NAME = tempdev, FILENAME = 'p:tempdbtempdb.mdf')
GO
ALTER DATABASE [tempdb] MODIFY FILE (NAME = templog, FILENAME = 'p:tempdbtemplog.ldf')
GO

• Khởi động lại SQL Server.
• Chạy tập lệnh sau để sửa đổi kích thước tệp và tạo ba tệp dữ liệu bổ sung cho tệp TempDB mới.

ALTER DATABASE [tempdb] MODIFY FILE (NAME = tempdev, FILENAME = 'p:tempdbtempdb.mdf', SIZE=8GB)
ALTER DATABASE [tempdb] MODIFY FILE (NAME = templog, FILENAME = 'p:tempdbtemplog.ldf' , SIZE = 2GB)
ALTER DATABASE [tempdb] ADD FILE (NAME = 'tempdev1', FILENAME = 'p:tempdbtempdev1.ndf' , SIZE = 8GB, FILEGROWTH = 0);
ALTER DATABASE [tempdb] ADD FILE (NAME = 'tempdev2', FILENAME = 'p:tempdbtempdev2.ndf' , SIZE = 8GB, FILEGROWTH = 0);
ALTER DATABASE [tempdb] ADD FILE (NAME = 'tempdev3', FILENAME = 'p:tempdbtempdev3.ndf' , SIZE = 8GB, FILEGROWTH = 0);
GO

Nếu bạn sử dụng SQL Server 2016, có 3 tệp TempDB bổ sung cần xóa sau khi bạn thực hiện các bước trước đó:

ALTER DATABASE [tempdb] REMOVE FILE temp2;
ALTER DATABASE [tempdb] REMOVE FILE temp3;
ALTER DATABASE [tempdb] REMOVE FILE temp4;

• Khởi động lại SQL Server.
• Xóa tệp model, MSDB, master và tempdb từ vị trí ban đầu trên ổ đĩa C:.

Bạn đã di chuyển thành công các tệp TempDB của mình vào phân vùng SSD local. Động thái này mang lại một số rủi ro, đã được đề cập trước đó, nhưng nếu chúng bị mất vì bất kỳ lý do gì, SQL Server sẽ xây dựng lại các tệp TempDB. Di chuyển TempDB mang lại cho bạn hiệu suất tăng thêm của SSD cục bộ và làm giảm IOPS được sử dụng trên các ổ đĩa lưu trữ của bạn.

Cài đặt “max degree of parallelism”

Cài đặt mặc định được đề xuất cho max degree of parallelism khớp với số lượng CPU trên máy chủ. Tuy nhiên, có một điểm mà việc thực thi truy vấn theo 16 hoặc 32 chunk song song và hợp nhất các kết quả chậm hơn nhiều so với việc chạy nó trong một tiến trình duy nhất. Nếu bạn đang sử dụng phiên bản 16 hoặc 32 lõi, bạn có thể đặt giá trị max degree of parallelism thành 8 bằng cách sử dụng lệnh T-SQL sau:

USE Master
GO
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'max degree of parallelism', 8
GO
RECONFIGURE WITH OVERRIDE
GO

Cài đặt “max server memory”

Cài đặt này mặc định là một con số rất lớn, nhưng bạn nên thiết lập nó thành số megabyte RAM vật lý có sẵn, trừ đi vài gigabyte cho hệ điều hành và các ứng dụng khác.

Ví dụ: lệnh T-SQL sau đây điều chỉnh max server memory thành 100 GB. Sửa đổi nó để điều chỉnh giá trị phù hợp với phiên bản của bạn.

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OVERRIDE
GO
exec sp_configure 'max server memory', 100000
GO
RECONFIGURE WITH OVERRIDE
GO

Xem lại tài liệu tùy chọn cấu hình máy chủ bộ nhớ Máy chủ để biết thêm thông tin.

Kết thúc

Khởi động lại SQL Server một lần nữa để đảm bảo tất cả các cài đặt mới có hiệu lực. Hệ thống SQL Server của bạn đã được cấu hình và bạn đã sẵn sàng tạo cơ sở dữ liệu của riêng mình và bắt đầu kiểm tra workload cụ thể của mình.