Technology innovations over the years have made personal computing and the infrastructure inside our data centres even more powerful. Gone are the days when our laptops used to come with single processors and single cores. I wonder even if they ever sell such configurations in the market. Let us learn today how to find if queries are running in Parallel?
Talking about multi-cores on our desktops and servers, these days software like SQL Server just use them to the max. When working with SQL Server, there are a number of settings that influence using parallelism. Check blog SQL SERVER – MAXDOP Settings to Limit Query to Run on Specific CPU, SQL SERVER – CXPACKET – Parallelism – Usual Solution – Wait Type and many other posts on this topic.
Queries are Running in Parallel
Having said that, I have seen people struggle to identify parallel queries in their environments. So here is the first shot at this requirement.
SELECT p.dbid, p.objectid, p.query_plan, q.encrypted, q.TEXT, cp.usecounts, cp.size_in_bytes, cp.plan_handle FROM sys.dm_exec_cached_plans cp CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS p CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS q WHERE cp.cacheobjtype = 'Compiled Plan' AND p.query_plan.value('declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; max(//p:RelOp/@Parallel)', 'float') > 0
Queries that run in parallel can be found with the above query. Remember, if a query runs in parallel it is a query that SQL Server thinks is expensive enough to run in parallel. MAX_DOP and the cost_threshold_for_parallelism drive the behaviour. MAX_DOP should be configured to match the number of physical processors in the server if required.
The next step is to understand what to do when you find them? When you find them look for ways to make them run more efficiently if they are run often and their performance during business hours is critical. Check indexing in DTA for recommendations, simplify the query, remove ORDER BYs, GROUP BYs, if they aren’t necessary – these are some steps to help you guided.
Another way to find parallelism is to get queries where the amount of time spent by the workers are more than the query execution time. You can also use the below method to get the same too:
SELECT qs.sql_handle, qs.statement_start_offset, qs.statement_end_offset, q.dbid, q.objectid, q.number, q.encrypted, q.TEXT FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) AS q WHERE qs.total_worker_time > qs.total_elapsed_time
I hope these two scripts will be of use and you have something similar in your environments. I often use them while helping my client at Comprehensive Database Performance Health Check. Please share my scenario’s where you saw parallelism perform slower and how did you find them? Do let me know via comments.
Reference: Pinal Dave (https://blog.sqlauthority.com)
One of the scenarios where I have found parallelism to be slower is when we have MIS reports running off an OLTP system. The aggregations in the queries for these reports is costly enough for the SQL Server database engine to opt for a parallel plan, but the normalized nature of the OLTP schema causes it to backfire and impact performance. In such cases, we explicitly ensure that these queries run under a MAXDOP setting of 1, i.e. no parallelism.
In my comment above, I forgot to add the part about how I found that parallelism was creating a problem. Ours is a legacy system (where the schema has evolved since the days of SQL 7.0 and continues to undergo enhancements and growth even today). When SQL Server 2005 was launched and we undertook a certification effort, that’s when we noticed that our reports were literally bringing the server down to a crawl. The change was that SQL Server 2005 came with support for parallelism – the moment we set it to 1 (at the instance level), the performance improved confirming our theory. Later on, we modified the queries to use the MAXDOP query hint wherever required.