5 cách để tìm các truy vấn SQL chậm

5 cách để tìm các truy vấn SQL chậm

1. Tìm truy vấn chậm với SQL DMV

Một trong những tính năng tuyệt vời của SQL Server là tất cả các khung nhìn quản lý năng động (DMV) được tích hợp vào nó. Có hàng chục người trong số họ và họ có thể cung cấp nhiều thông tin về một loạt các chủ đề.

Có một số DMV cung cấp dữ liệu về số liệu thống kê truy vấn, kế hoạch thực hiện, truy vấn gần đây và hơn thế nữa. Chúng có thể được sử dụng cùng nhau để cung cấp một số thông tin chi tiết tuyệt vời.

Ví dụ, truy vấn dưới đây có thể được sử dụng để tìm các truy vấn sử dụng nhiều lần đọc, viết, thời gian công nhân (CPU), v.v.

CHỌN TOP 10 SUBSTRING (qt.TEXT, (qs.statement_start_offset / 2) +1,10 SUBSTRING ( qt . TEXT , ( qs . Statement_start_offset / 2 ) + 1 , 
((CASE qs.statement_end_offset(( Qs TRƯỜNG HỢP . Statement_end_offset
KHI NÀO -1 THEN DATALENGTH (qt.TEXT)- 1 THEN DATALENGTH ( qt . TEXT )
ELSE qs.statement_end_offset. statement_end_offset
END - qs.statement_start_offset) / 2) +1),END - qs . statement_start_offset ) / 2 ) + 1 ), 
qs.execution_count,. execution_count ,
qs.total_logical_reads, qs.last_logical_reads,. total_logical_reads , qs . last_logical_reads ,
qs.total_logical_writes, qs.last_logical_writes,. total_logical_writes , qs . last_logical_writes ,
qs.total_worker_time,. total_worker_time ,
qs.last_worker_time,. last_worker_time ,
qs.total_elapsed_time / 1000000 total_elapsed_time_in_S,. total_elapsed_time / 1000000 total_elapsed_time_in_S ,
qs.last_elapsed_time / 1000000 last_elapsed_time_in_S,. last_elapsed_time / 1000000 last_elapsed_time_in_S ,
qs.last_execution_time,. last_execution_time ,
qp.query_plan. query_plan
FROM sys.dm_exec_query_stats qs. dm_exec_query_stats qs
CROSS ÁP DỤNG sys.dm_exec_sql_text (qs.sql_handle) qt. dm_exec_sql_text ( qs . sql_handle ) qt
CROSS ÁP DỤNG sys.dm_exec_query_plan (qs.plan_handle) qp. dm_exec_query_plan ( qs . plan_handle ) qp
ĐẶT HÀNG CỦA qs.total_logical_reads DESC - lần đọc logic. total_logical_reads DESC - lần đọc logic
- ĐẶT HÀNG CỦA qs.total_logical_writes DESC - viết lôgic- ORDER BY qs . total_logical_writes DESC - ghi logic
- ĐẶT HÀNG CỦA qs.total_worker_time DESC - Thời gian CPU- ORDER BY qs . total_worker_time DESC - Thời gian của CPU

Kết quả của truy vấn sẽ trông giống như dưới đây. Hình ảnh dưới đây là từ một ứng dụng tiếp thị tôi đã thực hiện. Bạn có thể thấy rằng một truy vấn cụ thể (truy vấn hàng đầu) chiếm tất cả các tài nguyên.

Bằng cách xem xét điều này, tôi có thể sao chép truy vấn SQL đó và xem có cách nào để cải thiện nó hay không, thêm chỉ mục, v.v.

Ưu điểm: Luôn có sẵn số liệu thống kê rollup cơ bản. 
Nhược điểm: Không cho bạn biết những gì đang gọi các truy vấn. Không thể hình dung khi các truy vấn được gọi theo thời gian.

2. Báo cáo truy vấn thông qua các giải pháp APM

Một trong những tính năng tuyệt vời của nhiều  công cụ quản lý hiệu suất ứng dụng(APM) là khả năng theo dõi các truy vấn SQL. Ví dụ,  Retrace theo dõi truy vấn SQL trên nhiều nhà cung cấp cơ sở dữ liệu, bao gồm SQL Server.

Retrace có thể cho bạn biết số lần truy vấn đã được thực hiện, thời gian truy vấn trung bình là bao lâu và giao dịch nào đang gọi nó. Đây thực sự là thông tin có giá trị cho việc điều chỉnh hiệu năng SQL.

Các giải pháp APM thu thập dữ liệu này bằng cách thực hiện lược tả hiệu suất nhẹ đối với mã ứng dụng của bạn khi chạy.

Dưới đây là một ảnh chụp màn hình từ bảng điều khiển ứng dụng của Retrace hiển thị cho một ứng dụng cụ thể, truy vấn SQL nào mất nhiều thời gian nhất.

TRUY XUẤT LẠI CÁC TRUY VẤN SQL HÀNG ĐẦU

Retrace thu thập số liệu thống kê hiệu suất về mọi truy vấn SQL được thực thi. Bạn có thể tìm kiếm các truy vấn cụ thể để tìm kiếm các vấn đề tiềm ẩn.

XEM LẠI TẤT CẢ TRUY VẤN SQL

Bằng cách chọn một truy vấn riêng lẻ, bạn có thể xem tần suất truy vấn đó được gọi theo thời gian và mất bao lâu. Bạn cũng có thể xem những trang web nào sử dụng truy vấn SQL và hiệu suất của chúng bị ảnh hưởng như thế nào.

THEO DÕI HIỆU SUẤT SQL THEO THỜI GIAN

Kể từ khi Retrace là một trình biên dịch mã nhẹ và nắm bắt các dấu vết yêu cầu ASP.NET, nó thậm chí có thể cho bạn thấy bối cảnh đầy đủ của những gì mã của bạn đang làm.

Dưới đây là một dấu vết cho thấy tất cả các truy vấn SQL và các chi tiết khác về những gì mã đã làm. Retrace thậm chí có thể hiển thị các thông điệp tường trình trong cùng một khung nhìn này. Ngoài ra, lưu ý rằng nó cho thấy địa chỉ máy chủ và tên cơ sở dữ liệu truy vấn đang được thực hiện trên. Bạn cũng có thể xem có bao nhiêu hồ sơ được trả về.

TRUY XUẤT LẠI DẤU VẾT GIAO DỊCH WEB

Như bạn thấy, Retrace cung cấp khả năng báo cáo SQL toàn diện như là một phần của khả năng APM của nó . Nó cũng cung cấp nhiều tính năng giám sát và cảnh báo xung quanh các truy vấn SQL.

Ưu điểm: Báo cáo chi tiết trên ứng dụng, mỗi ứng dụng và mỗi truy vấn. Có thể hiển thị dấu vết giao dịch chi tiết cách truy vấn được sử dụng. Bắt đầu chỉ với $ 10 một tháng . Luôn chạy khi được cài đặt. 
Nhược điểm: Không cung cấp số lần đọc hoặc ghi cho mỗi truy vấn.

3. SQL Server Profiler (KHÔNG CẬP NHẬT!)

Các SQL Server Profiler đã được khoảng một thời gian rất dài. Nó rất hữu ích nếu bạn đang cố gắng để xem trong thời gian thực những gì các truy vấn SQL đang được thực hiện đối với cơ sở dữ liệu của bạn.

LƯU Ý: Microsoft đã thông báo rằng SQL Server Profiler không được chấp nhận!

SQL Profiler nắm bắt các sự kiện rất chi tiết về sự tương tác của bạn với SQL Server.

• Đăng nhập kết nối, ngắt kết nối và thất bại
• Câu lệnh SELECT, INSERT, UPDATE và DELETE
• Cuộc gọi trạng thái lô RPC
• Bắt đầu và kết thúc các thủ tục lưu sẵn
• Bắt đầu và kết thúc các câu lệnh trong một thủ tục lưu sẵn
• Bắt đầu và kết thúc một lô SQL
• Lỗi được ghi vào nhật ký lỗi SQL Server
• Khóa được mua hoặc phát hành trên một đối tượng cơ sở dữ liệu
• Một con trỏ đã mở
• Kiểm tra quyền bảo mật

SQL SERVER PROFILER

Hướng dẫn: SQL Server Profiler

Ưu điểm: Dữ liệu rất chi tiết có sẵn. 
Nhược điểm: Bạn phải bật nó theo cách thủ công. Điều này buộc bạn phải tạo lại một kịch bản mà bạn đang cố chụp. Nó cuối cùng sẽ biến mất trong lợi của sự kiện mở rộng.

---

---

4. Sự kiện mở rộng SQL Server

SQL Profiler đã được thay thế bằng các sự kiện mở rộng SQL Server . Điều này chắc chắn sẽ khiến nhiều người phải tức giận nhưng tôi có thể hiểu tại sao Microsoft lại làm điều đó.

Sự kiện mở rộng hoạt động qua Theo dõi sự kiện (ETW). Đây là cách phổ biến cho tất cả các công nghệ liên quan đến Microsoft để lộ dữ liệu chẩn đoán.

ETW cung cấp sự linh hoạt hơn nhiều. Là một nhà phát triển, tôi có thể dễ dàng khai thác các sự kiện ETW từ SQL Server để thu thập dữ liệu để sử dụng tùy chỉnh. Đó là thực sự mát mẻ và thực sự mạnh mẽ.

THÊM:  Giới thiệu sự kiện mở rộng SQL Server

Ưu điểm: Dễ dàng bật và chạy. Dễ dàng hơn để phát triển các giải pháp tùy chỉnh. 
Nhược điểm: Vì nó là khá mới, hầu hết mọi người có thể không nhận thức được nó.

---

---

5. Thông tin chi tiết về hiệu suất truy vấn SQL Azure

Tôi sẽ giả định rằng báo cáo hiệu suất của SQL Azure được xây dựng trên các Sự kiện mở rộng. Trong Azure Portal, bạn có thể truy cập vào một loạt các mẹo báo cáo và tối ưu hóa hiệu suất rất hữu ích.

Lưu ý: Các khả năng báo cáo này chỉ có sẵn cho các cơ sở dữ liệu được lưu trữ trên SQL Azure.

Trong ảnh chụp màn hình bên dưới, bạn có thể thấy cách SQL Azure giúp dễ dàng sử dụng các truy vấn của bạn sử dụng hầu hết CPU, IO dữ liệu và Đăng nhập IO. Nó có một số báo cáo cơ bản tuyệt vời được tích hợp vào nó.

TRUY VẤN HÀNG ĐẦU SQL AZURE

Bạn cũng có thể chọn một truy vấn riêng lẻ và nhận thêm chi tiết để trợ giúp điều chỉnh hiệu năng SQL.

CHI TIẾT TRUY VẤN SQL AZURE

Ưu điểm: Báo cáo cơ bản tuyệt vời. 
Nhược điểm: Chỉ hoạt động trên Azure. Không có báo cáo trên nhiều cơ sở dữ liệu.