Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Find current running SQL Server queries with a PowerShell script


By:   |   Updated: 2016-12-27   |   Comments   |   Related: More > PowerShell

Problem

The requirement is to implement a quick method to find the currently executing queries on any SQL Server instance. There are times when you need to quickly check an instance, but it takes time to connect using SQL Server Management Studio (SSMS) so this tip shows a quick and simple way to check any SQL Server instance using a PowerShell script.

Solution

I wrote a very simple PowerShell script that prompts for the SQL Server instance name and then executes a SQL query by using the Invoke-sqlcmd cmdlet. This queries the sys.dm_exec_requests and the sys.dm_exec_sql_text dynamic management views in order to get the queries time statistics along with the T-SQL statements. The results from the query are then piped to an Out-GridView cmdlet that opens a sortable grid view with the results of the query.

These are some of the benefits for using this approach:

  1. Hide, Show, and Reorder Columns: Hide, show, or reorder a column, right-click a column header and then click select option.
  2. Sorting: Sort the data, click on a column header. Click again to toggle from ascending to descending order.
  3. Quick Filter: Use the Filter box at the top of the window to search for text in the table. You can search for text in a particular column, search for literals, and search for multiple words.
  4. Criteria Filter: Use the Add criteria drop-down menu to create rules to filter the data. This is very useful for very large data sets, such as event logs.
  5. Copy and paste: Copy rows of data from the Out-GridView, press CTRL+C (copy). You can then paste the data into a text editor or spreadsheet.

Here is the T-SQL query used for finding the current running SQL queries.  We will use this same query for the PowerShell script.

SELECT s.TEXT
 ,r.total_elapsed_time
 ,r.session_id
 ,r.STATUS
 ,r.command
FROM sys.dm_exec_requests r `
CROSS APPLY sys.dm_exec_sql_text(sql_handle) s

Here is the PowerShell script that uses this query.  It prompts you for the SQL Server instance name and shows the results in an Out-GridView.

$title = "Current Running SQL Queries"
$SqlServer = Read-Host "Enter SQL Server Instance name:"
$SqlQuery = "SELECT s.TEXT,r.total_elapsed_time,r.session_id,r.status,r.command FROM sys.dm_exec_requests r `
             CROSS APPLY sys.dm_exec_sql_text(sql_handle) s" 
Invoke-Sqlcmd -ServerInstance $SqlServer -Database "master" -Query $SqlQuery | Out-GridView -Title $title

Example Usage

Open the PowerShell ISE environment and copy the PowerShell script.

powershell ise

Run the script in the ISE (press F5) and enter the SQL instance name when prompted.

powershell prompt

The following grid view is then displayed showing the current running queries. With this view you can sort by any column (text, elapsed time, session id, status or command), filer the data, search for text, etc.

powershell t-sql results

Notes

1. The T-SQL query was tested using SQL Server 2012 Developer edition on my personal server.

2. The PowerShell script was executed on Windows 7 Enterprise edition with PowerShell version (see $PSVersionTable values): $PSVersionTable

Name                        Value
----                        ----- 
PSVersion                   3.0 
WSManStackVersion           3.0 
SerializationVersion        1.1.0.1 
CLRVersion                  4.0.30319.42000 
BuildVersion                6.2.9200.16481 
PSCompatibleVersions        {1.0, 2.0, 3.0} 
PSRemotingProtocolVersion   2.2 3.

3. The Out-GridView cmdlet requires a user interface, so it does not work on Server Core installations of Windows Server.

Next Steps
  • Use this as example to build other interactive T-SQL queries using PowerShell.
  • Check out these other PowerShell tips.


Last Updated: 2016-12-27


get scripts

next tip button



About the author
MSSQLTips author Eli Leiba Eli Leiba is a senior application DBA, a teacher and a senior database consultant with 19 years of RDBMS experience.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools