Lesson Learned #365: TSQL Execution Client Statistics using PowerShell

[Jose_Manuel_Jurado]

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: 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")