1

I wrote a program to test concurrent reads to a single database table. I'm expecting high amounts of read (no deletes, inserts, or updates) traffic in the future, using ad hoc queries, which is why I am testing up front to simulate this. Based on the logs below you can see that as the number of concurrent reads increases, the time it takes for requests to complete also increases. And drastically so, going from 3 seconds for a single request to a maximum of 15 seconds for 20 concurrent requests.

I'm not unreasonable I think, when I spam a database for requests I expect it to take longer to process, however not by such a huge factor. Especially since the server is not CPU-bound, as is evident by the SQL Server Performance Dashboard with CPU usage at 40% during 20 concurrent requests.

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

Interestingly, I found little to no difference between using SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED, and omitting it. The average response times are comparable. Is this normal? In case it helps, viewing a single request in the SQL Server Profiler looks like below. You will notice that the CPU, and Reads is pretty high (I intentionally wrote a rather heavy query). I also found that 20 concurrent requests targeting different tables does not suffer from as much a delay. So I would be open to splitting tables if there's no other way to increase performance.

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     

Can an SQL Server, or the queries sent to it, be optimized for concurrent reads? If so, how? If not, what product should I move to?

EDIT: added additional information.

When executing the script found at sqlskills.com after executing DBCC SQLPERF (N'sys.dm_os_wait_stats', CLEAR) and running 20 concurrent requests.

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

The output of SELECT @@VERSION; is Microsoft SQL Server 2017 (RTM-CU15-GDR) (KB4505225) - 14.0.3192.2 (X64) Jun 15 2019 00:45:05 Copyright (C) 2017 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2016 Standard 10.0 (Build 14393: ) (Hypervisor).

  • Rows returned are 2123. Number of cores is 4, MAXDOP is set to 1. I know there's more than CPU, but am unsure how to check what the bottleneck is. – bdebaere Nov 3 '20 at 10:37
5

The query consumes 2000ms of CPU time. So your throughput is limited by your CPU cores. With 4 cores throughput is 2 queries per second. If you have 30 concurrent requests it will take 15 seconds for them to complete. And SQL Server’s scheduler is very “fair” so each query will take about 15sec as SQL Server task switches between concurrent requests every few milliseconds.

And the wait stats confirm that the workload is CPU-bound. SOS_SCHEDULER_YIELD is the wait when a task has yielded the CPU core and is waiting for another turn.

The CPU time for a query is comprised of:

  • Time taken by the CPU processing instructions.
  • Time taken waiting for main memory reads (the wait time for this is much too short to yield the CPU).
  • Time waiting on another hyperthread that shares the physcial CPU core.
  • Time when the guest OS thread is scheduled on a vCore but is actually waiting on a physical core on the hypervisor host.

This query requires 436,673 reads, so reducing that is probably the main avenue for optimization. Better indexes, a better query, or, perhaps, columnstore compression can be used to reduce the number of reads required by the query.

  • 3
    Adding that SOS_SCHEDULER_YIELD wait stat reflects the switches between runnable requests every 4ms or so. – Dan Guzman Nov 3 '20 at 12:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.