优化并发读取性能

Question

我编写了一个程序来测试对单个数据库表的并发读取。我期望将来会有大量的读取(不删除、插入或更新)通信量，使用临时查询，这就是为什么我要预先测试来模拟这个问题。根据下面的日志，您可以看到，随着并发读取次数的增加，完成请求所需的时间也会增加。因此，对于单个请求，从3秒到20个并发请求最多15秒。

我并不是不合理的--我认为，当我为请求垃圾数据库时，我希望它需要更长的时间来处理，但是这并不是一个很大的因素。特别是因为服务器不是CPU绑定的，这一点从server性能仪表板可以看出，在20个并发请求中，CPU使用率为40%。

Started 1.
Ended 1. Elapsed milliseconds: 2938

Started 2.
Started 4.
Started 5.
Started 3.
Started 1.
Ended 1. Elapsed milliseconds: 3204
Ended 3. Elapsed milliseconds: 4486
Ended 5. Elapsed milliseconds: 5185
Ended 2. Elapsed milliseconds: 5261
Ended 4. Elapsed milliseconds: 6075

Started 1.
Started 5.
Started 7.
Started 3.
Started 6.
Started 4.
Started 8.
Started 9.
Started 2.
Started 10.
Ended 6. Elapsed milliseconds: 4789
Ended 4. Elapsed milliseconds: 5327
Ended 3. Elapsed milliseconds: 5684
Ended 7. Elapsed milliseconds: 5732
Ended 1. Elapsed milliseconds: 6846
Ended 5. Elapsed milliseconds: 7238
Ended 8. Elapsed milliseconds: 7355
Ended 2. Elapsed milliseconds: 7551
Ended 10. Elapsed milliseconds: 7581
Ended 9. Elapsed milliseconds: 7675

Started 1.
Started 11.
Started 6.
Started 7.
Started 17.
Started 2.
Started 3.
Started 16.
Started 9.
Started 18.
Started 10.
Started 19.
Started 8.
Started 20.
Started 4.
Started 12.
Started 14.
Started 13.
Started 15.
Started 5.
Ended 8. Elapsed milliseconds: 4167
Ended 4. Elapsed milliseconds: 7327
Ended 18. Elapsed milliseconds: 9435
Ended 11. Elapsed milliseconds: 9450
Ended 10. Elapsed milliseconds: 9506
Ended 1. Elapsed milliseconds: 9884
Ended 17. Elapsed milliseconds: 9904
Ended 3. Elapsed milliseconds: 10181
Ended 19. Elapsed milliseconds: 10273
Ended 7. Elapsed milliseconds: 10640
Ended 14. Elapsed milliseconds: 11451
Ended 12. Elapsed milliseconds: 11713
Ended 15. Elapsed milliseconds: 12148
Ended 2. Elapsed milliseconds: 12304
Ended 9. Elapsed milliseconds: 12496
Ended 6. Elapsed milliseconds: 12656
Ended 16. Elapsed milliseconds: 12973
Ended 20. Elapsed milliseconds: 14306
Ended 13. Elapsed milliseconds: 14603
Ended 5. Elapsed milliseconds: 15106

有趣的是，我发现在使用SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED和省略它之间没有什么区别。平均响应时间是可比的。这是正常的吗？如果有帮助，请在Server事件探查器中查看单个请求，如下所示。您将注意到CPU和读取相当高(我有意编写了一个相当繁重的查询)。我还发现，针对不同表的20个并发请求没有那么多延迟。因此，如果没有其他提高性能的方法，我就愿意对表进行拆分。

Audit Login -- network protocol: TCP/IP
set quoted_identifier on
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read uncommitted
                                ApplicationName     LoginName                   15544 (ClientProcessID) 93 (SPID)   2020-10-29 10:38:35.313         

SQL:BatchStarting   QueryText   ApplicationName     LoginName                   15544 (ClientProcessID) 93 (SPID)   2020-10-29 10:38:35.320         

SQL:BatchCompleted  QueryText   ApplicationName     LoginName   2187 (CPU)  436673 (Reads)  0 (Writes)  6811 (Duration) 15544 (ClientProcessID) 93 (SPID)   2020-10-29 10:38:35.320 2020-10-29 10:38:42.130     

是否可以为并发读取优化Server或发送给它的查询？如果是这样的话，是怎么做的？如果没有，我应该转移到什么产品？

编辑:添加了其他信息。

执行在sqlskills.com上找到的脚本时，执行DBCC SQLPERF (N'sys.dm_os_wait_stats', CLEAR)并运行20个并发请求。

WaitType    Wait_S  Resource_S  Signal_S    WaitCount   Percentage  AvgWait_S   AvgRes_S    AvgSig_S    Help/Info URL
SOS_SCHEDULER_YIELD 179.18  0.01    179.17  11860   95.25   0.0151  0.0000  0.0151  https://www.sqlskills.com/help/waits/SOS_SCHEDULER_YIELD
RESOURCE_SEMAPHORE  6.93    6.92    0.00    1   3.68    6.9270  6.9240  0.0030  https://www.sqlskills.com/help/waits/RESOURCE_SEMAPHORE
ASYNC_NETWORK_IO    1.57    1.56    0.01    26  0.84    0.0605  0.0600  0.0005  https://www.sqlskills.com/help/waits/ASYNC_NETWORK_IO
LCK_M_SCH_M 0.18    0.15    0.03    14  0.09    0.0127  0.0109  0.0019  https://www.sqlskills.com/help/waits/LCK_M_SCH_M
PREEMPTIVE_OS_CRYPTOPS  0.06    0.06    0.00    9   0.03    0.0067  0.0067  0.0000  https://www.sqlskills.com/help/waits/PREEMPTIVE_OS_CRYPTOPS
RESERVED_MEMORY_ALLOCATION_EXT  0.05    0.05    0.00    41234   0.03    0.0000  0.0000  0.0000  https://www.sqlskills.com/help/waits/RESERVED_MEMORY_ALLOCATION_EXT
PREEMPTIVE_OS_AUTHENTICATIONOPS 0.04    0.04    0.00    9   0.02    0.0046  0.0046  0.0000  https://www.sqlskills.com/help/waits/PREEMPTIVE_OS_AUTHENTICATIONOPS
PREEMPTIVE_OS_NETVALIDATEPASSWORDPOLICY 0.02    0.02    0.00    3   0.01    0.0070  0.0070  0.0000  https://www.sqlskills.com/help/waits/PREEMPTIVE_OS_NETVALIDATEPASSWORDPOLICY
PREEMPTIVE_OS_NETVALIDATEPASSWORDPOLICYFREE 0.02    0.02    0.00    3   0.01    0.0067  0.0067  0.0000  https://www.sqlskills.com/help/waits/PREEMPTIVE_OS_NETVALIDATEPASSWORDPOLICYFREE
PREEMPTIVE_OS_AUTHORIZATIONOPS  0.02    0.02    0.00    3   0.01    0.0067  0.0067  0.0000  https://www.sqlskills.com/help/waits/PREEMPTIVE_OS_AUTHORIZATIONOPS
PREEMPTIVE_OS_CRYPTACQUIRECONTEXT   0.02    0.02    0.00    3   0.01    0.0067  0.0067  0.0000  https://www.sqlskills.com/help/waits/PREEMPTIVE_OS_CRYPTACQUIRECONTEXT
PREEMPTIVE_OS_QUERYREGISTRY 0.02    0.02    0.00    17  0.01    0.0009  0.0009  0.0000  https://www.sqlskills.com/help/waits/PREEMPTIVE_OS_QUERYREGISTRY
PAGEIOLATCH_SH  0.01    0.01    0.00    12  0.00    0.0006  0.0006  0.0000  https://www.sqlskills.com/help/waits/PAGEIOLATCH_SH
LATCH_EX    0.00    0.00    0.00    3   0.00    0.0010  0.0003  0.0007  https://www.sqlskills.com/help/waits/LATCH_EX
PREEMPTIVE_XE_CALLBACKEXECUTE   0.00    0.00    0.00    48  0.00    0.0000  0.0000  0.0000  https://www.sqlskills.com/help/waits/PREEMPTIVE_XE_CALLBACKEXECUTE
PREEMPTIVE_OS_CLOSEHANDLE   0.00    0.00    0.00    2   0.00    0.0000  0.0000  0.0000  https://www.sqlskills.com/help/waits/PREEMPTIVE_OS_CLOSEHANDLE
PREEMPTIVE_OS_WRITEFILE 0.00    0.00    0.00    1   0.00    0.0000  0.0000  0.0000  https://www.sqlskills.com/help/waits/PREEMPTIVE_OS_WRITEFILE

SELECT @@VERSION;的输出是Microsoft 2017 (RTM-CU15-GDR) (KB4505225) - 14.0.3192.2 (X64) 2019年6月15日00:45:05版权(C) 2017年微软公司标准版(64位)上Windows 2016标准版10.0 (Build 14393：) (Hypervisor)。

Answer 1

查询消耗2000 of的CPU时间。因此，您的吞吐量受到CPU核心的限制。4核吞吐量为每秒2次查询。如果您有30个并发请求，则需要15秒才能完成。而且Server的调度程序非常“公平”，所以每隔几毫秒就会发生一次SQL Server任务在并发请求之间切换，所以每次查询大约需要15秒。

等待状态确认工作负载是CPU绑定的。SOS_SCHEDULER_YIELD是当任务产生CPU核心并等待下一个回合时的等待。

查询的CPU时间包括：

• CPU处理指令所花费的时间。
• 等待主内存读取所需的时间(等待时间太短，无法产生CPU)。
• 等待另一个共享物理CPU核心的超级线程的时间。
• 当客户操作系统线程被安排在一个vCore上，但实际上在虚拟机管理程序主机上等待一个物理内核时。

这个查询需要436,673读取，因此减少读取可能是优化的主要途径。可以使用更好的索引、更好的查询或柱状商店压缩来减少查询所需的读取数。