SQL SERVER – How to Find If Queries are Run in Parallel?

Technology innovations over years have made personal computing and the infrastructure inside our datacenters 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.

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.

Having said that, I have seen people struggle to identify parallel queries in their environments. So here is the first shot at this requirement.

Solarwinds

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 behavior. 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. Please share me 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)

Solarwinds
Previous Post
SQL SERVER – FIX – Property IsLocked is Not Available for Login
Next Post
Interview Question of the Week #029 – Difference Between CHARINDEX vs PATINDEX

Related Posts

No results found

2 Comments. Leave new

  • nakulvachhrajani
    July 25, 2015 3:30 pm

    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.

    Reply
  • nakulvachhrajani
    July 25, 2015 3:46 pm

    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.

    Reply

Leave a Reply

Menu