We have a client asking us what the login rate per second is that I can achieve on a S0, S1, S2, and S3 database. It's a complicated question but I'd like to share my experiences using a connection pooler.
The three previous articles we focus on optimizing connection pooling for application workloads, each addressing different aspects of this challenge:
-
Optimizing Connection Pooling - Thread Contention (Lesson Learned #455): - Lesson Learned #455:Optimizing Connection Pooling for Application Workloads: Thread Contention - Microsoft Community Hub
- Issue Addressed: A client experienced a 5-second delay in database connectivity while launching 50-100 concurrent processes. This delay was traced back to thread contention and not directly related to establishing database connections.
- Key Lessons:
- Connection Pool Optimization: Adjusting the pool size to match concurrency needs and database server capabilities.
- Improving Retry Logic: Implementing exponential backoff in retry logic.
- Limiting Concurrent Connections: Using semaphores or a task queue to control connection concurrency.
- Profiling and Monitoring: Utilizing tools to identify where contention occurs and understanding application behavior.
- Performance Measurement: Utilizing .NET CLR LocksAndThreads counters and external profiling tools to monitor thread behavior and database interactions.
-
Optimizing Connection Pooling - Active Connections (Lesson Learned #454): - Lesson Learned #454:Optimizing Connection Pooling for Application Workloads: Active Connections - Microsoft Community Hub
- Exploration: The focus was on understanding active connections in connection pooling, including the number of pools, active connections, and how they fluctuate during open/close operations.
- Findings: By opening and closing 100 connections, the dynamics of connection pooling were observed. Counters such as the number of active connections, connections managed by the pool, and connections waiting for readiness provided insights into the pool's performance.
- Connection Pool Management: Key aspects included the removal of idle connections after 4-8 minutes and the implications of setting the 'Min Pool Size' in the connection string. If this value is greater than zero, the pool remains active as long as the minimum size is maintained.
-
Optimizing Connection Pooling - A Single Journey (Lesson Learned #453): Lesson Learned #453:Optimizing Connection Pooling for Application Workloads: A single journey - Microsoft Community Hub
- Scenario: The customer reported high latency in establishing connections to a Basic type database using Active Directory authentication.
- Investigation Results: The first connection took 3 seconds due to several steps like sending prelogin requests, requesting AAD tokens, and creating pooled database connections.
- Connection Reuse Benefits: Subsequent connections were faster (0 ms) because the previously established connection was reused, avoiding the need for initial setup steps. The setup included a minimum pool size of 1 and a connection lifetime set to 0, ensuring constant availability and recovery in case of connectivity issues.
Each article contributes to a comprehensive understanding of the nuances and best practices in optimizing connection pooling, emphasizing the importance of configuration, monitoring, and understanding the behavior of connection pools in varying workloads and scenarios.
In this new article my idea is to know how using a subcore databases we could know how many logins per second we could have. So, the test will be the same for all databases: I'm going to run 50000 operations of Connect/Run a Select 1/Disconnect using 200 process at the same time, connection from home, in the North Europe database, using Redirect:
I'm going to start with Standard 0 database with 10 DTUs (0,10 of a vCore):
- Well, as we saw in our article Lesson Learned #455:Optimizing Connection Pooling for Application Workloads: Thread Contention - Microsoft Community Hub we could have a thread contention and for this reason, as I know that I'm going to have at least 200 threads operations running at the same time. I'm going to cache first the connection, reducing the thread contention opening a new connection and leaving the connection ready to use.
- We could see that the first operation 5 seconds to create the connection pooler stuff, but, the rest is taking few milliseconds to complete. The whole process take 1 minute and 33 seconds for opening and closing all.
2023-11-27 13:58:36.730: Pre-Warm - Done 0 - 02c6745e-c5eb-49be-8926-90d12fe7717e
2023-11-27 13:58:37.108: Pre-Warm - Done 1 - e0d8fb5b-bac3-4b9e-8076-53f92cd3b627
2023-11-27 13:58:37.490: Pre-Warm - Done 2 - f5085f5c-3abb-42f6-adf0-d28838dcc3e6
2023-11-27 13:58:38.005: Pre-Warm - Done 3 - fece89fd-4aa5-41ec-a21f-147e5472d547
2023-11-27 13:58:38.494: Pre-Warm - Done 4 - 50250745-249e-40a5-937e-90a5a1b70cf2
2023-11-27 13:58:39.063: Pre-Warm - Done 5 - fe880650-eb40-460a-aff2-133319f6906d
2023-11-27 13:58:39.549: Pre-Warm - Done 6 - d2383f0f-85c1-4b52-b9a4-76d61e353b08
2023-11-27 13:58:40.047: Pre-Warm - Done 7 - fe1e359d-7b5b-4e89-b4a5-f0b26c18ef6f
2023-11-27 13:58:40.514: Pre-Warm - Done 8 - 2483c7dd-e364-4e79-a511-73b685d4bcb6
2023-11-27 13:58:40.883: Pre-Warm - Done 9 - cb4309c2-03b2-476f-9efc-b32bb6197ef7
2023-11-27 13:58:41.340: Pre-Warm - Done 10 - 4ebb57ec-5255-42a2-a75e-b67fba1ef0a9
2023-11-27 13:58:41.706: Pre-Warm - Done 11 - d94c0ac3-7d32-4206-8092-0e68ea2b780e
2023-11-27 13:58:42.200: Pre-Warm - Done 12 - 1a6804a0-a1b7-464e-b9c3-62a2139992d4
2023-11-27 13:58:42.671: Pre-Warm - Done 13 - 75ab1eaf-04df-4888-85a4-88a8480996e4
2023-11-27 13:58:43.159: Pre-Warm - Done 14 - 16e8057d-af87-4ddc-ad1f-dc268caeb27d
2023-11-27 13:58:43.642: Pre-Warm - Done 15 - cd178928-fe09-4bc7-be49-cec3c8c7d96a
2023-11-27 13:58:44.005: Pre-Warm - Done 16 - e4768733-f58a-4a6a-8e21-9d9bbf5ef475
2023-11-27 13:58:44.493: Pre-Warm - Done 17 - 607b8cc9-471c-4cef-89db-ba41d5ca6a4c
2023-11-27 13:58:44.881: Pre-Warm - Done 18 - a5eb1d8d-43a2-4537-8f51-1c55ec739ac0
2023-11-27 13:58:45.253: Pre-Warm - Done 19 - 5d9e8443-ae15-4df1-86ce-0c468c861124
2023-11-27 13:58:45.679: Pre-Warm - Done 20 - b0cb4431-7f27-4690-ab21-e475586b5675
- During this pre-process, I've created a Extended Event to capture all the logins
CREATE EVENT SESSION [LoginLogoutTracking] ON DATABASE
ADD EVENT sqlserver.login(
ACTION(sqlserver.client_app_name,sqlserver.client_connection_id,sqlserver.session_id))
ADD TARGET package0.event_file(SET filename=N'https://blobstorage.blob.core.windows.net/xevents/LoginLogoutTracking.xel')
WITH (MAX_MEMORY=8096 KB,EVENT_RETENTION_MODE=NO_EVENT_LOSS,MAX_DISPATCH_LATENCY=15 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=OFF)
GO
- If we check the login extended event collected, we could see every second we have three connections made. Why? well connecting from home will take around aprox. 300 ms each time. So, running in Azure Environment, this time will be less.
name timestamp client_app_name client_connection_id session_id
login 2023-11-27 10:44:33.6792986 Testing by JMJD - SQL Unique DEC57BC1-2059-4000-8C69-5D6ACAF52479 76
login 2023-11-27 10:44:34.2287363 Testing by JMJD - SQL Unique 59D4DDFC-5029-41B8-BAAA-61E358186B37 77
login 2023-11-27 10:44:34.6238283 Testing by JMJD - SQL Unique 897094F2-9E18-42C9-BA12-CE68276F838B 78
login 2023-11-27 10:44:34.9933953 Testing by JMJD - SQL Unique 26F22541-28B4-437F-A239-A61C12E15206 79
login 2023-11-27 10:44:35.4782571 Testing by JMJD - SQL Unique 9B89E11B-F736-42EB-A11E-BD48310B0F9A 80
login 2023-11-27 10:44:35.8555266 Testing by JMJD - SQL Unique A15AD53D-A5A4-4045-BF47-1868BA8EAF4D 81
login 2023-11-27 10:44:36.3291021 Testing by JMJD - SQL Unique DAD45F9C-871F-4E50-957A-06696BAAD71D 82
login 2023-11-27 10:44:36.7298317 Testing by JMJD - SQL Unique C9EFF2C5-29B9-4348-B31B-A31C10FF674F 83
login 2023-11-27 10:44:37.5306220 Testing by JMJD - SQL Unique CA0562F0-CF9B-4BEE-B229-361105CD4482 84
login 2023-11-27 10:44:38.0614300 Testing by JMJD - SQL Unique C1F847A8-DC07-4FA3-B922-2BFC263E5CF5 85
login 2023-11-27 10:44:38.4204144 Testing by JMJD - SQL Unique DDF50636-3D36-4AA2-9C7F-A10DB098750D 86
login 2023-11-27 10:44:38.9137864 Testing by JMJD - SQL Unique 1DBE9971-CD04-4769-87F0-C02DF54610FD 87
login 2023-11-27 10:44:39.4501666 Testing by JMJD - SQL Unique AD6733EB-DE62-4669-B726-6B653F918E74 88
login 2023-11-27 10:44:40.0333471 Testing by JMJD - SQL Unique 3A986AE6-5A9A-4950-94D8-F42E8918D285 89
login 2023-11-27 10:44:40.4224193 Testing by JMJD - SQL Unique DE39C104-05A2-44B5-8D2D-954EFE13115E 90
login 2023-11-27 10:44:40.7971343 Testing by JMJD - SQL Unique A716A2CD-BE3F-4D54-9994-69B47CE17922 91
login 2023-11-27 10:44:41.2742748 Testing by JMJD - SQL Unique 2696C77C-2CCB-4DB2-BE84-DE206E7BB5D3 92
login 2023-11-27 10:44:41.6549745 Testing by JMJD - SQL Unique 187A0568-6645-489C-B985-E9EEAB900C4D 93
login 2023-11-27 10:44:42.1528854 Testing by JMJD - SQL Unique 70AB6F8B-1EFC-4A00-9CC7-00327535938C 94
login 2023-11-27 10:44:42.5191652 Testing by JMJD - SQL Unique 9D42B037-4266-49A1-BA91-2EA8305019E9 95
login 2023-11-27 10:44:42.8860994 Testing by JMJD - SQL Unique C286F1D2-69EB-41C4-939F-0EE46D74107E 96
login 2023-11-27 10:44:43.3517048 Testing by JMJD - SQL Unique A6204050-3479-48CC-8EF9-BD0D3FAC48C8 97
login 2023-11-27 10:44:43.8439214 Testing by JMJD - SQL Unique 64ECF105-6D8C-4537-96E2-D3954121551E 98
login 2023-11-27 10:44:44.2108147 Testing by JMJD - SQL Unique 06B89BBA-411A-4888-AA5D-C7D05020E253 99
login 2023-11-27 10:44:44.5836457 Testing by JMJD - SQL Unique EB3F3B4C-1644-4197-8C8E-F99538E3E031 100
login 2023-11-27 10:44:44.9495636 Testing by JMJD - SQL Unique F66ABB18-7028-47F5-A3C8-AD67D8F9C294 101
login 2023-11-27 10:44:45.4315576 Testing by JMJD - SQL Unique B3ABC039-9647-4737-8077-B7F4AF31E582 102
login 2023-11-27 10:44:45.8129010 Testing by JMJD - SQL Unique A4AB2302-E379-41BE-B12A-8360B9923DC1 103
login 2023-11-27 10:44:46.1830080 Testing by JMJD - SQL Unique 082E8239-1361-48D5-9C45-9824A5548971 104
login 2023-11-27 10:44:46.6643280 Testing by JMJD - SQL Unique 750AD80F-D1FD-41FD-91C3-248E2ADA9745 105
login 2023-11-27 10:44:47.0291074 Testing by JMJD - SQL Unique 67943FF2-6BE8-43A6-9D74-838E50F4B9E9 106
login 2023-11-27 10:44:47.5099507 Testing by JMJD - SQL Unique C405BF05-FC3E-47A0-BD90-019758160398 107
login 2023-11-27 10:44:47.9298079 Testing by JMJD - SQL Unique 42DF555B-9B80-43A3-8279-5BEFE29908DC 108
login 2023-11-27 10:44:48.4068833 Testing by JMJD - SQL Unique 040A33EC-76B5-4FB1-8C2E-C4C8082423FF 109
login 2023-11-27 10:44:48.9200983 Testing by JMJD - SQL Unique 4843B599-FFD5-4AB5-BF94-B2520A45FF16 110
login 2023-11-27 10:44:49.3980265 Testing by JMJD - SQL Unique 76094B1F-352B-40FF-B835-826C09467A30 111
login 2023-11-27 10:44:49.7629872 Testing by JMJD - SQL Unique 1C1BE667-6436-4C0A-9FC5-2D081CDB857E 112
login 2023-11-27 10:44:50.2298332 Testing by JMJD - SQL Unique 96218684-8FAD-4937-951E-E13AB397C1BB 113
login 2023-11-27 10:44:50.5982034 Testing by JMJD - SQL Unique 36A0D6DD-AF00-4EE1-BA7A-9F3CDEA86EEC 114
login 2023-11-27 10:44:50.9636013 Testing by JMJD - SQL Unique A9B43563-CF47-4549-B47D-5ACBE71012AB 115
login 2023-11-27 10:44:51.3276884 Testing by JMJD - SQL Unique 1E96E9FF-FB1B-41D8-88B6-57ABDA7E6CA6 116
login 2023-11-27 10:44:51.7998428 Testing by JMJD - SQL Unique 11A84CCF-E141-4B3B-9A1C-758190A346E7 117
login 2023-11-27 10:44:52.2671502 Testing by JMJD - SQL Unique BB9BCBE0-38DA-4A81-9E09-DBC335382AAF 118
login 2023-11-27 10:44:52.7351390 Testing by JMJD - SQL Unique F6338059-A304-4D2E-A5EB-EFC947159DE1 119
login 2023-11-27 10:44:53.2012630 Testing by JMJD - SQL Unique A518AD50-53F5-4FF7-8396-A5A2BEC2E358 120
login 2023-11-27 10:44:53.5806319 Testing by JMJD - SQL Unique 2B0E7935-C64F-4991-99A8-E3E17E64ABEF 121
login 2023-11-27 10:44:53.9437011 Testing by JMJD - SQL Unique 14BD1206-1C16-4C87-ADDE-100B05DE6C48 122
login 2023-11-27 10:44:54.4130997 Testing by JMJD - SQL Unique F7D82606-CDE7-47D3-ABEB-2DA2F186D499 124
login 2023-11-27 10:44:54.8270659 Testing by JMJD - SQL Unique 5528F45B-79F9-4E8B-B03D-D0896899ADEA 125
login 2023-11-27 10:44:55.1895067 Testing by JMJD - SQL Unique DEF9CF29-D69C-4073-A6CD-96BB3D60A5AB 126
login 2023-11-27 10:44:55.5613989 Testing by JMJD - SQL Unique EACAF0B9-C852-4F21-9896-237ACD93FD7F 127
login 2023-11-27 10:44:56.0186270 Testing by JMJD - SQL Unique 65484AE4-06CD-440B-A1D9-C750236E04DB 128
login 2023-11-27 10:44:56.5813187 Testing by JMJD - SQL Unique 01F5BD6F-C0CB-42DD-B75B-12DCFBAB7EAF 129
- Once we have this one, let's see how it went. More than 200 operations from 2023-11-27 10:46:00.5605246 to 2023-11-27 10:46:00.8593112 with CPU < 50% and 34.66% of session percent.
name timestamp client_app_name client_connection_id session_id
login 2023-11-27 10:46:00.5605246 Testing by JMJD - SQL Unique D59A3F48-CAE5-4D91-8FF9-D3751984B58F 273
login 2023-11-27 10:46:00.5605372 Testing by JMJD - SQL Unique 84EAB2E9-5FE7-4EBE-9C9D-2F55717E229A 271
login 2023-11-27 10:46:00.5609806 Testing by JMJD - SQL Unique D2BC8D7C-974B-4B8F-AD24-318B535C10F0 276
login 2023-11-27 10:46:00.5612032 Testing by JMJD - SQL Unique 8AA43764-0DD7-4BBD-ADDB-588EB5B2DB42 275
login 2023-11-27 10:46:00.5613842 Testing by JMJD - SQL Unique 99AEDDC6-E99A-4DF1-ABBE-051745F1E8C2 269
login 2023-11-27 10:46:00.5617300 Testing by JMJD - SQL Unique 734A83CE-EBF8-4940-BF65-2561FBA264D8 270
login 2023-11-27 10:46:00.5621432 Testing by JMJD - SQL Unique 1C208E5B-B3BF-4442-B90C-8AE35C512072 274
login 2023-11-27 10:46:00.5624127 Testing by JMJD - SQL Unique 41CE0EF5-CFC5-4B7D-852D-363ABC2C1F02 272
login 2023-11-27 10:46:00.5763789 Testing by JMJD - SQL Unique 48D7F03F-DD7B-45C3-BE4F-B362C303AC2B 265
login 2023-11-27 10:46:00.5774393 Testing by JMJD - SQL Unique 98F34251-A806-4B39-A1C1-098033EF9006 266
login 2023-11-27 10:46:00.5774732 Testing by JMJD - SQL Unique 65F0EF8F-0D2C-4763-AC95-F811C69A27E9 268
login 2023-11-27 10:46:00.5776777 Testing by JMJD - SQL Unique 4F72A4B1-DDCA-40FB-8E7D-3C13CFCF9781 262
login 2023-11-27 10:46:00.5781780 Testing by JMJD - SQL Unique CCA16E60-5149-4C39-B174-6915688CC2E3 267
login 2023-11-27 10:46:00.5783882 Testing by JMJD - SQL Unique 82AE6FFB-2713-467B-80DC-B8AE18FE2C98 264
login 2023-11-27 10:46:00.5805276 Testing by JMJD - SQL Unique 467D99EC-2242-440A-9FCE-54335B4FFA15 263
login 2023-11-27 10:46:00.5814869 Testing by JMJD - SQL Unique 06D71AB3-BF77-40D3-B441-08F3293A4BCC 261
login 2023-11-27 10:46:00.5917416 Testing by JMJD - SQL Unique E6509570-41C0-4C15-8864-C1EDB686D297 257
login 2023-11-27 10:46:00.5920256 Testing by JMJD - SQL Unique 41F871B5-EC7A-43E1-9F78-C4A6F76AA87F 256
login 2023-11-27 10:46:00.5927708 Testing by JMJD - SQL Unique 8B55AF40-941E-488D-9608-E0F1EA72EDE1 259
login 2023-11-27 10:46:00.5927984 Testing by JMJD - SQL Unique 64CF15E7-10FD-4976-A135-65713D6B7C92 253
login 2023-11-27 10:46:00.5930033 Testing by JMJD - SQL Unique 5C82BBF2-B310-4617-A041-61ED55AC7DAD 254
login 2023-11-27 10:46:00.5931963 Testing by JMJD - SQL Unique 596871B5-782B-49C2-83FB-5400B6641CE9 258
login 2023-11-27 10:46:00.5933714 Testing by JMJD - SQL Unique A452C026-FE36-4483-B091-66158308EE59 255
login 2023-11-27 10:46:00.5985870 Testing by JMJD - SQL Unique 802639D9-4CB0-4264-94BC-0764DB51EE53 260
login 2023-11-27 10:46:00.6071507 Testing by JMJD - SQL Unique 9729337C-2808-4CDC-8AD9-659E34969ECB 252
login 2023-11-27 10:46:00.6075342 Testing by JMJD - SQL Unique CE958CAF-D025-49C8-BF14-DFB75DFFD9BA 251
login 2023-11-27 10:46:00.6098043 Testing by JMJD - SQL Unique 983AE372-612F-45A5-8EB0-FC0C8F385418 250
login 2023-11-27 10:46:00.6105846 Testing by JMJD - SQL Unique B64B8A56-2F59-457D-A479-10C87910DCE3 249
login 2023-11-27 10:46:00.6130403 Testing by JMJD - SQL Unique 756B5639-06AE-4811-8E79-AEB3E95C1762 248
login 2023-11-27 10:46:00.6132711 Testing by JMJD - SQL Unique 01B156DB-E84D-46B6-B798-7C56B7FCF1B1 247
login 2023-11-27 10:46:00.6134761 Testing by JMJD - SQL Unique 9767990F-EF4C-4BF1-AC28-780760046587 246
login 2023-11-27 10:46:00.6161293 Testing by JMJD - SQL Unique 40A2F056-19F1-49A8-ACF7-BDFBDD0A7EAA 244
login 2023-11-27 10:46:00.6177301 Testing by JMJD - SQL Unique F47BED9D-90ED-44A6-957A-E607D483C5F8 245
login 2023-11-27 10:46:00.6188724 Testing by JMJD - SQL Unique EFDD6215-9EBD-4722-A0E4-5617DD542B38 242
login 2023-11-27 10:46:00.6190535 Testing by JMJD - SQL Unique 2AC0E667-C476-4C85-BE94-399986BC53A6 243
login 2023-11-27 10:46:00.6193785 Testing by JMJD - SQL Unique FA0C8EDA-E50B-417B-A825-DC0529AD14D6 241
login 2023-11-27 10:46:00.6199257 Testing by JMJD - SQL Unique B77ADA91-A7F2-4973-B772-FD9B73E63286 239
login 2023-11-27 10:46:00.6208711 Testing by JMJD - SQL Unique C5E2C94E-DCC4-4E3F-A4BC-F6E8B18812E9 240
login 2023-11-27 10:46:00.6220614 Testing by JMJD - SQL Unique 529885ED-F96D-4477-B3C7-229DDAA5366F 237
login 2023-11-27 10:46:00.6242741 Testing by JMJD - SQL Unique 0A7AF183-464F-4FE1-9A78-804A899EC7E4 236
login 2023-11-27 10:46:00.6249088 Testing by JMJD - SQL Unique 3226E92C-A48F-4718-83D9-94146A4171F5 238
login 2023-11-27 10:46:00.6251662 Testing by JMJD - SQL Unique D42E01C3-64AA-4B14-8BD8-9BCB6B4FD585 235
login 2023-11-27 10:46:00.6277925 Testing by JMJD - SQL Unique 4F3BB390-09ED-42C8-B6F7-EF1A455A0598 232
login 2023-11-27 10:46:00.6280229 Testing by JMJD - SQL Unique 4CBA2C72-5684-456B-BB2E-3C57B02EBA2E 234
login 2023-11-27 10:46:00.6285496 Testing by JMJD - SQL Unique E85E9054-BA63-4002-AA84-8C19B8B0FFAB 233
login 2023-11-27 10:46:00.6288087 Testing by JMJD - SQL Unique 467B2A2F-569B-4E58-9FB8-3A64D38A3DFF 231
login 2023-11-27 10:46:00.6293205 Testing by JMJD - SQL Unique 2E85D47F-DABC-4095-B88F-DE6EFCB4816B 229
login 2023-11-27 10:46:00.6295367 Testing by JMJD - SQL Unique 801A887B-130B-43EC-AF07-63FC80D1E2A9 230
login 2023-11-27 10:46:00.6356914 Testing by JMJD - SQL Unique 995C2B3F-108E-42CF-BBE2-EFF96B715F40 228
login 2023-11-27 10:46:00.6359400 Testing by JMJD - SQL Unique 382CA44D-A5AF-457E-A14D-D83F2C78433E 227
login 2023-11-27 10:46:00.6378450 Testing by JMJD - SQL Unique D677EE63-CCE6-4AA9-BE42-ED536C578848 225
login 2023-11-27 10:46:00.6390050 Testing by JMJD - SQL Unique 9378343C-8B2A-4D48-8C58-AD49D45E62CE 222
login 2023-11-27 10:46:00.6395002 Testing by JMJD - SQL Unique 033C07EC-DECE-489F-AC69-96E0C40394AA 223
login 2023-11-27 10:46:00.6404851 Testing by JMJD - SQL Unique 74347A84-39BB-4508-B85A-06F2513DE278 219
login 2023-11-27 10:46:00.6407057 Testing by JMJD - SQL Unique 738D0B76-E96C-4AC8-80C3-A958B881327C 224
login 2023-11-27 10:46:00.6408841 Testing by JMJD - SQL Unique 6F2258D0-4ECF-49CC-BDFA-C5A9F808D0EE 221
login 2023-11-27 10:46:00.6410828 Testing by JMJD - SQL Unique F72B0B76-15EE-4A39-8691-0E2D5205EE97 220
login 2023-11-27 10:46:00.6419274 Testing by JMJD - SQL Unique 498FE40A-16ED-44B4-94D2-FFA231F48DF9 226
login 2023-11-27 10:46:00.6453749 Testing by JMJD - SQL Unique 722F34AA-2221-4240-8322-CD26EA71E59C 218
login 2023-11-27 10:46:00.6456132 Testing by JMJD - SQL Unique 3DC0E06E-8607-4E9D-B39F-768EDD883036 216
login 2023-11-27 10:46:00.6459559 Testing by JMJD - SQL Unique D0DC8609-28BE-496F-9890-B09A5F2D5CED 217
login 2023-11-27 10:46:00.6463987 Testing by JMJD - SQL Unique FCDA4910-C03F-47CD-BDF9-1CDBC95297D5 215
login 2023-11-27 10:46:00.6473895 Testing by JMJD - SQL Unique 006968B5-53CD-4DE5-B56D-5BB9BA17133C 213
login 2023-11-27 10:46:00.6509526 Testing by JMJD - SQL Unique 0EFAEB85-C19F-4349-9E26-38404CA73E41 214
login 2023-11-27 10:46:00.6526392 Testing by JMJD - SQL Unique BF99642A-70A3-45AB-9B21-417EEF7CA5B6 211
login 2023-11-27 10:46:00.6529602 Testing by JMJD - SQL Unique F0B91060-7C10-4A6D-95CB-395A8EB313D3 210
login 2023-11-27 10:46:00.6535634 Testing by JMJD - SQL Unique DE9842DA-3EC9-4B52-892C-DDDB29EEB83F 207
login 2023-11-27 10:46:00.6545964 Testing by JMJD - SQL Unique 55CF8383-3320-4EE2-AAB5-597663AB9FCA 212
login 2023-11-27 10:46:00.6547969 Testing by JMJD - SQL Unique F5561467-8C82-4713-A733-68D96B15D77B 209
login 2023-11-27 10:46:00.6550093 Testing by JMJD - SQL Unique 08DD1272-CF62-4B40-94EA-32628239A64F 208
login 2023-11-27 10:46:00.6588838 Testing by JMJD - SQL Unique 402B279C-E2AE-48C7-B7A4-44DB92E5AB27 206
login 2023-11-27 10:46:00.6602934 Testing by JMJD - SQL Unique 31C1FA32-17EA-46B2-A11A-17271221CCCE 205
login 2023-11-27 10:46:00.6615148 Testing by JMJD - SQL Unique 30E332A4-8BE6-45B6-8839-9D77ED182FB3 204
login 2023-11-27 10:46:00.6634367 Testing by JMJD - SQL Unique 7FCF13FC-6787-4A6A-8065-E5B9D408786C 201
login 2023-11-27 10:46:00.6640712 Testing by JMJD - SQL Unique FCB0B37F-E3CF-4BD7-BCDB-4C7E2ADD6620 203
login 2023-11-27 10:46:00.6655306 Testing by JMJD - SQL Unique A2D34ED1-F911-4CBA-9FBC-CE4AF36D67F8 202
login 2023-11-27 10:46:00.6657257 Testing by JMJD - SQL Unique 3DE4E060-DD57-45E5-AF92-746EFC68D925 198
login 2023-11-27 10:46:00.6663265 Testing by JMJD - SQL Unique EF9DE934-CC39-4001-BE03-09FEF6DB2907 199
login 2023-11-27 10:46:00.6665065 Testing by JMJD - SQL Unique 9B447480-9AD2-4322-AAF3-A68A83219C4D 200
login 2023-11-27 10:46:00.6714126 Testing by JMJD - SQL Unique 7B49504D-E342-4C6A-854F-18D57F3822A5 196
login 2023-11-27 10:46:00.6716364 Testing by JMJD - SQL Unique 9D0157B0-ECBC-4815-9BDC-07B488A271E4 197
login 2023-11-27 10:46:00.6730397 Testing by JMJD - SQL Unique 69576A31-B992-4A44-8BB0-C2C56999E761 194
login 2023-11-27 10:46:00.6743119 Testing by JMJD - SQL Unique 1B68B0C9-5D3C-48CF-9AC3-AEC31A67F918 190
login 2023-11-27 10:46:00.6744961 Testing by JMJD - SQL Unique 2C231149-1F7F-463A-BB69-1E875267E642 192
login 2023-11-27 10:46:00.6747511 Testing by JMJD - SQL Unique 98137CAD-58BA-4CD5-B67F-48A44915FD86 191
login 2023-11-27 10:46:00.6759204 Testing by JMJD - SQL Unique 50FF93F7-E28D-4D2B-8436-8AE456DBF80F 189
login 2023-11-27 10:46:00.6777425 Testing by JMJD - SQL Unique 16DA61CD-59DA-4693-B1FD-7C4696384F32 193
login 2023-11-27 10:46:00.6918575 Testing by JMJD - SQL Unique 7A4C7550-E507-4929-932C-8FBE9A1A85FB 195
login 2023-11-27 10:46:00.6939270 Testing by JMJD - SQL Unique 01AC7777-98E1-4EBF-95B4-15D07A198C9E 188
login 2023-11-27 10:46:00.6941338 Testing by JMJD - SQL Unique CEAAC3A5-ADF6-42A9-ADD7-AF9D661286E2 187
login 2023-11-27 10:46:00.6943574 Testing by JMJD - SQL Unique F11F7554-C7CA-4660-BF89-24BF50C2FA7C 183
login 2023-11-27 10:46:00.6946845 Testing by JMJD - SQL Unique 86CC111E-D851-48C8-AD4E-7C581CD7211C 185
login 2023-11-27 10:46:00.6949137 Testing by JMJD - SQL Unique 0E0A0943-1373-4481-9770-08DAD0AAB355 184
login 2023-11-27 10:46:00.6963162 Testing by JMJD - SQL Unique 58E6FFD2-D17F-4954-97D8-95D9DB045202 186
login 2023-11-27 10:46:00.6968368 Testing by JMJD - SQL Unique 0AC610AD-5780-4C73-8E42-DCA927E7B65E 182
login 2023-11-27 10:46:00.7004281 Testing by JMJD - SQL Unique B8702BF8-C9A0-4A53-9869-9EB1CE409C0B 180
login 2023-11-27 10:46:00.7017483 Testing by JMJD - SQL Unique BBB2FBFB-386C-415B-BCF0-7D06FD704449 181
login 2023-11-27 10:46:00.7057129 Testing by JMJD - SQL Unique F96EA836-7BDD-4C14-B948-07A540A488B1 179
login 2023-11-27 10:46:00.7066366 Testing by JMJD - SQL Unique C4800B38-6C94-43C3-95B4-93CB0B7F47BE 178
login 2023-11-27 10:46:00.7068360 Testing by JMJD - SQL Unique DC867F99-09E7-4B18-822A-0A2989665718 177
login 2023-11-27 10:46:00.7078917 Testing by JMJD - SQL Unique 871AC211-7C68-4A9E-98B2-9098BC5C343D 174
login 2023-11-27 10:46:00.7084237 Testing by JMJD - SQL Unique 1A99DA34-07FE-4CAD-A219-B19CFB1A83F8 176
login 2023-11-27 10:46:00.7114590 Testing by JMJD - SQL Unique 49147935-B0A6-4EDB-A878-F919834B86A1 175
login 2023-11-27 10:46:00.7117029 Testing by JMJD - SQL Unique B62772CE-C2C2-498B-B6BE-0DDDB2CE059B 173
login 2023-11-27 10:46:00.7137781 Testing by JMJD - SQL Unique A703ADFD-B359-49C5-BB39-CD260E17F4B8 172
login 2023-11-27 10:46:00.7160086 Testing by JMJD - SQL Unique 4221920C-108F-4C04-988D-710449CA3823 171
login 2023-11-27 10:46:00.7162013 Testing by JMJD - SQL Unique 2DCB4A33-AF51-409A-9806-4E372A2F774D 169
login 2023-11-27 10:46:00.7164295 Testing by JMJD - SQL Unique E5EC59A9-C3CA-4F20-8423-EB7C405BCEDE 170
login 2023-11-27 10:46:00.7175631 Testing by JMJD - SQL Unique A25AA718-BAD0-4175-9214-0695E77A3FA4 165
login 2023-11-27 10:46:00.7184663 Testing by JMJD - SQL Unique BDB2B242-79A4-45A2-9376-A377D0CE14D0 168
login 2023-11-27 10:46:00.7187860 Testing by JMJD - SQL Unique D144B188-8F09-4F26-A15A-C20D7B4B9A4F 167
login 2023-11-27 10:46:00.7222161 Testing by JMJD - SQL Unique 47785B79-2C80-47A0-B10C-413721065186 164
login 2023-11-27 10:46:00.7227836 Testing by JMJD - SQL Unique B24EB444-9314-43A4-9E49-83372FC49AAF 166
login 2023-11-27 10:46:00.7248452 Testing by JMJD - SQL Unique BEF5C746-7547-43B5-A775-24E6488195C0 163
login 2023-11-27 10:46:00.7254347 Testing by JMJD - SQL Unique 3A3B638C-CE97-4F86-A9C0-211496F232D3 162
login 2023-11-27 10:46:00.7261478 Testing by JMJD - SQL Unique 9173F4DA-706A-4400-80CA-41630B1736BB 161
login 2023-11-27 10:46:00.7263346 Testing by JMJD - SQL Unique 9657EDE4-1D28-4A07-8CCE-6A1A212E1748 159
login 2023-11-27 10:46:00.7270909 Testing by JMJD - SQL Unique 7D7C8B24-B35E-4B37-BE5F-FB439AF78079 160
login 2023-11-27 10:46:00.7297161 Testing by JMJD - SQL Unique 753F949D-7D18-4A5A-96C7-839DCD0C16CD 156
login 2023-11-27 10:46:00.7301261 Testing by JMJD - SQL Unique EBEAC5B6-2123-4284-997E-7209BBF3ACAF 157
login 2023-11-27 10:46:00.7308407 Testing by JMJD - SQL Unique E184BF0E-ADE5-41E6-9D12-1CC85DF8B59A 158
login 2023-11-27 10:46:00.7340696 Testing by JMJD - SQL Unique 473C2076-E94E-41D3-8057-4CACDA1C53F5 155
login 2023-11-27 10:46:00.7344349 Testing by JMJD - SQL Unique 6F8DE943-E993-4AA8-B5D0-66DD9FCC7AAD 154
login 2023-11-27 10:46:00.7381817 Testing by JMJD - SQL Unique B9F0C9AF-4D48-43F8-9748-930329C3BEA1 152
login 2023-11-27 10:46:00.7383866 Testing by JMJD - SQL Unique CDC6F921-0C3A-4EBC-BB63-DDCD2D9B3834 153
login 2023-11-27 10:46:00.7395011 Testing by JMJD - SQL Unique BF6829CB-3681-4091-ABC7-E7FD6F91F451 150
login 2023-11-27 10:46:00.7404095 Testing by JMJD - SQL Unique 9A158A57-BEBF-4FF0-B15C-E283C03C680C 151
login 2023-11-27 10:46:00.7424891 Testing by JMJD - SQL Unique 00288740-B2FA-4DD1-A09C-4533EE1A4676 149
login 2023-11-27 10:46:00.7428020 Testing by JMJD - SQL Unique 34528F50-A34F-4E7D-AAC1-EECF2BE24FC3 148
login 2023-11-27 10:46:00.7451448 Testing by JMJD - SQL Unique E71EB385-7220-4093-B3FF-8891C9728CDC 144
login 2023-11-27 10:46:00.7458439 Testing by JMJD - SQL Unique A45DE679-7479-4F0D-AD51-0ED4DCA79FE8 147
login 2023-11-27 10:46:00.7460927 Testing by JMJD - SQL Unique 8223051B-3AB3-4466-8886-B878F5C6B9C0 146
login 2023-11-27 10:46:00.7466325 Testing by JMJD - SQL Unique 14A50570-AE8A-4BD6-9BDE-8A2F4B817F78 145
login 2023-11-27 10:46:00.7468230 Testing by JMJD - SQL Unique D15D9F09-FB8B-4243-98CA-10B529AC69AA 143
login 2023-11-27 10:46:00.7520416 Testing by JMJD - SQL Unique E6388475-6AE6-4499-B327-A0D785C43EAC 140
login 2023-11-27 10:46:00.7532556 Testing by JMJD - SQL Unique E86A4E8F-4A1E-4257-8830-B0D78A3E97C7 142
login 2023-11-27 10:46:00.7535719 Testing by JMJD - SQL Unique DF643FF8-3DBF-4287-B55F-402FC9380448 141
login 2023-11-27 10:46:00.7537448 Testing by JMJD - SQL Unique F529BFE2-93DB-4CB0-B16F-3506D41BD6AF 139
login 2023-11-27 10:46:00.7570528 Testing by JMJD - SQL Unique B655D60A-3829-45B0-ADD8-279F05FC842D 138
login 2023-11-27 10:46:00.7578690 Testing by JMJD - SQL Unique 52D272E1-5C85-447A-A3EF-135CC332AF92 134
login 2023-11-27 10:46:00.7585291 Testing by JMJD - SQL Unique 405B3CF7-8C47-4FFE-9211-F2382D976EC3 135
login 2023-11-27 10:46:00.7587268 Testing by JMJD - SQL Unique F6BA8A02-E6CF-430B-A886-8EEA3127733B 136
login 2023-11-27 10:46:00.7597093 Testing by JMJD - SQL Unique 80DDC723-51C4-4FA2-AEF5-8F25C896BDD2 137
login 2023-11-27 10:46:00.7614264 Testing by JMJD - SQL Unique 6560E110-AF3E-45D8-A608-C9969421EEC4 132
login 2023-11-27 10:46:00.7640368 Testing by JMJD - SQL Unique 12FD656E-64FF-4C89-8B7E-6FE979C4413C 131
login 2023-11-27 10:46:00.7656950 Testing by JMJD - SQL Unique 9B5770F7-9D8D-4161-9BCA-B34FEE78E88A 130
login 2023-11-27 10:46:00.7667870 Testing by JMJD - SQL Unique 91DCB33A-30EE-4E9F-A6D8-3A9BD859831E 133
login 2023-11-27 10:46:00.7677848 Testing by JMJD - SQL Unique 01F5BD6F-C0CB-42DD-B75B-12DCFBAB7EAF 129
login 2023-11-27 10:46:00.7679790 Testing by JMJD - SQL Unique 65484AE4-06CD-440B-A1D9-C750236E04DB 128
login 2023-11-27 10:46:00.7702423 Testing by JMJD - SQL Unique EACAF0B9-C852-4F21-9896-237ACD93FD7F 127
login 2023-11-27 10:46:00.7712063 Testing by JMJD - SQL Unique DEF9CF29-D69C-4073-A6CD-96BB3D60A5AB 126
login 2023-11-27 10:46:00.7721467 Testing by JMJD - SQL Unique 5528F45B-79F9-4E8B-B03D-D0896899ADEA 125
login 2023-11-27 10:46:00.7772339 Testing by JMJD - SQL Unique F7D82606-CDE7-47D3-ABEB-2DA2F186D499 124
login 2023-11-27 10:46:00.7866839 Testing by JMJD - SQL Unique 14BD1206-1C16-4C87-ADDE-100B05DE6C48 122
login 2023-11-27 10:46:00.7893111 Testing by JMJD - SQL Unique A518AD50-53F5-4FF7-8396-A5A2BEC2E358 120
login 2023-11-27 10:46:00.7905628 Testing by JMJD - SQL Unique F6338059-A304-4D2E-A5EB-EFC947159DE1 119
login 2023-11-27 10:46:00.7913152 Testing by JMJD - SQL Unique 1E96E9FF-FB1B-41D8-88B6-57ABDA7E6CA6 116
login 2023-11-27 10:46:00.7920453 Testing by JMJD - SQL Unique BB9BCBE0-38DA-4A81-9E09-DBC335382AAF 118
login 2023-11-27 10:46:00.7944590 Testing by JMJD - SQL Unique 11A84CCF-E141-4B3B-9A1C-758190A346E7 117
login 2023-11-27 10:46:00.7949198 Testing by JMJD - SQL Unique 2B0E7935-C64F-4991-99A8-E3E17E64ABEF 121
login 2023-11-27 10:46:00.7986795 Testing by JMJD - SQL Unique 36A0D6DD-AF00-4EE1-BA7A-9F3CDEA86EEC 114
login 2023-11-27 10:46:00.7995343 Testing by JMJD - SQL Unique 96218684-8FAD-4937-951E-E13AB397C1BB 113
login 2023-11-27 10:46:00.8015556 Testing by JMJD - SQL Unique 76094B1F-352B-40FF-B835-826C09467A30 111
login 2023-11-27 10:46:00.8019820 Testing by JMJD - SQL Unique 1C1BE667-6436-4C0A-9FC5-2D081CDB857E 112
login 2023-11-27 10:46:00.8024274 Testing by JMJD - SQL Unique A9B43563-CF47-4549-B47D-5ACBE71012AB 115
login 2023-11-27 10:46:00.8039269 Testing by JMJD - SQL Unique 4843B599-FFD5-4AB5-BF94-B2520A45FF16 110
login 2023-11-27 10:46:00.8051279 Testing by JMJD - SQL Unique 040A33EC-76B5-4FB1-8C2E-C4C8082423FF 109
login 2023-11-27 10:46:00.8053309 Testing by JMJD - SQL Unique 42DF555B-9B80-43A3-8279-5BEFE29908DC 108
login 2023-11-27 10:46:00.8061450 Testing by JMJD - SQL Unique C405BF05-FC3E-47A0-BD90-019758160398 107
login 2023-11-27 10:46:00.8066036 Testing by JMJD - SQL Unique 67943FF2-6BE8-43A6-9D74-838E50F4B9E9 106
login 2023-11-27 10:46:00.8096368 Testing by JMJD - SQL Unique 750AD80F-D1FD-41FD-91C3-248E2ADA9745 105
login 2023-11-27 10:46:00.8109640 Testing by JMJD - SQL Unique 082E8239-1361-48D5-9C45-9824A5548971 104
login 2023-11-27 10:46:00.8135545 Testing by JMJD - SQL Unique A4AB2302-E379-41BE-B12A-8360B9923DC1 103
login 2023-11-27 10:46:00.8148581 Testing by JMJD - SQL Unique B3ABC039-9647-4737-8077-B7F4AF31E582 102
login 2023-11-27 10:46:00.8163897 Testing by JMJD - SQL Unique F66ABB18-7028-47F5-A3C8-AD67D8F9C294 101
login 2023-11-27 10:46:00.8178093 Testing by JMJD - SQL Unique 06B89BBA-411A-4888-AA5D-C7D05020E253 99
login 2023-11-27 10:46:00.8179854 Testing by JMJD - SQL Unique EB3F3B4C-1644-4197-8C8E-F99538E3E031 100
login 2023-11-27 10:46:00.8214708 Testing by JMJD - SQL Unique A6204050-3479-48CC-8EF9-BD0D3FAC48C8 97
login 2023-11-27 10:46:00.8216982 Testing by JMJD - SQL Unique 64ECF105-6D8C-4537-96E2-D3954121551E 98
login 2023-11-27 10:46:00.8242674 Testing by JMJD - SQL Unique C286F1D2-69EB-41C4-939F-0EE46D74107E 96
login 2023-11-27 10:46:00.8270319 Testing by JMJD - SQL Unique 9D42B037-4266-49A1-BA91-2EA8305019E9 95
login 2023-11-27 10:46:00.8298905 Testing by JMJD - SQL Unique 187A0568-6645-489C-B985-E9EEAB900C4D 93
login 2023-11-27 10:46:00.8301312 Testing by JMJD - SQL Unique 70AB6F8B-1EFC-4A00-9CC7-00327535938C 94
login 2023-11-27 10:46:00.8330678 Testing by JMJD - SQL Unique 2696C77C-2CCB-4DB2-BE84-DE206E7BB5D3 92
login 2023-11-27 10:46:00.8332845 Testing by JMJD - SQL Unique DE39C104-05A2-44B5-8D2D-954EFE13115E 90
login 2023-11-27 10:46:00.8333545 Testing by JMJD - SQL Unique A716A2CD-BE3F-4D54-9994-69B47CE17922 91
login 2023-11-27 10:46:00.8416815 Testing by JMJD - SQL Unique DDF50636-3D36-4AA2-9C7F-A10DB098750D 86
login 2023-11-27 10:46:00.8419510 Testing by JMJD - SQL Unique 3A986AE6-5A9A-4950-94D8-F42E8918D285 89
login 2023-11-27 10:46:00.8434196 Testing by JMJD - SQL Unique 1DBE9971-CD04-4769-87F0-C02DF54610FD 87
login 2023-11-27 10:46:00.8443097 Testing by JMJD - SQL Unique AD6733EB-DE62-4669-B726-6B653F918E74 88
login 2023-11-27 10:46:00.8472025 Testing by JMJD - SQL Unique CA0562F0-CF9B-4BEE-B229-361105CD4482 84
login 2023-11-27 10:46:00.8480833 Testing by JMJD - SQL Unique C1F847A8-DC07-4FA3-B922-2BFC263E5CF5 85
login 2023-11-27 10:46:00.8490098 Testing by JMJD - SQL Unique C9EFF2C5-29B9-4348-B31B-A31C10FF674F 83
login 2023-11-27 10:46:00.8515278 Testing by JMJD - SQL Unique DAD45F9C-871F-4E50-957A-06696BAAD71D 82
login 2023-11-27 10:46:00.8518519 Testing by JMJD - SQL Unique A15AD53D-A5A4-4045-BF47-1868BA8EAF4D 81
login 2023-11-27 10:46:00.8520748 Testing by JMJD - SQL Unique 9B89E11B-F736-42EB-A11E-BD48310B0F9A 80
login 2023-11-27 10:46:00.8555748 Testing by JMJD - SQL Unique 26F22541-28B4-437F-A239-A61C12E15206 79
login 2023-11-27 10:46:00.8593112 Testing by JMJD - SQL Unique 59D4DDFC-5029-41B8-BAAA-61E358186B37 77
- What happens when the database is S3 ( 1vCore):
- The Pre-Warm time was the same.
- But, executing the process take less, CPU and MaxSession usage.
Code
using System;
using Polly;
using Microsoft.Data.SqlClient;
using System.Diagnostics;
using System.Threading;
using System.Threading.Tasks;
using System.IO;
using System.Collections;
namespace HealthCheck
{
class Program
{
const string LogFolder = "c:\\temp\\Mydata";
const string LogFilePath = LogFolder + "\\log";
const string LogExtPath = ".log";
static async Task Main(string[] args)
{
DeleteDirectoryIfExists(LogFolder);
int numberOfThreads = 50000; //Nr Threads
int maxDegreeOfParallelism = 200; //Nr Threads to run concurrent
string connectionString = "data source=tcp:servername.database.windows.net,1433;initial catalog=test;User Id=username@microsoft.com;Password=Pwd1!;ConnectRetryCount=3;ConnectRetryInterval=10;Connection Timeout=5;Max Pool Size=200;MultipleActiveResultSets=false;Min Pool Size=0;Pooling=true;Authentication=Active Directory Password;PoolBlockingPeriod=NeverBlock;Connection Lifetime=0;Application Name=Testing by JMJD - SQL Unique";
/*Pre-Warm*/
Log($"Pre-Warm - Opening connection",-1);
SqlConnection[] oConnection = new SqlConnection[maxDegreeOfParallelism];
for (int i = 0; i < maxDegreeOfParallelism; i++)
{
Log($"Pre-Warm - Starting {i}", -1);
oConnection[i] = new SqlConnection(connectionString);
oConnection[i].Open();
Log($"Pre-Warm - Done {i} - {oConnection[i].ClientConnectionId.ToString()}", -1);
}
Log($"Pre-Warm - Closing connection", -1);
for (int i = 0; i < maxDegreeOfParallelism; i++)
{
if (oConnection[i].State == System.Data.ConnectionState.Open)
{
Log($"Pre-Warm - Closing {i}", -1);
oConnection[i].Close();
}
}
Log($"Pre-Warm - Starting the process", -1);
var semaphore = new SemaphoreSlim(maxDegreeOfParallelism);
var tasks = new Task[numberOfThreads];
for (int i = 0; i < numberOfThreads; i++)
{
tasks[i] = Task.Run(async () =>
{
await semaphore.WaitAsync();
try
{
await ExecuteQueryAsync(connectionString, false);
}
finally
{
semaphore.Release();
}
});
}
await Task.WhenAll(tasks);
}
static async Task ExecuteQueryAsync(string connectionString, Boolean bDifferentConnString=false)
{
int threadId = Thread.CurrentThread.ManagedThreadId;
TimeSpan ts;
string elapsedTime;
string sConnectionStringDummy = "";
try
{
Log($"Thread {threadId}: Opening the connection",threadId);
if (bDifferentConnString)
{
sConnectionStringDummy = sConnectionStringDummy + connectionString + " T# " + threadId.ToString();
}
else
{
sConnectionStringDummy = sConnectionStringDummy + connectionString;
}
Stopwatch stopWatch = new Stopwatch();
stopWatch.Start();
SqlConnection connection = await ConnectWithRetriesAsync(sConnectionStringDummy, threadId);
ts = stopWatch.Elapsed;
elapsedTime = String.Format("{0:00}:{1:00}:{2:00}.{3:00}",
ts.Hours, ts.Minutes, ts.Seconds,
ts.Milliseconds / 10);
Log($"Thread {threadId}: Connected - {elapsedTime} " + connection.ClientConnectionId.ToString(),threadId);
//Log($"Thread {threadId}: Executing the command",threadId);
SqlCommand command = new SqlCommand("SELECT 1", connection);
command.CommandTimeout = 5;
stopWatch.Reset();
stopWatch.Start();
object result = await ExecuteCommandWithRetriesAsync(command);
stopWatch.Stop();
ts = stopWatch.Elapsed;
elapsedTime = String.Format("{0:00}:{1:00}:{2:00}.{3:00}",
ts.Hours, ts.Minutes, ts.Seconds,
ts.Milliseconds / 10);
Log($"Thread {threadId}: Executed the command - {elapsedTime} - Result: {result}", threadId);
//Log($"Thread {threadId}: Closing the connection");
connection.Close();
}
catch (OperationCanceledException canc)
{
Log($"Thread {threadId}: Error (Cancelation): {canc.Message}", threadId);
}
catch (Exception ex)
{
Log($"Thread {threadId}: - Error (Exception): {ex.Message}", threadId);
}
}
static async Task<SqlConnection> ConnectWithRetriesAsync(string connectionString, int threadId)
{
Log($"Thread {threadId}: Calling", threadId);
SqlConnection connection = new SqlConnection(connectionString);
connection.StatisticsEnabled = true;
Log($"Thread {threadId}: End Calling", threadId);
var policy = Policy
.Handle<Exception>()
.WaitAndRetryAsync(5, retryAttempt => TimeSpan.FromSeconds(connection.ConnectionTimeout * 1.05),
(exception, timespan, retryCount, context) =>
{
Log($"Thread {threadId}: Retry {retryCount} due to {exception.Message}. Will retry in {timespan.TotalSeconds} seconds.", threadId);
});
await policy.ExecuteAsync(async () =>
{
try
{
await connection.OpenAsync();
IDictionary currentStatistics = connection.RetrieveStatistics();
if (currentStatistics["ExecutionTime"].ToString() != "0" || currentStatistics["ConnectionTime"].ToString() != "0")
{
Log("Thread "+ threadId.ToString() + ": " + connection.ClientConnectionId.ToString() + " ExecutionTime: " + currentStatistics["ExecutionTime"] + " ConnectionTime:" + currentStatistics["ConnectionTime"], threadId);
}
}
catch (Exception ex)
{
throw;
}
});
return connection;
}
static async Task<object> ExecuteCommandWithRetriesAsync(SqlCommand command)
{
var policy = Policy
.Handle<Exception>()
.WaitAndRetryAsync(5, retryAttempt => TimeSpan.FromSeconds(command.CommandTimeout * 1.05),
(exception, timespan, retryCount, context) =>
{
Log($"Retry {retryCount} due to {exception.Message}. Will retry in {timespan.TotalSeconds} seconds.");
});
object result = null;
await policy.ExecuteAsync(async () =>
{
try
{
result = await command.ExecuteScalarAsync();
}
catch (Exception ex)
{
throw;
}
});
return result;
}
static void Log(string message, int iThread = 0)
{
var ahora = DateTime.Now;
string logMessage = $"{ahora.ToString("yyyy-MM-dd HH:mm:ss.fff")}: {message}";
string sLogFile = LogFolder + "\\log" + iThread.ToString() + LogExtPath;
//Console.WriteLine(logMessage);
try
{
using (FileStream stream = new FileStream(sLogFile, FileMode.Append, FileAccess.Write, FileShare.ReadWrite))
{
using (StreamWriter writer = new StreamWriter(stream))
{
writer.WriteLine(logMessage);
}
}
}
catch (IOException ex)
{
Console.WriteLine($"Error writing in the log file: {ex.Message}");
}
}
public static void DeleteDirectoryIfExists(string path)
{
try
{
if (Directory.Exists(path))
{
Directory.Delete(path, true);
}
Directory.CreateDirectory(path);
}
catch (Exception ex)
{
Console.WriteLine($"Error deleting the folder: {ex.Message}");
}
}
}
}
Enjoy!
Updated Nov 27, 2023
Version 1.0
Jose_Manuel_Jurado
Microsoft
Microsoft