sql-server - SQL Server 在开发中非常慢，但在生产中没有
问题描述
我一直在与 SQL Server (14.0.2027.2) 在我的开发环境中为最简单的查询超时作斗争。
这不仅会影响我开发的 Web 应用程序，还会影响 SQL Server Management Studio (v15.0.18.131.0) 中的简单功能，例如
扩展“表”或“存储过程”节点（包含大约 100 个表和 400 个过程）可能需要 2-3 分钟
右键单击 > 修改存储过程可能需要 2-3 分钟才能打开 ALTER PROCEDURE 查询窗口。
我在带有 4 个 SAMSUNG SSD 的 Core i7 上运行。这个问题在我的开发环境中是一致的，我定期下载生产数据库并恢复到开发。
所以你会问我试过什么：
我很难知道如何使用工具，例如
sp_WhoIsActive所有东西都锁定在 SQL Server Management Studio 中，而 UI 被上面 1 和 2 中描述的缓慢进程阻塞。我已经重新安装了 Management Studio（没有解决问题）
我查看了我的物理磁盘：作为开发环境，数据库数据和日志存储在启用了 Bitlocker 的 256GB 三星 SSD 上，它仅用于数据库数据和日志。我以前将日志放在单独的（非 bitlocker 非操作系统驱动器）上，但当时问题仍然存在。
我正在从我们的两个生产环境中进行备份并将其还原到开发环境。备份和还原都不会像查询那样延迟。该数据库为 5GB，具有良好的索引和 RI，并且在生产环境中运行良好，尽管配置相似（一个在 SSD 上运行，另一个在 HDD 上运行）。
我怀疑从 SQL Server 2016 升级到 SQL Server 2019 后出现了问题，但那是几个月前的事了，所以我不能确定。
我还真的怀疑这不是数据库表、索引和关系的问题，而是在 SQL Server 级别进行了一些配置。欢迎任何建议。
编辑：@@VERSION返回“Microsoft SQL Server 2017 (RTM-GDR) (KB4505224) - 14.0.2027.2 (X64) Jun 15 2019 00:26:19 Copyright (C) 2017 Microsoft Corporation Standard Edition (64-bit) on Windows 10 Pro 10.0（内部版本 18362：）（管理程序）“
编辑：我已经全面检查了自动关闭。在实时、测试和开发中，每个数据库都将 AUTO CLOSE 设置为 FALSE。
编辑：检查 SQL 服务器的配置（使用 SQLServerManager14.exe）：
- SQL Server：集成服务 14：运行/自动
- SQL Server：运行/自动
- SQL Server 分析服务：运行/自动
- SQL Server 浏览器：已停止/其他
- SQL Server 代理：已停止/手动
本机客户端协议：
- 共享内存：[1st] 启用
- TCP/IP：[2nd] 启用
- 命名管道：[3rd] 已启用
网络配置协议：
- 共享内存：启用
- 命名管道：已禁用
- TCP/IP：启用
编辑：在等待右键单击存储过程 > 修改打开时查询sys.dm_os_waiting_tasks视图（打开 SP 大约需要 45 秒）：（运行了几次，但这是一个示例；blocking_task_address blocking_session_id blocking_exec_context_id resource_description都是 NULL）
1> SELECT * FROM sys.dm_os_waiting_tasks
2> go
waiting_task_address session_id exec_context_id wait_duration_ms wait_type resource_address
-------------------- ---------- --------------- -------------------- ------------------------------------------------------------ ------------------
0x000001A31701C8C8 12 0 8707 XE_DISPATCHER_WAIT NULL
0x000001A31701CCA8 18 0 102 ONDEMAND_TASK_QUEUE 0x000000240F7FE950
0x000001A31701D848 9 0 8833279 BROKER_TRANSMITTER NULL
0x000001A317027088 4 0 1 LAZYWRITER_SLEEP NULL
0x000001A317027468 20 0 4509358 BROKER_EVENTHANDLER NULL
0x000001A31702ECA8 19 0 1209983 CHECKPOINT_QUEUE 0x000000240FBFEB00
0x000001A31702F088 7 0 544 SLEEP_TASK NULL
0x000001A317036CA8 6 0 8833175 KSOURCE_WAKEUP NULL
0x000001A3170368C8 17 0 134353 SP_SERVER_DIAGNOSTICS_SLEEP 0x0000000000000001
0x000001A317037848 34 0 8833279 BROKER_TRANSMITTER NULL
0x000001A31703ECA8 5 0 3714 REQUEST_FOR_DEADLOCK_SEARCH 0x000000240FFFEBD0
0x000001A31703F088 36 0 215 SLEEP_TASK NULL
0x000001A3170468C8 NULL NULL 8834648 XTP_PREEMPTIVE_TASK NULL
0x000001A317046CA8 8 0 118 SLEEP_TASK NULL
0x000001A317047C28 35 0 322 HADR_FILESTREAM_IOMGR_IOCOMPLETION NULL
0x000001A31704E8C8 1 0 8834648 WAIT_XTP_HOST_WAIT NULL
0x000001A31704ECA8 NULL NULL 14105 QDS_PERSIST_TASK_MAIN_LOOP_SLEEP NULL
0x000001A317056CA8 11 0 4190 XE_TIMER_EVENT NULL
0x000001A317057088 NULL NULL 8834640 QDS_ASYNC_QUEUE NULL
0x000001A317057468 21 0 8834376 HADR_NOTIFICATION_DEQUEUE 0x00000024115FF3D0
0x000001A3176904E8 10 0 102 SLEEP_TASK NULL
0x000001A311A204E8 2 0 82 LOGMGR_QUEUE 0x000000240EDFD170
0x000001A311A384E8 3 0 28 DIRTY_PAGE_POLL NULL
0x000001A3103304E8 NULL NULL 4711 CLR_AUTO_EVENT NULL
0x000001A31034A4E8 NULL NULL 4711 CLR_AUTO_EVENT NULL
0x000001A30FA064E8 15 0 1137 SQLTRACE_INCREMENTAL_FLUSH_SLEEP NULL
(26 rows affected)
编辑：我在打开存储过程进行编辑时运行了 SQL Profiler。打开大约需要 90 秒。配置文件在此特定查询上被阻止了 86.43 秒（它很长！）：
exec sp_executesql N'SELECT
sp.name AS [Name],
sp.object_id AS [ID],
sp.create_date AS [CreateDate],
sp.modify_date AS [DateLastModified],
ISNULL(ssp.name, N'''') AS [Owner],
CAST(case when sp.principal_id is null then 1 else 0 end AS bit) AS [IsSchemaOwned],
SCHEMA_NAME(sp.schema_id) AS [Schema],
CAST(
case
when sp.is_ms_shipped = 1 then 1
when (
select
major_id
from
sys.extended_properties
where
major_id = sp.object_id and
minor_id = 0 and
class = 1 and
name = N''microsoft_database_tools_support'')
is not null then 1
else 0
end
AS bit) AS [IsSystemObject],
CAST(ISNULL(OBJECTPROPERTYEX(sp.object_id,N''ExecIsAnsiNullsOn''),0) AS bit) AS [AnsiNullsStatus],
CAST(ISNULL(OBJECTPROPERTYEX(sp.object_id,N''ExecIsQuotedIdentOn''),0) AS bit) AS [QuotedIdentifierStatus],
CAST(ISNULL(OBJECTPROPERTYEX(sp.object_id, N''IsSchemaBound''),0) AS bit) AS [IsSchemaBound],
CAST(CASE WHEN ISNULL(smsp.definition, ssmsp.definition) IS NULL THEN 1 ELSE 0 END AS bit) AS [IsEncrypted],
CAST(ISNULL(smsp.is_recompiled, ssmsp.is_recompiled) AS bit) AS [Recompile],
case when amsp.object_id is null then N'''' else asmblsp.name end AS [AssemblyName],
case when amsp.object_id is null then N'''' else amsp.assembly_class end AS [ClassName],
case when amsp.object_id is null then N'''' else amsp.assembly_method end AS [MethodName],
case when amsp.object_id is null then case isnull(smsp.execute_as_principal_id, -1) when -1 then 1 when -2 then 2 else 3 end else case isnull(amsp.execute_as_principal_id, -1) when -1 then 1 when -2 then 2 else 3 end end AS [ExecutionContext],
case when amsp.object_id is null then ISNULL(user_name(smsp.execute_as_principal_id),N'''') else user_name(amsp.execute_as_principal_id) end AS [ExecutionContextPrincipal],
CAST(ISNULL(spp.is_auto_executed,0) AS bit) AS [Startup],
CASE WHEN sp.type = N''P'' THEN 1 WHEN sp.type = N''PC'' THEN 2 ELSE 1 END AS [ImplementationType],
CAST(CASE sp.type WHEN N''RF'' THEN 1 ELSE 0 END AS bit) AS [ForReplication],
ISNULL(sm.uses_native_compilation,0) AS [IsNativelyCompiled]
FROM
sys.all_objects AS sp
LEFT OUTER JOIN sys.database_principals AS ssp ON ssp.principal_id = ISNULL(sp.principal_id, (OBJECTPROPERTY(sp.object_id, ''OwnerId'')))
LEFT OUTER JOIN sys.sql_modules AS smsp ON smsp.object_id = sp.object_id
LEFT OUTER JOIN sys.system_sql_modules AS ssmsp ON ssmsp.object_id = sp.object_id
LEFT OUTER JOIN sys.assembly_modules AS amsp ON amsp.object_id = sp.object_id
LEFT OUTER JOIN sys.assemblies AS asmblsp ON asmblsp.assembly_id = amsp.assembly_id
LEFT OUTER JOIN sys.procedures AS spp ON spp.object_id = sp.object_id
LEFT OUTER JOIN sys.all_sql_modules AS sm ON sm.object_id = sp.object_id
WHERE
(sp.type = @_msparam_0 OR sp.type = @_msparam_1 OR sp.type=@_msparam_2)and(sp.name=@_msparam_3 and SCHEMA_NAME(sp.schema_id)=@_msparam_4)',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000),@_msparam_3 nvarchar(4000),@_msparam_4 nvarchar(4000)',@_msparam_0=N'P',@_msparam_1=N'RF',@_msparam_2=N'PC',@_msparam_3=N'CreateItem_FlightCheck',@_msparam_4=N'Assessment'
编辑：如果我的系统数据库出现故障，它们将存储在我的开发环境的 OS 磁盘 (SSD) 上：
Volume in drive C has no label.
Volume Serial Number is 1202-7717
Directory of C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA
15/07/2019 21:10 <DIR> .
15/07/2019 21:10 <DIR> ..
18/09/2019 13:32 5,636,096 master.mdf
18/09/2019 13:32 1,835,008 mastlog.ldf
18/09/2019 10:28 8,388,608 model.mdf
18/09/2019 13:32 8,388,608 modellog.ldf
18/09/2019 11:43 20,578,304 MSDBData.mdf
18/09/2019 13:32 20,578,304 MSDBLog.ldf
21/08/2018 01:34 778 MS_AgentSigningCertificate.cer
15/07/2019 21:10 778 MS_AgentSigningCertificate2DA7FDC4-1A16-487A-89A4-630444C694CA.cer
30/05/2019 09:53 778 MS_AgentSigningCertificate35C8E19E-8C24-4101-835E-D976260916A0.cer
15/05/2019 15:50 778 MS_AgentSigningCertificateAC01273F-89E4-48ED-8F9C-CF4A608AE064.cer
18/09/2019 12:58 8,388,608 tempdb.mdf
18/09/2019 13:31 8,388,608 tempdb_mssql_2.ndf
18/09/2019 13:31 8,388,608 tempdb_mssql_3.ndf
18/09/2019 13:31 8,388,608 tempdb_mssql_4.ndf
18/09/2019 13:31 8,388,608 tempdb_mssql_5.ndf
18/09/2019 13:31 8,388,608 tempdb_mssql_6.ndf
18/09/2019 13:32 8,388,608 tempdb_mssql_7.ndf
18/09/2019 13:10 8,388,608 tempdb_mssql_8.ndf
18/09/2019 13:32 8,388,608 templog.ldf
19 File(s) 140,905,512 bytes
2 Dir(s) 76,880,195,584 bytes free
谢谢，克里斯