As with most of these types of things, I had a need. I want to show how using sys.dm_exec_[requests/sessions/connections] is better than sp_who. Particularly when you have a large number of connections. Well in order to do that I need a large number of connections right? Now I’m sure someone out there has a script to generate somewhat random connections but writing one myself would be good practice and I’d like to get better at Powershell anyway. In the end I need some help and as aways it was plentiful and easy to find. So thanks to Derik Hammer (b/t), Drew Furgiuele(b/t), and of course it wouldn’t be a PoSH script if I didn’t get help from Mike Fal (b/t). (To be honest Mike actually wrote most of the final script)

#This script creates a number of connections ($MaxConnections) 
# to a SQL Server instance ($Server) that connect to a random database and exist/run for 
# a certain amount of time ($WaitType/$WaitTime)
#Driver variables
$MaxConnections = 2;           #Number of parallel connections
$Server= "(local)\sql2014cs" ; #Server to connect to
$WaitType="DELAY";             #Type of wait.  DELAY or TIME
$WaitLength="00:00:10";        #Length of wait. Format is HH:MM:SS
                               #  If DELAY then wait for the amount of time.  
                               #  If TIME then wait until the time specified.
                               # Note: Connections are only exist until the wait is over. 
                               #       They are active the whole time.
#Set Initial collections and objects    
$SqlInstance = New-Object Microsoft.SqlServer.Management.Smo.Server $Server ;
$DbConnections = @();
$dbs = $SqlInstance.Databases | Where-Object {$_.IsSystemObject -eq 0} ;
#Build DB connection array
for($i=0;$i -le $MaxConnections-1;$i++){
  $randdb = Get-Random -Minimum 1 -Maximum $dbs.Count
  $DbConnections += $dbs[$randdb].Name
}
#Loop through DB Connection array, create script block for establishing SMO connection/query
#Start-Job for each script block
foreach ($DBName in $DbConnections ) {
# All of that extra information after "Smo" tells it to load just v12 (for when you have multiple
#   versions of SQL installed.)  Note: V12 is 2014.
 $cmdstr =@"
`Add-Type -AssemblyName "Microsoft.SqlServer.Smo,Version=$(12).0.0.0,Culture=neutral,PublicKeyToken=89845dcd8080cc91"
`[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
`$SqlConn = New-Object Microsoft.SqlServer.Management.Smo.Server ("$Server")
`$SqlConn.Databases['$DBName'].ExecuteNonQuery("WAITFOR $WaitType '$WaitLength'")
"@
#Uncomment the next like to print the command string for debugging
# $cmdstr
#Execute script block in jobs to run the command asyncronously
$cmd = [ScriptBlock]::Create($cmdstr)
Start-Job -ScriptBlock $cmd
}

A few things I learned.

Dealing with arrays

Declaring the array

$DbConnections = @();

Adding a row to the array

$DbConnections += $dbs[$randdb].Name

Looping through the array

foreach ($DBName in $DbConnections ) {

Obviously this is not everything you can do with an array or even every way to work with an array but it’s a start.

A multi line command

There are lots of ways to handle multi-line commands (some if not all detailed in the link) but this is how it was done in this script.

The line starts here
`and continues here.

For Loops
For Counter

for($i=0;$i -le $MaxConnections-1;$i++){
}

Where $i is the counter, -le means continue while $i is less than $MaxCounters-1, and last but not least increment $i.

For Each

foreach ($DBName in $DbConnections ) {
}

Where $DBName is the variable each element will go into and $DbConnections is a collection (array, object collection etc) of elements.

Running code asynchronously

Create a ScriptBlock out of string.

[$cmd = [ScriptBlock]::Create($cmdstr)

Run the script block as a job.

Start-Job -ScriptBlock $cmd

Filed under: Microsoft SQL Server, Powershell, SQLServerPedia Syndication Tagged: Powershell