This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Community Hub.
We have an interested service request that our customer needs to measure the network time spent in several interactions executing a query. Following I would like to share the following script using based on this URL: https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/provider-statistics-for-sql-server
$SrvName = "ServerName.database.windows.net"
$Database = "DBName"
$Username = "UserName"
$Password = "Password"
$NumberExecutions = 10000
cls
try
{
$sw = [diagnostics.stopwatch]::StartNew()
$connectionString = "Server=tcp:$SrvName,1433;Initial Catalog=$Database;Persist Security Info=False;User ID=$Username;Password=$Password;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;Pooling=True;Packet Size=8092"
$connection = New-Object -TypeName System.Data.SqlClient.SqlConnection($connectionString)
$connection.StatisticsEnabled = 1
$connection.Open()
$query = "select Replicate('X',8000)"
$command = New-Object -TypeName System.Data.SqlClient.SqlCommand
$command.CommandTimeout = 60
$command.Connection=$connection
$command.CommandText = $query
write-Output ("Query: " +$query)
for ($i=1; $i -le $NumberExecutions; $i++)
{
try
{
If( $Red.IsClosed -eq $false) { $Red.Close() }
$start = get-date
$Red = $command.ExecuteReader()
$Null = $red.Read()
$end = get-date
$data = $connection.RetrieveStatistics()
$lSelectRows=$data.SelectRows
If( $lSelectRows -eq 0 ) { $lSelectRows = 1}
write-Output ("-----------" )
write-Output ("Iteration: " +$i)
write-Output ("Spent(ms): " +(New-TimeSpan -Start $start -End $end).TotalMilliseconds)
write-Output ("Exec.(ms): " +$data.ExecutionTime)
write-Output ("Rnd.Trips: " +$data.ServerRoundtrips)
write-Output ("Buf.Recd : " +$data.BuffersReceived)
write-Output ("Sel.Rows : " +$lSelectRows)
write-Output ("Byt.Recd : " +$data.BytesReceived)
write-Output ("KB Recd : " +($data.BytesReceived/1024))
$connection.ResetStatistics()
}
catch
{
Write-Output -ForegroundColor DarkYellow "Error at execution"
Write-Output -ForegroundColor Magenta $Error[0].Exception
}
}
}
catch
{
Write-Output -ForegroundColor DarkYellow "Error at connection"
Write-Output -ForegroundColor Magenta $Error[0].Exception
}
finally
{
$connection.Close()
}
write-Output ("Time spent (ms) Procces : " +$sw.elapsed)
write-Output ("Review: https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/provider-statistics-for-sql-server")
