Monitor SQL Server 2014 – Lab SQL Server 2014 P6

A. Activity Monitor:

1. xem hoạt động SQL server: vào SSMS phải chuột vào Server chọn Activity Monitor

Xuất hiện màn hình Activity Monitor

Chọn Processes để xem các tiến trình đang làm việc

Có thể click vào icon filter của từng column để lọc lại các tiến trình cần xem

Chọn Resource waits xem tiến trình đang chờ xử lý

Chọn Data File I/O xem thông tin các data file đang xử lý

Recent Expensive Queries xem danh sách các lệnh truy vấn đang chiếm dụng tài nguyên

2. Khắc phục sự cố blocked process: giả lập sự cố bằng cách phải chuột vào file Activityworkload.cmd trong folder module 7 chọn Run as administrator. Trở lại SSMS phải chuột vào table Production.Product của database AdventureWorks chọn Select Top 1000 Rows

Xem thanh status bar ở phía dưới cửa sổ query, lệnh truy vấn vẫn tiếp tục thực thi (Executing query) chưa hoàn tất.

Để khắc phục trường hợp này, trở về cửa sổ Activity Monitor chọn processes, tại cột task state chọn filter và chọn Suspended.

Xem và ghi chú lại ID tại cột Blocked By của tiến trình Suspended

Bỏ filter ở cột task state xuất hiện tất cả các tiến trình, tìm giá trị ID tại cột Blocked By đã xem ở bước trên (có 1 tiến trình không thể hoàn tất). Lúc này hãy tìm tiến trình nào có giá trị =1 tại cột Head Blocker (tiến trình này là nguyên nhân làm cho tiến trình suspended đã xem ở trên), phải chuột vào tiến trình này chọn Details

ấn Kill process Yes

Kiểm tra lại không còn tiến nào Suspended

Trở lại cửa sổ Select Top 1000 Rows, kiểm tra lại query đã hoàn tất.

1. Sử dụng Performance Monitor Vào server manager chọn tools performamce monitor, Tại cửa sổ Performamce Monitor ấn nút Add trên thanh toolbar (nút dấu + màu xanh), tại cửa sổ add counters, tìm và chọn %processor time trong processor object ấn Add, tìm và chọn %page Faults/sec trong Memory object ấn Add, tìm và chọn Average Wait Time (ms), lock Requests/sec, Lock Waits/sec trong SQLServer:Lock object ấn Add

Chọn Cache Hit Ratio trong SQLServer:Plan Cache object ấn add,chọn Transactions trong SQLServer:Transactions object ấn add OK

Xem biểu đồ dạng line. Có thể chuyển xem biểu đồ dạng Histogram bar hay report, bằng cách chọn trên thanh công cụ Change Graph Type (bên trái nút dấu + màu xanh)

2. Tạo Data Collector Set: Tại cửa sổ Performance Monitor chọn Data Collector Sets, phải chuột vào User defined new Data collector set. Tại cửa sổ Create new Data Collector set, trong phần name đặt tên SQL server Workload, chọn Option Create manually(Advanced) next

Chọn option create data logs và check vào checkbox Performance counter next, trong phần performance counters ấn add

Chọn các counter như trong hình next

Trong phần root directory ấn Browse chon nơi lưu trữ log file (vd: f:\Logs1) next

Chọn option Save and close Finish, Phải chuột vào SQL server Workload vừa tạo chọn start. Để có thông tin test thử bạn vào folder module 7 phải chuột vào baseline.ps1 file chọn Run with Powershell ấn Y. vào đĩa F:\Fogs1 double click vào file log để xem kết quả.

Kết quả ghi nhận được.

3. Data Collection Reports: ngoài performance monitor bạn có thể dùng SQL Server Data Collector là một chức năng có sẳn trong SSMS để thu thập các số liệu trên nhiều SQL server. Để sử dụng được Data Collector bạn phải start SQL server Agent và Data Warehouse management phải được cấu hình trước.

• • Vào SSMS, tại cửa sổ Object Explorer chọn Management, phải chuột vào Data Collection chọn Reports chọn report cần xem như Server Activity History, Query Statistics History, hay Disk Usage Summary.
  • Bạn cũng có thể Phải chuột vào SQL server Instance (tên server SQL) chọn Reports Standard report chọn report cần xem

Hay phải chuột vào tên database chọn Reports Standard report chọn report cần xem.

C. Dynamic Management Views and Functions:

1. View SQL Server Service Configuration Settings: mở file DMVs.sql trong folder module 7. Đánh dấu khối lệnh trong View service information ấn F5 xem thông tin về services.

–View service information

SELECT * FROM sys.dm_server_services

Đánh dấu khối lệnh trong View registry information ấn F5 xem thông tin registry

–View registry information

SELECT * FROM sys.dm_server_registry

2. View Storage Volume Statistics: đánh dấu khối các lệnh trong phần View volume stats ấn F5 xem thông tin về nơi lưu trữ database.

— View volume stats

SELECT d.name database_name,

f.name logical_filename,

s.volume_mount_point volume,

s.total_bytes volume_size,

s.available_bytes free_space,

f.size current_file_size

FROM sys.sysdatabases d

JOIN sys.master_files f ON d.dbid = f.database_id

CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id) s

3. View Query Statistics: đánh dấu khối các lệnh trong Empty the cache ấn F5

–Empty the cache

DBCC FREEPROCCACHE

Tiếp tục đánh dấu khối các lệnh trong get query stats ấn F5

–Get query stats

SELECT qt.[text] SQLText, qs.execution_count, qs.creation_time, qs.last_execution_time, qs.last_elapsed_time, qs.max_elapsed_time, qs.total_elapsed_time

FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt

ORDER BY qs.total_elapsed_time DESC

Tiếp tục đánh dấu khối các lệnh trong Execute a query ấn F5

–Execute a query

SELECT c.AccountNumber, SUM(UnitPrice * OrderQty) AS OrderTotal

FROM AdventureWorks.Sales.SalesOrderDetail AS SOD

JOIN AdventureWorks.Sales.SalesOrderHeader AS SOH ON SOD.SalesOrderID = SOH.SalesOrderID

JOIN AdventureWorks.Sales.Customer AS C ON SOH.CustomerID = C.CustomerID

GROUP BY C.AccountNumber

ORDER BY OrderTotal DESC

GO

Xem kết quả

Tiếp tục đánh dấu khối các lệnh trong get query stats ấn F5 lần nữa chú ý các giá trị trong cột SQLtext và execution_count

–Get query stats

SELECT qt.[text] SQLText, qs.execution_count, qs.creation_time, qs.last_execution_time, qs.last_elapsed_time, qs.max_elapsed_time, qs.total_elapsed_time

FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt

ORDER BY qs.total_elapsed_time DESC

Tiếp tục đánh dấu khối các lệnh trong Execute a query ấn F5 lần nữa

–Execute a query

SELECT c.AccountNumber, SUM(UnitPrice * OrderQty) AS OrderTotal

FROM AdventureWorks.Sales.SalesOrderDetail AS SOD

JOIN AdventureWorks.Sales.SalesOrderHeader AS SOH ON SOD.SalesOrderID = SOH.SalesOrderID

JOIN AdventureWorks.Sales.Customer AS C ON SOH.CustomerID = C.CustomerID

GROUP BY C.AccountNumber

ORDER BY OrderTotal DESC

GO

Tiếp tục đánh dấu khối các lệnh trong get query stats ấn F5 lần thứ 3 chú ý các giá trị trong cột SQLtext và execution_count

–Get query stats

SELECT qt.[text] SQLText, qs.execution_count, qs.creation_time, qs.last_execution_time, qs.last_elapsed_time, qs.max_elapsed_time, qs.total_elapsed_time

FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt

ORDER BY qs.total_elapsed_time DESC