How to Solve the Issues Associated with Intraquery Parallelism in SQL Server

In this article, we will discuss how to decomposition of queries can help in achieving lineal performance, how to check for sessions containing parallel requests and the role of database tuning advisor.

Cost Threshold For ParallelismSQL Server optimizer try to choose the best plans whenever we create an execution plan for our query, which gives quickest response time for the query. Cost threshold for parallelism is an option given that holds a specified value and if the proposed query cost exceeds this value and parallelism features is not disabled then the SQL Server optimizer generates a plan which can be run in parallel. Multiple threads are used in parallel query processing and each of these threads are distributed among various CPUs. They simultaneously utilize CPU time for every processor.

Before execution of query the SQL Server finds out number of schedulers that are underutilized and choose degree of parallelism for query which can utilize leftover schedulers. Parallel queries use a same but vaguely higher amount of processor time as compared to serial plans, but it does that in a shorter period of time. 100% CPU is utilized by parallel plans if there is no bottlenecking. Server prefers parallel plans whenever it gets a query in idle time and for batch processing, parallel queries are quite helpful. Parallel query plans are not bad as they give fastest response time but the response time must be compared with rest of queries which are on the system to see if it is worth it to use them or not.

Decomposition of Queries

SQL ServerFor fast query processing, just like real world problems, we decompose them in small parts and process them in parallel and hence it is known as intraquery parallelism. Intraquery parallelism has both its pros and cons. To detect the issues which might occur due to usage of intraquery parallelism, SQL Server offer DMVs and performance monitor.

Threshold Cost

Before considering query as part of parallel plan, its cost should exceed cost threshold for the configuration of parallelism. Batches are inversely proportional to parallel query plan which means the more batches being processed per second, the less likely parallel plan are running with them. Server with many parallel plans have lesser amount of batch requests.

Sessions with PARALLEL requests

To determine whether a session is running parallel requests, following query can be used on an active SQL Server.

Select 
          a.request_id,
          a.sql_handle,
          a.plan_handle,
          a.session_id,
          a.statement_end_offset,
          a.statement_start_offset,
          max(isnull(exec_context_id, 0)) as Total_Workers
From
         sys.dm_exec_requests a
         join sys.dm_os_tasks b on b.session_id = a.session_id
         join sys.dm_exec_sessions c on a.session_id = c.session_id 
Where
         c.is_user_process = 1
Group by
           a.statement_start_offset, a.sql_handle
           a.statement_end_offset, a.request_id,
           a.plan_handle, a.session_id   
having max(isnull(exec_context_id, 0)) >0

Using this query we can get both plan and text by using sys.dm_exec_cached_plan and sys.dm_exec_sql_text respectively. We can also search plans that support parallel processing. Cached plans are needed to be searched to check whether relational operators have their parallel attribute as nonzero value or not. If they are nonzero then it means we can run them in parallel if our server is not too busy.

PARALLEL Query Plans

Following query can be used to find parallel query plans.

Select
          cp.plan_handle,
          a.*,
          b.*
From 
         sys.dm_exec_cached_plans cp
         cross apply sys.dm_exec_query_plan(cp.plan_handle) a
         cross apply sys.dm_exec_sql_text(cp_plan_handle) as b
Where
         a.query_plan.value('declare namespace 
         p = https://schemas.microsoft.com/sqlserver/2004/07/showplan;
         max(//p:RelOp/@Parallel)', 'float') > 0
         AND
         cp.cacheobjtype = 'Compiled Plan'

Most of the time query duration is longer than CPU time amount because some of time is spent on waiting of resources like physical I/O or a lock. Only scenario in which CPU time is less than elapsed duration is when query runs along with parallel plans so that multiple threads use CPU at the same time.

Tuning Advisor

Database Engine Tuning Advisor is a useful sql component to check whether changes are made to indexed views or not, any indexing changes or partition changes can reduce query cost or not. Any query which can run in parallel with the plan is identified by optimizer as the one which expensive and capable to exceed cost threshold of parallelism. We should check for any significant difference estimated and actual cardinality because they are primary factor for estimation of query cost.

If different are found between actual and estimated cardinality, then we need to make sure that warnings column of the show plan does not have missing stat entry. We can also use update stats on table whose cardinality estimate are turned off. We should also check if different T-SQL statements/expressions can help us in writing efficient queries or not.

Having an efficient intraquery parallelism plan not only helps in achieving lineal performance but also helps in avoiding SQL Server corruption.

Database Corruption

Intraquery parallelism issue can lead to database corruption and in this scenario we might lose our recent useful data which is not backed up. A SQL Server recovery tool called DataNumen SQL Recovery provides solution to this problem and brings back all your recent data which wasn’t part of your latest backup.

Author Introduction:

Upton Mark is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including access recovery and word recovery software products. For more information visit www.datanumen.com

Comments are closed.