Giám Sát Hoạt Động SQL Server – Lab SQL Server 2014 P7

A. Sử dụng SQL Server Profiler tạo trace và xem kết quả:

Tại SSMS, vào menu Tools chọn SQL server profiler, khi SQL server profiler khởi động kết nối vào SQL server (Ser100).Tại cửa sổ Trace Properties chọn tab General đặt tên Trace name: Demo trace, Chọn Use the template: TSQL, check vào save to file và chọn nơi lưu trữ F:\Module 8\Demo trace.trc check vào Enable file rollover.

Qua tab Events Selection, check vào show all events, tìm và check SQL:stmtcompleted

Bỏ check show all events, check vào show all columns, Tại cột Duration check vào checkbox tương ứng với dòng SQL:StmtCompleted, click vào cột Database Name xuất hiện cửa sổ Edit Filter

Tại cửa sổ Edit Filter, chọn DatabaseName bên trái, chọn dấu + Like nhập vào AdventureWorks OK. Bỏ check show all columns ấn Run

Trở về SSMS. Mở file Query.sql trong folder module 8 và ấn F5 để thực thi query này (query này sẽ truy vấn AdventureWorks database 20 lần để tạo trace), chờ cho quá trình thực thi query kết thúc, trở lại SQL Server Profiler vào menu file chọn stop trace, tìm và chọn sự kiện SQL:stmtCompleted bất kỳ trong cửa sổ trace, xem lệnh truy vấn ở cuối cửa sổ. Giữ nguyên cửa sổ SQL server profiler để thực hiện phần lab kế tiếp

B. Sử dụng SQL Trace:

Tại cửa sổ SQL server profiler, vào menu file chọn Export Script trace Definition For SQL Server 2005-2014 lưu file script là DemoTrace.sql trong folder module 8. Trờ lại SSMS mở file DemoTrace.sql vừa lưu. Tìm đoạn code bắt đầu bằng exec @rc = sp_trace_create, đổi InsertFileNameHere thành F:\Demo Trace\SQLTraceDemo đây là file sẽ lưu lại kết quả trace. Sau đó ấn F5 để thực thi script này, và ghi lại giá trị TraceID ở cửa sổ Results

Trở về cửa sổ chứa file Query.sql đã mở trước đó, ấn F5 để thực thi query này(query này sẽ truy vấn AdventureWorks database 20 lần để tạo trace). Tiếp tục mở file StopTrace.sql trong module 8, sửa lại TraceID phù hợp với giá trị đã ghi lại ở bước trên, đánh dấu khối các lệnh trong phần Stop the trace ấn F5.

Trong phần View the trace, sửa lại đường dẫn trong lệnh Select thành F:\Demo Trace\SQLTraceDemo.trc, đánh dấu khối lệnh Select trong phần view the trace ấn F5, xem kết quả. Giữ nguyên cửa sổ SSMS để làm tiếp phần lab tiếp theo.

C. Sử dụng Database Engine Tuning Advisor:

1. Tạo tuning session và recommendation(kiến nghị):

Tại cửa sổ SQL server profiler, vào menu Tools chọn Database Engine Tuning Advisor (DTA), kết nối vào database server (Ser100). Tại cửa sổ Database Engine Tuning Advisor đặt tên cho session name: Demo Trace,trong phần Workload chọn option File, chọn file F:\Module 8\Demo trace.trc (file này là kết quả trong phần lab A), ở phần Database for workload analysis chọn AdventureWorks. ở phần Select databases and tables to tune: chọn AdventureWorks và click vào mũi tên bên phải của database này.

Chọn các table sau: Product, ProductCategory, ProductSubcategory, SalesOrderDetail SalesOrderHeade.

Qua tab Tuning Options, ấn Advanced Option, Chọn option Generate online recommendations where possible OK, trên thanh toolbar của Database Engine Tuning Advisor ấn Start Analysis. Chờ quá trình analysis kết thúc xem kết quả ở tab Recommendation xem các index mà DTA kiến nghị

Qua tab Report, trong phần Select report chọn Statement detail report, so sách giá trị 2 cột Current Statement Cost và Recommended Statement Cost (Cost là giá trị xác định số lượng công việc cần thiết mà SQL server dùng để thực thi query). Sau đó vào menu Actions, chọn Save Recommendations, lưu vào ổ đĩa:

F:\Module 8\DTA Recommendations.sql OK, đóng Database Engine Tuning Advisor

2. Xác nhận kiến nghị:

– Trở lại SQL Server Management Studio (SSMS), mở lại file query.sql, đánh dấu khối toàn bộ lệnh Select nhưng không đánh dấu khối lệnh Go 20 ở cuối lệnh, ấn F5 để thực thi query này một lần.

– Vào menu Query chọn Display Estimated Execution Plan, rê con trỏ vào lệnh select ở góc dưới bên trái cửa sổ query chú ý giá trị Estimated Subtree Cost hiển thỉ ở tooltip.

– Mở file DTA Recommendations.sql vừa save ở trên, ấn F5

– Trở lại cửa sổ query.sql, đánh dấu khối toàn bộ lệnh Select nhưng không đánh dấu khối lệnh Go 20 ở cuối lệnh, ấn F5 để thực thi query này một lần nữa.

– Vào menu Query chọn lại Display Estimated Execution Plan lần 2.

– Rê con trỏ vào lệnh select ở góc dưới bên trái cửa sổ query chú ý giá trị Estimated Subtree Cost hiển thỉ ở tooltip, so sách giá trị này với giá ban đầu.

D. Kết hợp Trace với Performance Data:

• • Double-click vào AWCounters.blg trong folder Mudule 8, để mở Performance Monitor và xem kết quả.
  • Trở lại SQL Server Profiler, mở file AWTrace.trc trong folder module 8 chú ý giá trị ở cột StartTime.
  • Tại SQL Server Profiler, vào menu File chọn Import Performance Data và mở file AWCounters.blg trong module 8
  • Tại cửa sổ Performance Counters Limit Dialogdialog chọn \\MIA-SQL Ok
  • Chọn thời gian trong đồ thị sẽ thời gian tương ứng trong cửa sổ trace và lệnh bên dưới cùng cửa sổ.

E. Xử lý Deadlocks:

• Tại SQL Server Profiler, vào menu File chọn New Trace, kết nối vào SQL server.
• Tại cửa sổ Trace Properties, đặt tên Trace name: Locks, Use the template chọn TSQL_Locks

• Qua tab Events selection, xem các sự kiện có sẳn trong template này, sau đó click vào cột databaseName

Tại cửa sổ Edit Filter ấn vào dấu + Like nhập vào AdventureWorks Ok, ấn Run

• Double-click vào file Deadlock.cmd trong folder module 8, sẽ mở ra 2 cửa sổ
• Khi 2 cửa sổ command tự đóng lại, trở lại SQL server profiler vào menu file chọn Stop trace.
• Trong cửa sổ locks trace tìm và chọn Deadlock graph, ở cuối màn hình sẽ xuất hiện Deadlock graph

• Vào menu File chọn export Extract SQL Server Events Extract Deadlock Events. Đặt tên file là Deadlock lưu trong folder module 8. Đóng SQL server profiler.
• Trở lại SQL Server Management Studio (SSMS), mở file Deadlocks_1.xdl vừa lưu. Xem deadlock graph trong SSMS.
• Rê và giữ mouse đến vòng tròn trong hình để xem deadlock, vì có một quá trình update vào table Production.Product và sau đó là table Sales.SpecialOffer, trong khi một tiến trình khác thì ngược lại