問題描述
從 .NET 應用程序到 SQL Server 的偶爾緩慢的調用 (Sporadically Slow Calls From .NET Application To SQL Server)
我在 SQL Server 中有一個表，該表是從仍在生產中的遺留系統繼承而來的，該系統根據以下代碼進行結構化。我創建了一個 SP 來查詢表，如表創建語句下方的代碼中所述。我的問題是，通過 Enterprise Library 4 和 DataReader 對像從 .NET 到此 SP 的調用偶爾會很慢。SP 通過數據層中的循環結構調用，該結構指定進入 SP 以填充用戶對象的參數。值得一提的是，不會在每次通過循環結構時都進行慢速調用。它通常可以在一天或更長時間的大部分時間裡使用，然後開始呈現，這使得調試變得非常困難。
有問題的表包含大約 500 萬行。緩慢的通話，例如，將需要長達 10 秒，而快速調用平均需要 0 到 10 毫秒。我在慢速調用期間檢查了鎖定/阻塞事務，沒有找到。我在數據層創建了一些自定義性能計數器來監控調用時間。從本質上講，當性能很差時，這對那個電話來說真的很糟糕。但是當它很好時，它真的很好。我已經能夠在幾台不同的開發人員機器上重現該問題，但不能在我們的開發和暫存數據庫服務器上重現，這些服務器當然具有更強大的硬件。通常，通過重新啟動 SQL Server 服務可以解決問題，但並非總是如此。表上有我正在查詢的字段的索引，但是索引比我想要的要多。但是，我' 由於可能對遺留系統產生影響，我不願刪除任何索引或玩具。以前有沒有人遇到過這樣的問題，或者您有解決方法的建議嗎？
CREATE TABLE [dbo].[product_performance_quarterly](
[performance_id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[product_id] [int] NULL,
[month] [int] NULL,
[year] [int] NULL,
[performance] [decimal](18, 6) NULL,
[gross_or_net] [char](15) NULL,
[vehicle_type] [char](30) NULL,
[quarterly_or_monthly] [char](1) NULL,
[stamp] [datetime] NULL CONSTRAINT [DF_product_performance_quarterly_stamp] DEFAULT (getdate()),
[eA_loaded] [nchar](10) NULL,
[vehicle_type_id] [int] NULL,
[yearmonth] [char](6) NULL,
[gross_or_net_id] [tinyint] NULL,
CONSTRAINT [PK_product_performance_quarterly_4_19_04] PRIMARY KEY CLUSTERED
(
[performance_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[product_performance_quarterly] WITH NOCHECK ADD CONSTRAINT [FK_product_performance_quarterlyProduct_id] FOREIGN KEY([product_id])
REFERENCES [dbo].[products] ([product_id])
GO
ALTER TABLE [dbo].[product_performance_quarterly] CHECK CONSTRAINT [FK_product_performance_quarterlyProduct_id]
CREATE PROCEDURE [eA.Analytics.Calculations].[USP.GetCalculationData]
(
@PRODUCTID INT, ‑‑products.product_id
@BEGINYEAR INT, ‑‑year to begin retrieving performance data
@BEGINMONTH INT, ‑‑month to begin retrieving performance data
@ENDYEAR INT, ‑‑year to end retrieving performance data
@ENDMONTH INT, ‑‑month to end retrieving performance data
@QUARTERLYORMONTHLY VARCHAR(1), ‑‑do you want quarterly or monthly data?
@VEHICLETYPEID INT, ‑‑what product vehicle type are you looking for?
@GROSSORNETID INT ‑‑are your looking gross of fees data or net of fees data?
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @STARTDATE VARCHAR(6),
@ENDDATE VARCHAR(6),
@vBEGINMONTH VARCHAR(2),
@vENDMONTH VARCHAR(2)
IF LEN(@BEGINMONTH) = 1
SET @vBEGINMONTH = '0' + CAST(@BEGINMONTH AS VARCHAR(1))
ELSE
SET @vBEGINMONTH = @BEGINMONTH
IF LEN(@ENDMONTH) = 1
SET @vENDMONTH = '0' + CAST(@ENDMONTH AS VARCHAR(1))
ELSE
SET @vENDMONTH = @ENDMONTH
SET @STARTDATE = CAST(@BEGINYEAR AS VARCHAR(4)) + @vBEGINMONTH
SET @ENDDATE = CAST(@ENDYEAR AS VARCHAR(4)) + @vENDMONTH
‑‑because null values for gross_or_net_id and vehicle_type_id are represented in
‑‑multiple ways (true null, empty string, or 0) in the PPQ table, need to account for all possible variations if
‑‑a ‑1 is passed in from the .NET code, which represents an enumerated value that
‑‑indicates that the value(s) should be true null.
IF @VEHICLETYPEID = '‑1' AND @GROSSORNETID = '‑1'
SELECT
PPQ.YEARMONTH, PPQ.PERFORMANCE
FROM PRODUCT_PERFORMANCE_QUARTERLY PPQ
WITH (NOLOCK)
WHERE
(PPQ.PRODUCT_ID = @PRODUCTID)
AND (PPQ.YEARMONTH BETWEEN @STARTDATE AND @ENDDATE)
AND (PPQ.QUARTERLY_OR_MONTHLY = @QUARTERLYORMONTHLY)
AND (PPQ.VEHICLE_TYPE_ID IS NULL OR PPQ.VEHICLE_TYPE_ID = '0' OR PPQ.VEHICLE_TYPE_ID = '')
AND (PPQ.GROSS_OR_NET_ID IS NULL OR PPQ.GROSS_OR_NET_ID = '0' OR PPQ.GROSS_OR_NET_ID = '')
ORDER BY PPQ.YEARMONTH ASC
IF @VEHICLETYPEID <> '‑1' AND @GROSSORNETID <> '‑1'
SELECT
PPQ.YEARMONTH, PPQ.PERFORMANCE
FROM PRODUCT_PERFORMANCE_QUARTERLY PPQ
WITH (NOLOCK)
WHERE
(PPQ.PRODUCT_ID = @PRODUCTID)
AND (PPQ.YEARMONTH BETWEEN @STARTDATE AND @ENDDATE)
AND (PPQ.QUARTERLY_OR_MONTHLY = @QUARTERLYORMONTHLY)
AND (PPQ.VEHICLE_TYPE_ID = @VEHICLETYPEID )
AND (PPQ.GROSS_OR_NET_ID = @GROSSORNETID)
ORDER BY PPQ.YEARMONTH ASC
IF @VEHICLETYPEID = '‑1' AND @GROSSORNETID <> '‑1'
SELECT
PPQ.YEARMONTH, PPQ.PERFORMANCE
FROM PRODUCT_PERFORMANCE_QUARTERLY PPQ
WITH (NOLOCK)
WHERE
(PPQ.PRODUCT_ID = @PRODUCTID)
AND (PPQ.YEARMONTH BETWEEN @STARTDATE AND @ENDDATE)
AND (PPQ.QUARTERLY_OR_MONTHLY = @QUARTERLYORMONTHLY)
AND (PPQ.VEHICLE_TYPE_ID IS NULL OR PPQ.VEHICLE_TYPE_ID = '0' OR PPQ.VEHICLE_TYPE_ID = '')
AND (PPQ.GROSS_OR_NET_ID = @GROSSORNETID)
ORDER BY PPQ.YEARMONTH ASC
IF @VEHICLETYPEID <> '‑1' AND @GROSSORNETID = '‑1'
SELECT
PPQ.YEARMONTH, PPQ.PERFORMANCE
FROM PRODUCT_PERFORMANCE_QUARTERLY PPQ
WITH (NOLOCK)
WHERE
(PPQ.PRODUCT_ID = @PRODUCTID)
AND (PPQ.YEARMONTH BETWEEN @STARTDATE AND @ENDDATE)
AND (PPQ.QUARTERLY_OR_MONTHLY = @QUARTERLYORMONTHLY)
AND (PPQ.VEHICLE_TYPE_ID = @VEHICLETYPEID)
AND (PPQ.GROSS_OR_NET_ID IS NULL OR PPQ.GROSS_OR_NET_ID = '0' OR PPQ.GROSS_OR_NET_ID = '')
ORDER BY PPQ.YEARMONTH ASC
END
參考解法
方法 1:
I have seen this happen with indexes that were out of date. It could also be a parameter sniffing problem, where a different query plan is being used for different parameters that come in to the stored procedure.
You should capture the parameters of the slow calls and see if they are the same ones each time it runs slow.
You might also try running the tuning wizard and see if it recommends any indexes.
You don't want to worry about having too many indexes until you can prove that updates and inserts are happening too slow (time needed to modify the index plus locking/contention), or you are running out of disk space for them.
方法 2:
Sounds like another query is running in the background that has locked the table and your innocent query is simply waiting for it to finish
方法 3:
A strange, edge case but I encountered it recently.
If the queries run longer in the application than they do when run from within Management Studio, you may want to check to make sure that Arithabort is set off. The connection parameters used by Management Studio are different from the ones used by .NET.
方法 4:
It seems like it's one of two things ‑ either the parameters on the slow calls are different in some way than on the fast calls, and they're not able to use the indexes as well, or there's some type of locking contention that's holding you up. You say you've checked for blocking locks while a particular process is hung, and saw none ‑ that would suggest that it's the first one. However ‑ are you sure that your staging server (that you can't reproduce this error on) and the development servers (that you can reproduce it on) have the same database configuration? For example, maybe "READ COMMITTED SNAPSHOT" is enabled in production, but not in development, which would cause read contention issues to disappear in production.
If it's a difference in parameters, I'd suggest using SQL Profiler to watch the transactions and capture a few ‑ some slow ones and some faster ones, and then, in a Management Studio window, replace the variables in that SP above with the parameter values and then get an execution plan by pressing "Control‑L". This will tell you exactly how SQL Server expects to process your query, and you can compare the execution plan for different parameter combination to see if there's a difference with one set, and work from there to optimize it.
Good luck!
(by Greg、Eric Z Beard、rpetrich、Josef、SqlRyan)