Do parallel threads process equal rows? Usually no, and a simple AdventureWorks query with its actual execution plan proves it in a few seconds.
SQL SERVER – Parallelism for Heap Scan
Two DBAs asked me if their table needed a clustered index. Our conversation during a Health Check turned into a lesson on parallelism for Heap Scan.
Max Degree of Parallelism for Microsoft Dynamics CRM
Max degree of parallelism controls how many cores one query can use, and the vendor guidance for Microsoft Dynamics CRM has been 1. This post measures what parallel plans cost on a heavy and a small query. It shows a database scoped limit and the instance wide statements.
Serial Zones in Parallel Plans: Where One Thread Does the Work
A parallel plan can still leave one core busy. See a harmless serial zone, a costly one, and how to read per-thread rows from the plan.
SQL SERVER – Parallel Redo on AlwaysOn Secondary – DIRTY_PAGE_TABLE_LOCK
Redo lag and high DIRTY_PAGE_TABLE_LOCK waits hit a client’s AlwaysOn secondary. Here is how Parallel Redo in SQL Server 2016 causes them and what to do.
SPID, KPID and ECID in sys.sysprocesses: What They Mean
SPID, KPID and ECID are the three ID columns of sys.sysprocesses: the session, the Windows thread and the part of a parallel query. This post reads them for an idle session and for a query running in parallel, explains why KPID can be negative, and names the DMVs that give the same answers today.
How to Find Queries Running in Parallel in SQL Server
To find queries running in parallel, read max_dop and the CPU to elapsed ratio from the query stats view, then confirm with the plan XML. This post tests both on a two million row table. It also explains why the same query stays serial until its cost passes the threshold.