Lesson Learned #365: TSQL Execution Client Statistics using PowerShell

We have an interesting service request where our customer needs to measure the network time spent in several interactions while executing a query. Following I would like to share the following script using based on this URL: Provider Statistics for SQL Server - ADO.NET

$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: Provider Statistics for SQL Server - ADO.NET")

Continue reading...