elstar.com.au

Understanding and Using PowerShell Support in SQL Server 2008

Code: Select all

PS> get-process | sort-object workingset -descending | select-object
-first 10
Handles  NPM(K)    PM(K)      WS(K) VM(M)   CPU(s)     Id ProcessName
-------  ------    -----      ----- -----   ------     -- -----------
    637      82   163668     157312  1228   232.92   2132 sqlservr
    535      80   120208     117256  1225   261.53   1344 sqlservr
    562      18    99972      77364   357   457.14   3580 Ssms
    598      11    52048      50352   179    57.86   4012 powershell
    308      73    61612      45740  1155   156.54    728 sqlservr
    602      17    57452      37956   255   298.60   1400 ReportingServicesService
    494      10    26636      33144   155     5.93   3308 SQLPS
    713      46    36704      27984   210   241.31   1264 msmdsrv
   1011      42    12872      19556    80   144.29    808 svchost
    158       4    12248      13272   104     2.22   1204 MsDtsSrvr

This takes the output of the Get-Process cmdlet we examined earlier and pipes the output to the sort-object cmdlet, which allows us to sort the results in a particular order.

SQLPS.exe – The SQL Server Mini-Shell
To make things easier for administrators using PowerShell for SQL Server, Microsoft created an executable - a mini-shell - which automatically loads the SQL Server PowerShell snap-ins. The program is called sqlps.exe and is included when you install SQL Server 2008. Besides automatically loading the snap-ins the sqlps.exe environment enables script execution automatically by setting the execution policy to RemoteSigned.