PowerShell connection to SQL Server: MARS enabled, pooling disabled

Sometimes I have to troubleshoot connection issues... or even query performance issues... for various applications.  It can be very valuable to mimic the connection settings including MARS and connection pooling.

Here's how to do that - code snippets at the end of the post.

In the capture below I construct my desired connection string - in this case with MARS enabled and connection pooling disabled.  I bring back one row/one column with a SQL statement just to confirm it worked :-)

Here I specifically enabled MARS because its something I'm looking into right now.

Can we see the MARS connection from the database server side?  Yep.  Code snippet for this also at the end of the blog post.

OK.  Let's be nice and call Dispose.  Oops - typo. Try again.

OK.  Since Connection Pooling was disabled for this connection, it should disappear right away.  And it is really gone. (It can take 4 to 8 minutes for an idle pool connection to disappear by default.)

The PowerShell code...

$scon = New-Object System.Data.SqlClient.SqlConnection
$scon.ConnectionString = "Data Source=localhost;Initial Catalog=MASTER;Connection Timeout=15;Integrated Security=true;MultipleActiveResultSets=True;Pooling=False"
$cmd = New-Object System.Data.SqlClient.SqlCommand
$cmd.Connection = $scon
$cmd.CommandText = "SELECT db_name();"
$cmd.CommandTimeout = 0
$scon.Open()
$adp = New-Object System.Data.SqlClient.SqlDataAdapter $cmd
$data = New-Object System.Data.DataSet
$adp.Fill($data) | Out-Null
$data.Tables
$scon.Dispose()

The SQL query to see the PowerShell MARS session...

select conn.net_transport, conn.connection_id, conn.parent_connection_id, conn.session_id, 
       conn.num_reads, conn.last_read, conn.num_writes, conn.last_write,
       sess.login_time, sess.client_interface_name from sys.dm_exec_connections conn
join sys.dm_exec_sessions sess on sess.session_id = conn.session_id
where exists 
(select 1 from sys.dm_exec_connections ic where ic.session_id = conn.session_id and ic.net_transport = 'Session')
AND sess.login_time > '2017-09-14 10:20:00'
order by conn.session_id;