By: Eli Leiba | Comments | Related: > 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:
- Hide, Show, and Reorder Columns: Hide, show, or reorder a column, right-click a column header and then click select option.
- Sorting: Sort the data, click on a column header. Click again to toggle from ascending to descending order.
- 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.
- 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.
- 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.
Run the script in the ISE (press F5) and enter the SQL instance name when prompted.
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.
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.
About the author
Eli Leiba is a senior application DBA, a teacher and a senior database consultant with 19 years of RDBMS experience.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
