Parallelism

Parallelism is when SQL Server utilizes multiple processors for executing a query. Normally, SQL Server will execute different parts of a query serially but if the query is costly enough, it will split it into different streams and then execute them in parallel.

Plant Applications has a dual role, which is affected differently by parallelism. It is both an Online-Transaction Processing (OLTP) applications and a reporting application at the same time. OLTP applications involve lots of small transactions where data is created, updated and deleted while reporting applications typically involved lots of large complex queries to extract and analyze data.

For reporting applications, parallelism is typically a good thing as it improves the performance of large complex data queries. However, parallelism can be bad for Plant Applications as it consumes multiple processors for a single query, thereby impacting the performance of the rest of the server. This is generally a bad thing as the responsiveness of Plant Applications to operators and other systems is more important than the execution time of a report. Parallelism can also cause performance blocking of a server where a complex query consumes all the resources on a server and the normal transactional operations of the server cannot complete.

Parallelism can be seen in the master..sysprocesses table. For a given spid, you will see multiple records, each with an incremental ecid and different kpid (WinNT process). The record with an ecid of 0 is the ‘master’ thread that will contain key information about the query (for example, sql_handle, stmt_start and stmt_end), which will help with diagnosis.

The following query will show any current processes that are utilizing parallelism.


SELECT sp.spid, sp.ecid, sp.kpid, sp.*
FROM master..sysprocesses sp WITH (NOLOCK) 
JOIN (	SELECT spid
        FROM master..sysprocesses WITH (NOLOCK) 
        GROUP BY spid
HAVING count(kpid) > 1) p ON sp.spid = p.SPID

Parallelism can be addressed in 3 different ways:

Optimize the query to run faster so it does not meet the minimum threshold for parallelism. This is the best way to address parallelism.
Use the MAXDOP option to restrict SQL Server to only use 1 processor. This is generally a safe option to use but will likely result in the query to run longer than without it which is often an acceptable tradeoff (for example, if it is a part of a report). For example,
```
SELECT *
FROM Tests
WHERE Result_On > @RptStartTime 
OPTION (MAXDOP 1)
```
Modify the SQL Server settings to either restrict the number of processors available to parallelism, raise the minimum query threshold or disable parallelism altogether. This is the same idea as the MAXDOP query option but with a server-wide scale. These settings should be thoroughly tested before implementing in a production environment.