Trong nhiều trường hợp, các Index của SQL Server là những giải pháp tuyệt vời để giải quyết các vấn đề về hiệu năng của hệ thống.
Thêm vào đó, các giải pháp này thường rất rẻ và có khả năng cải thiện hiệu năng một cách kỳ diệu.
Trong bài viết này, tôi muốn chia sẻ một ví dụ về cách sử dụng SQL Server Turning Advisor, đây là một công cụ đi kèm với bộ cài SQL Server. Để sử dụng được công cụ Turning Advisor chúng ta sẽ cần sử dụng SQL Server Profiler để thu thập thông tin.
Công cụ này phân tích các câu truy vấn dữ liệu và đưa ra các khuyến cáo về các index, statistic và partition cho các bảng và các views trong cơ sở dữ liệu của bạn.
Turning Advisor được đính kèm với mọi bản SQL Server trừ phiên bản Express.
SQL Server Profiler là một công cụ tuyệt vời để chúng ta có thể theo dõi được các câu truy vấn dài và giám sát hiệu năng của database. Chúng ta cũng có thể sử dụng nó để giám sát các hoạt động của database vì mục đích an ninh.
SQL Server Profiler có thể sử dụng để giám sát các cơ sở dữ liệu quan hệ (Relational Databases) hoặc cơ sở dữ liệu đa chiều (Multidimensional databases).
SQL Server Profiler được đính kèm với mọi bản SQL Server trừ phiên bản Web và Express.
Để xác định được phiên bản database, bạn có thể sử dụng câu truy vấn sau:
1. Mở công cụ SQL Server Profiler
2. Để theo dõi được thông tin, vào File > New Trace:
3. Nhập thông tin đăng nhập tới SQL Server
4. Ở Tab General, nhập thông tin về Trace name, và chọn loại Template là "Turning"
5. Bạn có thể chọn lưu trữ file trace thành một file hoặc lưu trữ thành bảng ở trên SQL Server. Lưu trữ trên file sẽ tiện hơn nhưng lưu trữ thành bảng sẽ hữu dụng hơn nếu bạn có nhu cầu sử dụng một số nghiệp vụ tracking nâng cao. Trong ví dụ này, tôi chọn lưu trữ vào một file.
6. Sau khi chọn xong các thông tin về cấu hình trace, bạn có thể bấm "run" để bắt đầu việc theo dõi.
7. Bước tiếp theo, bạn mở SQL Server Managerment Studio (SSMS), bảng mà chúng ta sẽ làm việc là Person.Person trong database Adventurework.
8. Để mô phỏng việc thiếu index trong bảng Person.Person, chúng ta sẽ xóa một index hiện có của bảng. Index cần xóa có tên là: [IX_Person_LastName_FirstName_MiddleName]
9. Bây giờ, chúng ta sẽ chạy một vài lệnh truy vấn dữ liệu như sau để lấy dữ liệu trong bảng Person.Person:
10. Đây là mô phỏng để tìm hiểu làm thế nào chúng ta có thể làm việc với công cụ Turning Advisor.
Trên thực tế, bạn sẽ chạy các Profile trace và người dùng sẽ làm việc trên các nghiệp vụ hàng ngày của database, công cụ Profiler sẽ lưu trữ những truy vấn với các hoạt động thực tế. Vào cuối ngày, các file trace sẽ được lưu lại để dùng cho việc giám sát database. Trong SQL Server Profiler, bạn có thể dừng việc theo dõi như sau:
11. Nếu bạn để ý và kiểm tra trường TextData, bạn sẽ nhìn thấy có rất nhiều các truy vấn nội bộ trong hệ thống được sinh ra khi bạn sử dụng SQL Server Profiler. Công cụ này sẽ lưu trữ tất cả các hoạt động của SQL, bạn có thể chỉ lọc nhưng dữ liệu cần thiết để giảm được tải:
12. Bạn có thể tìm thấy thông tin về câu lệnh truy vấn mà mình đã thực hiện ở bước 9. Thời gian để thực thi câu lệnh là 63 ms, ProcessID (SPID) là 58:
13. Hiện tại, dữ liệu được lưu trữ trong trace file đã đủ, chúng ta hãy cùng mở công cụ Database Engine Turing Advisor và đăng nhập vào SQL Server:
14. Mặc định, tên của phiên (session name) sẽ là username và thời gian:
Bạn hãy chọn file trace mà bạn đã lưu, chọn tên database cần làm việc là [AdventureWorks] như hình 12.
15. Ở tab "Turning Options", bạn có thể cấu hình một số lựa chọn như là thời gian dừng việc phân tích. Nếu lượng dữ liệu cần phân tích là lớn, thời gian cần để phân tích dữ liệu có thể lên đến vài giờ, đó chính là lý do bạn nên hạn chế thời gian phân tích dữ liệu.
Bạn cũng có thể lựa chọn chỉ phân tích các Index, các index view hoặc Nonclustered Indexes.
Bạn cũng có thể lựa chọn phân tích về chiến lược phân chia partition hoặc giữ lại các Physical design structure.
16. Khi mọi thứ đã sẵn sàng, bạn có thể bắt đầu việc phân tích:
17. Khi Turning Advisor chạy xong, bạn có thể nhìn thấy các khuyến nghị. Ở trong ví dụ này ta có thể nhìn thấy rằng, nếu thực hiện theo khuyến nghị hiệu năng của hệ thống có thể được cải thiện tới 97%. Cụ thể ở đây, Turning Advisor khuyên ta nên tạo thêm 02 indexes:
18. Ở trong tab "Reports", bạn có thể theo dõi được khá nhiều các loại báo cáo. "Statement cost report" sẽ cho ta biết các câu lệnh T-SQL và tỉ lệ phần trăm hiệu năng được cải thiện khi chúng ta áp dụng khuyến nghị:
19. Báo cáo "Event frequency report" cho thấy tần suất mà các truy vấn được sử dụng. Điều này là rất quan trọng vì nếu chúng ta sử dụng quá nhiều các index sẽ có thể làm giảm hiệu năng của hệ thống. Do vậy, bạn không thể áp dụng tất cả các khuyến nghị, bạn cần phải phân tích các báo cáo và lựa chọn các khuyến nghị một cách khôn ngoan, chỉ nên áp dụng khuyến nghị với các truy vấn thường xuyên được sử dụng nhất.
20. Báo cáo "Statement detail report" cho chúng ta thấy chi phí hiện tại và chi phí sau khi áp dụng khuyến nghị. Chi phí càng thấp thì hiệu suất càng cao:
21. Ngoài những báo cáo trên, còn khá nhiều các báo cáo mà bạn có thể xem xét để có thể đưa ra quyết định xem là mình sẽ áp dụng những khuyến nghị nào.
22. Ở "Actions menu" bạn có thể lựa chọn áp dụng các khuyến nghị hoặc lưu các khuyến nghị thành các sql scripts.
23. Chúng ta sẽ thực hiện lưu các khuyến nghị thành một sql script.
24. Mở file sql đã được lưu ở bước 23 và thực thi chúng trên SSMS:
25. Bây giờ, bạn hãy chạy lại câu lệnh truy vấn đã thực hiện ở bước 9 trên SSMS. Bạn hãy bật chức năng "Actual Execution Plan" bằng cách bấm tổ hợp phím Ctrl + M hoặc bấm vào nút như hình sau:
26. Sau khi chạy lệnh truy vấn, bạn có thể nhìn thấy Index được tạo ở bước 24 đã được sử dụng để phục vụ cho câu lệnh truy vấn:
Đến đây, bạn đã có thể nhận ra Turning Advisor là một công cụ rất hữu hiệu và sử dụng một cách đơn giản. Bạn chỉ việc lưu trữ các câu lệnh truy vấn tới database vào trong các file trace hoặc các bảng, sau đó đưa chúng vào Turning Advisor để phân tích. Ngoài ra, bạn còn có thể sử dụng Turning Advisor để phân tích các câu lệnh đang đươc lưu trữ ở trong Plan cache.
Tuy nhiên, những khuyến nghị mà Turning Advisor đưa ra cũng cần phải được bạn xem xét một cách cẩn thận trước khi áp dụng.
Thêm vào đó, các giải pháp này thường rất rẻ và có khả năng cải thiện hiệu năng một cách kỳ diệu.
Trong bài viết này, tôi muốn chia sẻ một ví dụ về cách sử dụng SQL Server Turning Advisor, đây là một công cụ đi kèm với bộ cài SQL Server. Để sử dụng được công cụ Turning Advisor chúng ta sẽ cần sử dụng SQL Server Profiler để thu thập thông tin.
Database Engine Tuning Advisor
Turning Advisor là một công cụ được sử dụng để tìm ra các khuyến cáo về việc áp dụng các index, statistic và partition trong SQL Server. Các khuyến cáo này dựa trên thông tin từ các tập lệnh sql hoặc từ một file xml hoặc từ một file Profile trace.Công cụ này phân tích các câu truy vấn dữ liệu và đưa ra các khuyến cáo về các index, statistic và partition cho các bảng và các views trong cơ sở dữ liệu của bạn.
Turning Advisor được đính kèm với mọi bản SQL Server trừ phiên bản Express.
SQL Server Profiler
Đây là một công cụ cho phép bạn ghi lại các hoạt động hàng ngày của cơ sở dữ liệu. SQL Server Profiler sẽ lưu trữ các câu truy vấn T-SQL mà chúng ta sẽ dùng làm dữ liệu đầu vào cho công cụ Turning Advisor.SQL Server Profiler là một công cụ tuyệt vời để chúng ta có thể theo dõi được các câu truy vấn dài và giám sát hiệu năng của database. Chúng ta cũng có thể sử dụng nó để giám sát các hoạt động của database vì mục đích an ninh.
SQL Server Profiler có thể sử dụng để giám sát các cơ sở dữ liệu quan hệ (Relational Databases) hoặc cơ sở dữ liệu đa chiều (Multidimensional databases).
SQL Server Profiler được đính kèm với mọi bản SQL Server trừ phiên bản Web và Express.
Để xác định được phiên bản database, bạn có thể sử dụng câu truy vấn sau:
SELECT SERVERPROPERTY('Edition') AS Edition
Nào, chúng ta cùng bắt đầu
Dùng công cụ SQL Server Profiler chúng ta sẽ thực hiện lưu giữ lại các hoạt động của database vào một file theo dõi (trace file). File này sẽ được phân tích bởi công cụ Turning Advisor nhằm đưa ra được các khuyến cáo.1. Mở công cụ SQL Server Profiler
 |
| Ảnh 1: Mở SQL Server Profiler |
 |
| Ảnh 2: Tạo một theo dõi mới |
 |
| Ảnh 3: Đăng nhập SQL Server |
 |
| Ảnh 4: Cấu hình theo dõi |
 |
| Ảnh 5: Lưu trữ file trace |
7. Bước tiếp theo, bạn mở SQL Server Managerment Studio (SSMS), bảng mà chúng ta sẽ làm việc là Person.Person trong database Adventurework.
 |
| Ảnh 6: Bảng Person.Person |
USE [AdventureWorks]
GO
DROP INDEX [IX_Person_LastName_FirstName_MiddleName] ON [Person].[Person]
GO
SELECT BusinessEntityID, FirstName, MiddleName, LastName, ModifiedDate
from Person.Person
where LastName = 'Sullivan'
SELECT COUNT(*) FROM Person.Person
10. Đây là mô phỏng để tìm hiểu làm thế nào chúng ta có thể làm việc với công cụ Turning Advisor.
Trên thực tế, bạn sẽ chạy các Profile trace và người dùng sẽ làm việc trên các nghiệp vụ hàng ngày của database, công cụ Profiler sẽ lưu trữ những truy vấn với các hoạt động thực tế. Vào cuối ngày, các file trace sẽ được lưu lại để dùng cho việc giám sát database. Trong SQL Server Profiler, bạn có thể dừng việc theo dõi như sau:
 |
| Ảnh 7: Dừng theo dõi |
 |
| Ảnh 8: Thông tin về các câu lệnh theo dõi được |
 |
| Ảnh 9: Thông tin về câu lệnh truy vấn đã thực hiện ở bước 9 |
 |
| Ảnh 10: Mở công cụ Database Engine Turning Advisor |
 |
| Ảnh 11: Kết nối vào database |
 |
| Ảnh 12: Thông tin phiên |
15. Ở tab "Turning Options", bạn có thể cấu hình một số lựa chọn như là thời gian dừng việc phân tích. Nếu lượng dữ liệu cần phân tích là lớn, thời gian cần để phân tích dữ liệu có thể lên đến vài giờ, đó chính là lý do bạn nên hạn chế thời gian phân tích dữ liệu.
Bạn cũng có thể lựa chọn chỉ phân tích các Index, các index view hoặc Nonclustered Indexes.
Bạn cũng có thể lựa chọn phân tích về chiến lược phân chia partition hoặc giữ lại các Physical design structure.
 |
| Ảnh 13: Thông tin về cấu hình phân tích |
 |
| Ảnh 14: Bắt đầu phân tích |
 |
| Ảnh 15: Khuyến nghị của công cụ Turning Advisor |
18. Ở trong tab "Reports", bạn có thể theo dõi được khá nhiều các loại báo cáo. "Statement cost report" sẽ cho ta biết các câu lệnh T-SQL và tỉ lệ phần trăm hiệu năng được cải thiện khi chúng ta áp dụng khuyến nghị:
 |
| Ảnh 16: Statement cost report |
19. Báo cáo "Event frequency report" cho thấy tần suất mà các truy vấn được sử dụng. Điều này là rất quan trọng vì nếu chúng ta sử dụng quá nhiều các index sẽ có thể làm giảm hiệu năng của hệ thống. Do vậy, bạn không thể áp dụng tất cả các khuyến nghị, bạn cần phải phân tích các báo cáo và lựa chọn các khuyến nghị một cách khôn ngoan, chỉ nên áp dụng khuyến nghị với các truy vấn thường xuyên được sử dụng nhất.
 |
| Ảnh 17: Event frequency report |
20. Báo cáo "Statement detail report" cho chúng ta thấy chi phí hiện tại và chi phí sau khi áp dụng khuyến nghị. Chi phí càng thấp thì hiệu suất càng cao:
 |
| Ảnh 18: Statement detail report |
21. Ngoài những báo cáo trên, còn khá nhiều các báo cáo mà bạn có thể xem xét để có thể đưa ra quyết định xem là mình sẽ áp dụng những khuyến nghị nào.
 |
| Ảnh 19: Các báo cáo của công cụ Turning Advisor |
22. Ở "Actions menu" bạn có thể lựa chọn áp dụng các khuyến nghị hoặc lưu các khuyến nghị thành các sql scripts.
 |
| Ảnh 20: Actions Menu |
23. Chúng ta sẽ thực hiện lưu các khuyến nghị thành một sql script.
 |
| Ảnh 21: Lưu các khuyến nghị thành sql script |
24. Mở file sql đã được lưu ở bước 23 và thực thi chúng trên SSMS:
use [AdventureWorks]
go
SET ANSI_PADDING ON
go
CREATE NONCLUSTERED INDEX [_dta_index_Person_28_1765581328__K7_1_5_6_13] ON [Person].[Person]
(
[LastName] ASC
)
INCLUDE ( [BusinessEntityID],
[FirstName],
[MiddleName],
[ModifiedDate]) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
go
CREATE NONCLUSTERED INDEX [_dta_index_Person_28_1765581328__K3] ON [Person].[Person]
(
[NameStyle] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
go
 |
| Ảnh 22: Bật chức năng Actual Execution Plan |
 |
| Ảnh 23: Execution Plan |
Đến đây, bạn đã có thể nhận ra Turning Advisor là một công cụ rất hữu hiệu và sử dụng một cách đơn giản. Bạn chỉ việc lưu trữ các câu lệnh truy vấn tới database vào trong các file trace hoặc các bảng, sau đó đưa chúng vào Turning Advisor để phân tích. Ngoài ra, bạn còn có thể sử dụng Turning Advisor để phân tích các câu lệnh đang đươc lưu trữ ở trong Plan cache.
Tuy nhiên, những khuyến nghị mà Turning Advisor đưa ra cũng cần phải được bạn xem xét một cách cẩn thận trước khi áp dụng.
Nhận xét
Đăng nhận xét