SQL Server connectivity testing (timing ) from client-powershell

Question

Hi , I am looking to measure(timing in seconds) how long will take to connect to SQL server from client.
Basically just connect to sql and run select @@version also enough with powershell .

Output looks:
5/07/2021 3:56:29 PM Dbserver- Reply from course: bytes=32 time=1ms TTL=126

This works from ping. but not sure how I caan get it SQL connectivity with timing
ping.exe -t DBServer |Foreach{"{0} - {1}" -f (Get-Date),$_} > C:\DBA\ping\timing.txt

I following way SQL connectivity. but not sure how to get timing (to connect) and direct to a text file until stop
Invoke-Sqlcmd -Query "SELECT GETDATE() AS TimeOfQuery" -ServerInstance "DBServer"

Answer 1

Hi @Ashwan ,

Please refer to this: Different ways to check SQL Server uptime, which may be helpful for you.

As for directing to a text file

In SSMS, if you use T-SQL, press Ctrl+T(Results to text). If you right click to see the Reports, you can use Print.

Or refer to this: https://blog.sqlauthority.com/2009/10/01/sql-server-sql-server-management-studio-and-client-statistics/, which is about Client Statistics.

Best regards,
Seeya

---

If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi Seeya Thank you for the update. however requirement to is how much time take to get in to the DB.

NO need to check uptime.

That good to have if issue or slow connection to check from application to DB.
Hope you will understand

regards

Answer 3

One way to capture the duration in PowerShell is with a .NET Stopwatch. Below is an example that runs the query in a loop, writing the returned TimeOfQuery and client duration to the file until the script is stopped. Because connections typically take only a few milliseconds, I added a sleep for one second after each iteration.

try {
    while($true) {

        $timer = [System.Diagnostics.Stopwatch]::StartNew()
        $TimeOfQuery = Invoke-Sqlcmd -Query "SELECT GETDATE() AS TimeOfQuery" -ServerInstance "DBServer"
        "$($TimeOfQuery[0].ToString('yyyy-MM-dd HH:mm:ss')) duration $($timer.Elapsed.ToString())" | Out-File "C:\DBA\ping\timing.txt" -Append
        Start-Sleep 1

    }
}
catch {
    throw
}

Importantly, note that with client connection pooling (which apps also typically use), only the first iteration will establish a new connection and incur the overhead of establishing the physical network connection and authentication. Subsequent iterations will simply reuse the pooled connection so mostly query duration and network latency will be measured.

If your intent is to measure the aforementioned connection overhead, add the following after the sleep to clear the connections each iteration:

[System.Data.SqlClient.SqlConnection]::ClearAllPools()

Answer 4

Hi Dan, That is close to my expectation. Thank you very much . however you output come as follows . I think if we know connection start time and connected time , then we can subtract to get time taken/travel time? this duration is not clear how you have calculated

2021-07-07 06:40:44 duration 00:00:00.0029270
2021-07-07 06:40:54 duration 00:00:00.0037380
2021-07-07 06:41:04 duration 00:00:00.0032210
2021-07-07 06:41:14 duration 00:00:00.0035037
2021-07-07 06:41:24 duration 00:00:00.0039628
2021-07-07 06:41:34 duration 00:00:00.0037470
2021-07-07 06:41:44 duration 00:00:00.0034398
2021-07-07 06:41:54 duration 00:00:00.0038772
2021-07-07 06:42:04 duration 00:00:00.0040108
2021-07-07 06:42:14 duration 00:00:00.0032997
2021-07-07 06:42:24 duration 00:00:00.0042999
2021-07-07 06:42:34 duration 00:00:00.0033013
2021-07-07 06:42:44 duration 00:00:00.0030208
2021-07-07 06:42:54 duration 00:00:00.0033746

Answer 5

Answering the additional question in your comment, the Stopwatch Elapsed property performs the duration calculation intrinsically with slightly less code, which is why I used that instead of capturing the start/end time and calculating the difference. The duration is formatted as hours, minutes seconds, and fractional seconds but you can report milliseconds too if you prefer. Note that the milliseconds value is not rounded and you might observe minor timing anomalies with very small durations on multi-processor machines.

Below in another example that uses both methods, along with milliseconds.

try {

    while($true) {

        $timer = [System.Diagnostics.Stopwatch]::StartNew()
        $startTime = [DateTime]::Now
        $TimeOfQuery = Invoke-Sqlcmd -Query "SELECT GETDATE() AS TimeOfQuery" -ServerInstance "DBServer"
        $timer.Stop()
        $endTime = [DateTime]::Now
        $timeDifference = $endTime.Subtract($startTime)

        "startTime=$($startTime.ToString('yyyy-MM-dd HH:mm:ss.fff'))" + `
            ", timeOfQuery=$($TimeOfQuery[0].ToString('yyyy-MM-dd HH:mm:ss.fff'))" + `
            ", endTime=$($endTime.ToString('yyyy-MM-dd HH:mm:ss.fff'))" + `
            ", timeDifferenceDuration $($timeDifference.ToString())" + `
            ", timeDifferenceMilliseconds $($timeDifference.Milliseconds)" + `
            ", stopwatchDuration=$($timer.Elapsed.ToString())" + `
            ", stopwatchMilliseconds=$($timer.ElapsedMilliseconds)" `
             | Out-File "C:\DBA\ping\timing.txt" -Append


        Start-Sleep 1

        [System.Data.SqlClient.SqlConnection]::ClearAllPools()

    }
}
catch {
    throw
}

Sample result:

startTime=2021-07-07 07:13:53.826, timeOfQuery=2021-07-07 07:13:53.830, endTime=2021-07-07 07:13:53.830, timeDifferenceDuration 00:00:00.0040058, timeDifferenceMilliseconds 4, stopwatchDuration=00:00:00.0041146, stopwatchMilliseconds=4
startTime=2021-07-07 07:13:54.858, timeOfQuery=2021-07-07 07:13:54.863, endTime=2021-07-07 07:13:54.865, timeDifferenceDuration 00:00:00.0070053, timeDifferenceMilliseconds 7, stopwatchDuration=00:00:00.0070475, stopwatchMilliseconds=7
startTime=2021-07-07 07:13:55.872, timeOfQuery=2021-07-07 07:13:55.877, endTime=2021-07-07 07:13:55.879, timeDifferenceDuration 00:00:00.0070002, timeDifferenceMilliseconds 7, stopwatchDuration=00:00:00.0072906, stopwatchMilliseconds=7
startTime=2021-07-07 07:13:56.890, timeOfQuery=2021-07-07 07:13:56.897, endTime=2021-07-07 07:13:56.897, timeDifferenceDuration 00:00:00.0069949, timeDifferenceMilliseconds 6, stopwatchDuration=00:00:00.0068910, stopwatchMilliseconds=6
startTime=2021-07-07 07:13:57.907, timeOfQuery=2021-07-07 07:13:57.913, endTime=2021-07-07 07:13:57.914, timeDifferenceDuration 00:00:00.0070062, timeDifferenceMilliseconds 7, stopwatchDuration=00:00:00.0071905, stopwatchMilliseconds=7
startTime=2021-07-07 07:13:58.922, timeOfQuery=2021-07-07 07:13:58.927, endTime=2021-07-07 07:13:58.930, timeDifferenceDuration 00:00:00.0080190, timeDifferenceMilliseconds 8, stopwatchDuration=00:00:00.0075408, stopwatchMilliseconds=7
startTime=2021-07-07 07:13:59.935, timeOfQuery=2021-07-07 07:13:59.940, endTime=2021-07-07 07:13:59.943, timeDifferenceDuration 00:00:00.0080083, timeDifferenceMilliseconds 8, stopwatchDuration=00:00:00.0076028, stopwatchMilliseconds=7