Câu hỏi ADO.NET gọi thủ tục lưu trữ T-SQL gây ra một SqlTimeoutException


Tôi có một thủ tục lưu trữ T-SQL với chữ ký

CREATE PROCEDURE MyProc
@recordCount INT OUTPUT
@param1 INT
...

Khi được thực hiện trực tiếp trong Sql Server, quy trình chạy trong vòng 5 giây, trả về một vài tập kết quả có tổng cộng khoảng 100 hàng.

Gọi thủ tục này bằng cách sử dụng ADO.NET SqlDataAdapter.Fill phương pháp để điền một Dataset gây ra một SqlTimeoutException trên SqlCommand sau 3 phút (khoảng thời gian chờ đã chỉ định).

Thay đổi thủ tục lưu trữ sao cho nó không còn có tham số đầu ra, và giá trị đầu ra được yêu cầu được trả về như tập kết quả cuối cùng, giải quyết vấn đề và toàn bộ hoạt động trong dưới 5 giây như mong đợi.

Nhưng tại sao?

Tôi không muốn đi qua cơ sở mã của tôi và sửa đổi tất cả các trường hợp của loại hành vi này mà không hiểu nếu tôi đã thực sự giải quyết vấn đề.

Một điều cần lưu ý là điều này chỉ rõ ràng trên một máy chủ cụ thể, mà phải thừa nhận có một tập dữ liệu lớn hơn các cơ sở dữ liệu tương tự khác mà chúng tôi chạy. Chắc chắn không phải là một thiết lập Sql Server?

CẬP NHẬT

Bước vào nguồn khung sự cố xuất hiện trong quá trình truy xuất siêu dữ liệu. Các ConsumeMetaData phương pháp của SqlDataReader đối tượng bị treo vô thời hạn. Tuy nhiên tôi chạy thử nghiệm trên cơ sở dữ liệu khác và không thể sao chép, do đó, nó là một vấn đề cơ sở dữ liệu cụ thể khi thủ tục này được gọi là mặc dù ADO.NET ... Tuyệt vời.

CẬP NHẬT II

Đã xác nhận sự cố vẫn xảy ra nếu tôi thay đổi mã để sử dụng OleDbDataAdapter với các kiểu nhà cung cấp SQLOLEDB hoặc SQLNCLI. Chắc chắn để làm với kết nối.


29
2018-05-07 11:25


gốc


Điều này có hiệu quả nếu bạn sử dụng một bộ dữ liệu không? Bạn đã xem xét hồ sơ sql trong khi nó bị mắc kẹt trong sqldataadapter.fill? - Sam Saffron
Cùng một thỏa thuận với một SqlDataReader. Đã lược tả nó là tốt. Tương tác tôi thấy nó mất dưới 5 giây thời gian, chính xác cùng một cuộc gọi thông qua ADO.NET profiler cho thấy nó mất thời gian chờ (180000ms). Phương thức Fill đang treo và giữ cho đến khi nó kết thúc. - crowleym


Các câu trả lời:


Khi tôi đã xác định rằng đó là kết nối ADO.NET ở gốc của vấn đề, điều này sợi chỉ dẫn tôi đến câu trả lời.

Về cơ bản các kết nối thông qua Sql Server Management Studio (SSMS) theo mặc định có SET ARITHABORT ON. ADO.NET kết nối không.

Cài đặt ARITHABORT OFF và thực hiện truy vấn trực tiếp thông qua SSMS cho tôi thời gian phản hồi chậm tương tự.

Sự khác biệt chính khi chạy có hoặc không có cài đặt này là một kế hoạch truy vấn khác được tạo cho hai cuộc gọi. Khi nào ARITHABORT là OFF, lệnh SSMS sẽ sử dụng kế hoạch truy vấn được lưu trữ trước khi được lưu trữ mà kết nối ADO.NET đang sử dụng và do đó hết thời gian chờ.

Bằng cách chạy các lệnh sau với tư cách là quản trị viên trên cơ sở dữ liệu, tất cả truy vấn chạy như mong đợi bất kể ARITHABORT cài đặt.

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

Tôi chỉ có thể giả định một kế hoạch truy vấn được biên dịch đã bị hỏng hoặc không hợp lệ.

Tôi sẽ đi với điều này như là giải pháp (tôi đã lên bình chọn câu trả lời) trên khác sợi chỉ

Cảm ơn.


43
2018-05-08 09:45



Cảnh báo Câu trả lời này chỉ là một sửa chữa cực kỳ ngắn hạn và hoàn toàn không cần thiết tàn bạo. DBCC DROPCLEANBUFFERS sẽ thả hầu hết các trang dữ liệu từ bộ nhớ cache và có không có hiệu lực. DBCC FREEPROCCACHE sẽ tuôn ra toàn bộ bộ nhớ cache thủ tục tất cả chỉ để loại bỏ một kế hoạch có vấn đề! Không có gì đảm bảo rằng vấn đề sẽ không tái diễn ở một số giai đoạn trong tương lai. Vấn đề là tham số sniffing. Xin vui lòng xem bài viết này để giải thích đầy đủ hơn. - Martin Smith
@MartinSmith: Tôi đã đọc qua bài viết đó, và có một thời gian khá khó khăn thực sự grokking nó. Bạn có thể cung cấp giải thích cho những gì mọi người có thể làm để khắc phục sự cố này mà không cần chạy hai dòng mã này và mọi người có thể làm gì để tránh điều này xảy ra trong tương lai không? - StriplingWarrior
@StriplingWarrior một bài viết hay khác ở đây thảo luận về một loạt các biện pháp phòng ngừa có thể sqlperformance.com/2013/08/t-sql-queries/… - Martin Smith
sử dụng VỚI RECOMPILE; về thủ tục lưu sẵn. ĐỌC: sqladvice.com/blogs/gstark/archive/2008/02/12/… - Shiham


Tôi đã sửa chữa - vâng, bạn CÓ THỂ có cả hai - một tham số OUTPUT cũng như một tập hợp các hàng được trả về. Bạn học được điều gì mới mỗi ngày :-)

Về lý do tại sao một thời gian chờ xảy ra - hmm .... khó nói. Một mẫu nhỏ nhanh chóng làm việc tốt cho tôi. Bạn có thể đăng bài của bạn được lưu trữ proc (ít nhất là có liên quan bit của nó)?

Có bao nhiêu hàng chúng ta đang nói đến, được trả lại ở đây?

Tại thời điểm nào trong proc được lưu trữ của bạn, bạn có tính toán số hàng mà bạn cần trả lại dưới dạng tham số OUTPUT không?

Điều gì sẽ xảy ra nếu bạn cố gắng thêm thông số khác MaxRows để một SProc của bạn như một thử nghiệm và làm một SELECT TOP (@MaxRows)....... trên dữ liệu của bạn? Điều đó có trở lại nhanh chóng không?

Marc


1
2018-05-07 11:34



Bạn có chắc chắn về điều này, tôi có thể thề Ive sử dụng params đầu ra với tập hợp kết quả trước khi - Sam Saffron
Như tôi đã nói tôi chạy tương tác không có vấn đề. - crowleym
@marc_s Khi câu hỏi cho biết, khoảng 100 hàng được trả về (nghĩa là tối thiểu). Một lần nữa như câu hỏi nói, tất cả điều này hoạt động tương tác trong một vài giây. Chỉ khi được gọi mặc dù ADO.NET chúng ta mới gặp vấn đề. Các thủ tục hoạt động tốt, trừ khi bạn gọi nó mặc dù ADO.NET để cư một DataSet. Tôi đang bước qua mã nguồn cho SqlDataAdapter.Fill để xem nơi / tại sao nó treo cứng. Cảm ơn. - crowleym


Tóm lại - Tôi đã khắc phục vấn đề của mình bằng cách buộc SQL Server sử dụng chỉ mục thích hợp nhất để hạn chế số lần đọc lôgic của lob khi nó không thể tự tìm ra nó.

Trong dài -

Tôi chỉ gặp vấn đề này và giải quyết nó theo một cách khác sau khi thử tất cả các câu trả lời được đề xuất khác. Trong SSMS truy vấn đang chạy trong ~ 3s, nhưng đã hết thời gian khi được gọi từ ứng dụng web .Net MVC.

Đầu ra IO thống kê trong SSMS cho tôi biết rằng có hơn 195.500.000 lob đọc logic trên một bảng (bảng 20M với chỉ mục cột nhóm và cũng có chỉ mục hàng, nhưng không có cột "LOB"). Tôi nhận thấy từ kế hoạch thực hiện rằng một phần lớn tải (76%) đến từ một chỉ mục tìm kiếm một trong các chỉ số hàng. Tôi đã sử dụng như sau:

from [table] with (index([clustered columnstore index name]))

trong truy vấn của tôi để ép buộc việc sử dụng chỉ mục cột nhóm và truy vấn của tôi đã giảm xuống còn <1s và số lần đọc lôgic bị giảm xuống <6k từ> 195M và khi gọi SP từ ứng dụng web hiện tại, nó đang bị vấp ngã 1,3s.

Tôi đã thử tùy chọn biên dịch lại, thiết lập arithabort trên, sniffing tham số, và trong SQL Server cuối cùng chỉ không thể tìm ra chỉ mục để sử dụng. Đây là một trường hợp cạnh quá btw, và thời gian duy nhất tôi đã có để buộc một chỉ số trong cơ sở dữ liệu này.


0
2017-10-24 16:15