We usually have service requests when our customers, using different drivers, need to know the connection latency, execution time spent or validate the connection.
For this reason, I developed this small PowerShell script (based on initiative of AzureSQLConnectivityChecker in multiple languages) which will basically connect to the database using 4 drivers/providers and test this.
Under this initiative we are going to share several scripts developed in Java, PHP, Python, C# and PowerShell to explain these details:
Today, we started with SQLConnectivityTestMultiDriver.ps1 PowerShell Script and you are going to be able to:
I hope it you could find useful for your connectivity and query execution test.
Here are the main details:
SELECT 1
SELECT count(Id) FROM PerformanceVarcharnVarchar Where TextToSearch = N'Test Search ' + CONVERT(nvarchar(200),RAND()*(2000000-10000)+10000) OPTION (MAXDOP 1)
2022-08-11 03:33:15 Driver: SQLOLEDB
2022-08-11 03:33:15 Connecting to the database: servername.database.windows.net - DB: dbname...Attempt #1 of 5 - IP:52.138.224.1
2022-08-11 03:33:16 Connected to the database in (ms):324.1887 - Provider:SQLOLEDB -- HostName: Server Version:12.00.0312
2022-08-11 03:33:16 Total Connections Failed : 0
2022-08-11 03:33:16 Total Connections Success: 1
2022-08-11 03:33:16 Total Connections ms : 324
2022-08-11 03:33:16 Ports Count : 1433 0 (0) 11xx: (0) Others:(215)
2022-08-11 03:33:16 Ports Status:
2022-08-11 03:33:16 ---->Established (207)
2022-08-11 03:33:16 ---->TimeWait (5)
2022-08-11 03:33:16 ---->SynSent (2)
2022-08-11 03:33:16 -------------------------------------------------------------------------------------
2022-08-11 03:33:16 Query : SELECT 1
2022-08-11 03:33:16 Iteration : 1 Query 1 / 2
2022-08-11 03:33:16 Time required (ms) : 41,416
2022-08-11 03:33:16 Query Commands Failed : 0
2022-08-11 03:33:16 Query Commands Success: 1
2022-08-11 03:33:16 Query Commands ms : 41
2022-08-11 03:33:16 Total Commands Failed : 0
2022-08-11 03:33:16 Total Commands Success: 1
2022-08-11 03:33:16 TotalCommands ms : 41
2022-08-11 03:33:16 -------------------------------------------------------------------------------------
2022-08-11 03:33:16 Query : SELECT count(Id) FROM PerformanceVarcharnVarchar Where TextToSearch = N'Test Search ' + CONVERT(nvarchar(200),RAND()*(2000000-10000)+10000) OPTION (MAXDOP 1)
2022-08-11 03:33:16 Iteration : 1 Query 2 / 2
2022-08-11 03:33:17 Time required (ms) : 473,7891
2022-08-11 03:33:17 Query Commands Failed : 0
2022-08-11 03:33:17 Query Commands Success: 1
2022-08-11 03:33:17 Query Commands ms : 474
2022-08-11 03:33:17 Total Commands Failed : 0
2022-08-11 03:33:17 Total Commands Success: 2
2022-08-11 03:33:17 TotalCommands ms : 257,5
2022-08-11 03:33:17 ---------------------------------------------------------------------------------------------------------------
2022-08-11 03:33:17 Connecting to the database: servername.database.windows.net - DB: databasena...Attempt #1 of 5 - IP:52.138.224.1
2022-08-11 03:33:17 Connected to the database in (ms):0 - Provider:SQLOLEDB -- HostName: Server Version:12.00.0312
2022-08-11 03:33:17 Total Connections Failed : 0
2022-08-11 03:33:17 Total Connections Success: 2
2022-08-11 03:33:17 Total Connections ms : 162
2022-08-11 03:33:17 Ports Count : 1433 0 (0) 11xx: (0) Others:(215)
2022-08-11 03:33:17 Ports Status:
2022-08-11 03:33:17 ---->Established (207)
2022-08-11 03:33:17 ---->TimeWait (5)
2022-08-11 03:33:17 ---->SynSent (2)
2022-08-11 03:38:00 -------------------------------------------------------------------------------------
2022-08-11 03:38:00 Query : SELECT count(Id) FROM PerformanceVarcharnVarchar Where TextToSearch = N'Test Search ' + CONVERT(nvarchar(200),RAND()*(2000000-10000)+10000) OPTION (MAXDOP 1)
2022-08-11 03:38:00 Iteration : 200 Query 2 / 2
2022-08-11 03:38:01 Time required (ms) : 501,1269
2022-08-11 03:38:01 NetworkServerTime (ms): 535
2022-08-11 03:38:01 Execution Time (ms) : 535
2022-08-11 03:38:01 Connection Time : 1068
2022-08-11 03:38:01 ServerRoundTrips : 2
2022-08-11 03:38:01 BuffersReceived : 2
2022-08-11 03:38:01 SelectRows : 2
2022-08-11 03:38:01 SelectCount : 2
2022-08-11 03:38:01 BytesSent : 390
2022-08-11 03:38:01 BytesReceived : 86
2022-08-11 03:38:01 Query Commands Failed : 0
2022-08-11 03:38:01 Query Commands Success: 200
2022-08-11 03:38:01 Query Commands ms : 204,575
2022-08-11 03:38:01 Total Commands Failed : 0
2022-08-11 03:38:01 Total Commands Success: 400
2022-08-11 03:38:01 TotalCommands ms : 120,0725
2022-08-11 03:38:01 -------------------------------------- SUMMARY -----------------------------------------------------------------
2022-08-11 03:38:01 Total Connections Failed :0
2022-08-11 03:38:01 Total Connections Success:200
2022-08-11 03:38:01 Total Connections Avg ms :2,515
2022-08-11 03:38:01 Total Number Executions :200
2022-08-11 03:38:01 Total Number Queries :2
2022-08-11 03:38:01 Total Number Process :400
2022-08-11 03:38:01 Total Commands Failed :0
2022-08-11 03:38:01 Total Commands Success :400
2022-08-11 03:38:01 Total Commands ms :120,0725
2022-08-11 03:38:01 ------------------- Queries SummarySELECT count(Id) FROM PerformanceVarcharnVarchar Where TextToSearch = N'Test Search ' + CONVERT(nvarchar(200),RAND()*(2000000-10000)+10000) OPTION (MAXDOP 1)
2022-08-11 03:38:01 Query # :SELECT 1
2022-08-11 03:38:01 Total Commands Failed :0
2022-08-11 03:38:01 Total Commands Success:200
2022-08-11 03:38:01 Total Commands ms :35,57
2022-08-11 03:38:01 Query # :SELECT count(Id) FROM PerformanceVarcharnVarchar Where TextToSearch = N'Test Search ' + CONVERT(nvarchar(200),RAND()*(2000000-10000)+10000) OPTION (MAXDOP 1)
2022-08-11 03:38:01 Total Commands Failed :0
2022-08-11 03:38:01 Total Commands Success:200
2022-08-11 03:38:01 Total Commands ms :204,575
2022-08-11 03:38:01 Review: https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/provider-statistics-for-sql-server
2022-08-11 03:38:01 -------------------------------------- SUMMARY -----------------------------------------------------------------
Enjoy!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.