Monitor SQL Server 2014 – Lab SQL Server 2014 P6

Phần lớn thời gian của người quản trị database (DBA) là giám sát database và database server, mục đích là thu thập các số liệu dùng chuẩn đoán và theo dõi việc sử dụng tài nguyên của hệ thống, để có những quyết định can thiệp xử lý phù hợp kịp thời, nhầm đảm bảo cho hệ thông làm việc nhanh chóng và ổn định. SQL server cung cấp rất nhiệu công cụ dùng để giám sát các hoạt động hiện tại và ghi lại chi tiết các hoạt động trong khoảng thời gian trước đó.

Trong bài lab này hướng dẫn các bạn sử dụng các công cụ như Activity Monitor, dynamic management views and functions (DMVs and DMFs), Performance Monitor để giam sát hoạt động của SQL server.

Thực hiện xong bài lab này, bạn có thể sử dụng các công cụ giám sát SQL server.

Thực Hiện Monitor SQL Server 2014

A. Activity Monitor:

A. Activity Monitor:

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

Monitor SQL Server 2014

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

Monitor SQL Server 2014

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

Monitor SQL Server 2014

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

Monitor SQL Server 2014

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

Monitor SQL Server

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

Monitor SQL Server

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

Monitor SQL Server 2014

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

Monitor SQL Server 2014

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.

picture011

Để 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.

picture012

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

picture013

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

picture015

ấn Kill process Yes

picture016

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

picture018

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

picture019

B. Performance Monitor:

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

picture005

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

picture007

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)

picture010

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
picture014

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

picture015

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

picture023

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ả.

picture029

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

picture036

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:

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

picture002

Đá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

picture004

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

picture006

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ả

picture009

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

picture010

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

picture012

Lab SQL Server 2014 phần 7

Mr CTL – ctl.vn



Leave a Reply