为什么我的SELECT查询需要这么长时间？

问题描述：

CREATE TABLE [dbo].[Notifications](
[ID] [int] IDENTITY(1,1) NOT NULL, 
[Fk_institutionId] [int] NOT NULL, 
[Fk_userId] [int] NOT NULL, 
[Read] [bit] NOT NULL, 
[CategoryId] [int] NOT NULL, 
[Title] [nvarchar](150) NULL, 
[NotificationText] [text] NULL, 
[CreateDate] [datetime] NOT NULL, 
[ReadDate] [datetime] NULL, 
[DisplayDate] [datetime] NULL, 
[ReadBy] [nvarchar](100) NULL, 
CONSTRAINT [PK_Notifications] PRIMARY KEY CLUSTERED 
(
    [ID] ASC 
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

简单select * from Notifications需要30秒，而有此表仅800,000行。

这种实际情况是每个用户执行查询select * from notifications where DisplayDate is null and Fk_userId = [the user id]，这在我的.NET Web应用程序中创建了一个服务器错误，提到了有关死锁的一些信息。

该查询的执行计划很简单

我在做什么错？或者为什么会造成死锁错误？

好像还有比上面列出多个列 - 因为你CREATE语句不完整 –

可我知道，你为什么要选择整个表？据我所知，SQL Server将执行简单的表扫描。涉及扫描表的每一行，分配一些内存，并返回这将是最快的方式返回结果 –

30秒获取800,000行是每秒26,667行。慢速网络为100Mbps或每秒约12.5 MB。因此，如果您的数据库平均每个记录大约500个字节（如果您有长文本数据，这很容易），网络可能会限制您的传输速度。 –

答

这可能是由于多种原因：

也许别人正在访问该表也上了锁。在这种情况下，请尝试以下操作：

SELECT * FROM Notifications WITH(NOLOCK)

有时，问题与缺失索引有关。我不认为它与索引有关，因为你有一个主键。但是，您需要检查该索引的碎片程度，并在必要时重新构建它。

另一个关键点可能是query optimization statistics。您可以通过执行此更新它们：

UPDATE STATISTICS Notifications;

按你最后一次更新，您可以使用此查询处理阻塞：

-- Detect blocking (run multiple times) (Query 62) (Detect Blocking) 
SELECT t1.resource_type AS [lock type], DB_NAME(resource_database_id) AS [database], 
t1.resource_associated_entity_id AS [blk object],t1.request_mode AS [lock req], --- lock requested 
t1.request_session_id AS [waiter sid], t2.wait_duration_ms AS [wait time],  -- spid of waiter 
(SELECT [text] FROM sys.dm_exec_requests AS r WITH (NOLOCK)      -- get sql for waiter 
CROSS APPLY sys.dm_exec_sql_text(r.[sql_handle]) 
WHERE r.session_id = t1.request_session_id) AS [waiter_batch], 
(SELECT SUBSTRING(qt.[text],r.statement_start_offset/2, 
    (CASE WHEN r.statement_end_offset = -1 
    THEN LEN(CONVERT(nvarchar(max), qt.[text])) * 2 
    ELSE r.statement_end_offset END - r.statement_start_offset)/2) 
FROM sys.dm_exec_requests AS r WITH (NOLOCK) 
CROSS APPLY sys.dm_exec_sql_text(r.[sql_handle]) AS qt 
WHERE r.session_id = t1.request_session_id) AS [waiter_stmt],     -- statement blocked 
t2.blocking_session_id AS [blocker sid],          -- spid of blocker 
(SELECT [text] FROM sys.sysprocesses AS p          -- get sql for blocker 
CROSS APPLY sys.dm_exec_sql_text(p.[sql_handle]) 
WHERE p.spid = t2.blocking_session_id) AS [blocker_stmt] 
FROM sys.dm_tran_locks AS t1 WITH (NOLOCK) 
INNER JOIN sys.dm_os_waiting_tasks AS t2 WITH (NOLOCK) 
ON t1.lock_owner_address = t2.resource_address OPTION (RECOMPILE); 

-- Helps troubleshoot blocking and deadlocking issues 
-- The results will change from second to second on a busy system 
-- You should run this query multiple times when you see signs of blocking

我同意！查询速度慢的其他原因是，您包含所有列，并且没有WHERE子句。你可能会认为你会得到性能上的好处，因为你有一个聚集索引，但它不是 –

@luis Teijon增加'WITH（NOLOCK）'似乎没有改变make case，即使它可能是一个问题，因为查询在那张桌子上有时会返回一个死锁应用程序错误（请参阅我的编辑） – Greg

答

你在你的表有一个文本字段（NotificationText）。 SQL Server中的“文本”是一种BLOB（二进制大型对象）。这些字段类型非常难以优化。您可以使用全文索引，但会变得复杂。

我会尝试排除该字段的选择。或者，问问自己是否真的需要使用文本。 Nvarchar（8000）就足够了吗？

我敢打赌，如果您删除该文本字段，或将其重新定义为nvarchar，您的查询将突然开始飞行。

它可能是......在我实际测试之前需要做一些工作。谢谢你的评论 – Greg

祝你好运！如果有帮助，请考虑赞成或接受答案。 –

我终于尝试了你的解决方案，它似乎让事情变得更糟......我无法得到任何东西，只能在桌面上产生死锁，而之前我会在一段时间内陷入僵局 – Greg

答

您已经询问了两个不同的查询。

更重要的似乎是

SELECT * 
FROM notifications 
WHERE DisplayDate IS NULL 
     AND Fk_userId = [the user id]

执行计划表明，缺少任何有用的索引，所以越来越整个表的并行扫描。

如果你提供了一个索引，那么只需要读取较少的数据，它就会表现得更好，并且可以减少死锁。

该查询的一个例子指数是

CREATE INDEX IX_Fk_userId_Where_DisplayDate_Is_Null 
    ON notifications(Fk_userId) 
    INCLUDE (DisplayDate) 
    WHERE DisplayDate IS NULL;

为什么我的SELECT查询需要这么长时间？

相关推荐