During the recent Comprehensive Database Performance Health Check, my client asked me if I have a query which can list all the parallel queries which are running in the server along with the count of times the query has executed.
Yes, it is indeed possible to go back to SQL Server cache and find all the queries which are running in parallel. Additionally, the cache will also show how many times the query has run since the data is in the cache. Watch this video about how you can do it.
The script used in the video you can download from this link: SQL SERVER – How to Find If Queries are Running in Parallel?
Additionally, please remember that the data which you will be retrieving will be from the cache and the cash is refreshed every single time the SQL Server services restarts. It is quite possible that the data which are stored in the cache gets removed by internal SQL Server processes as well as by user.
So essentially what I am saying is that this script is a great starting point to see how many queries are running in parallel in your system. If you really want a 100% accurate picture of this scenario, you will have to use extended events for it.
Now here are a few additional blog posts on the same topics which you may find useful.
- SQL SERVER – T-SQL Script to Identify TOP 10 Parallel Running Queries
- How to Force a Parallel Execution Plan for a Query? – Interview Question of the Week #170
- SQL SERVER – Parallelism Query in Database
- SQL SERVER – Using Query Hint ENABLE_PARALLEL_PLAN_PREFERENCE
- SQL SERVER – Find Queries using Parallelism from Cached Plan
You can follow my youtube channel or connect with me on twitter.
Reference: Pinal Dave (https://blog.sqlauthority.com)
