Thursday, September 14, 2017

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;



4 comments:

  1. creativity of writer is purely impressive. It has touched to the level of expertise with his writing. Everything is up to the mark. Written perfectly and I can use such information for my coming assignment.เครื่อง ups ราคา

    ReplyDelete
  2. Sometime few educational blogs become very helpful while getting relevant and new information related to your targeted area. As I found this blog and appreciate the information delivered to my database.Acer AT350 F3

    ReplyDelete
  3. So net_transport session is the key to find out MARS. Please is it right way confirm

    ReplyDelete
  4. Your deep diving is really very very appreciated

    ReplyDelete