14/10/2014
Understanding Parallelism and the CXPACKET wait type Christian Bolton
[email protected] @christianbolton
www.coeo.com
Agenda • What is parallelism? • SQL Server Execution Model • How does SQL Server decide to use parallelism? • Parallel Operators • Options to control parallelism • What does a sub-optimal plan look like? • What does a CXPACKET wait tell you?
Understanding Parallelism
www.coeo.com
1
14/10/2014
What is Parallelism?
What is Parallelism? Many hands make light work • In this case hands are processors • Parallelism is the distribution of a piece of work across multiple processors • Multi-processor servers available for a LONG time • Support for parallelism since SQL Server 7.0 • Serious contender from SQL Server 2005
Understanding Parallelism
www.coeo.com
2
14/10/2014
What is Parallelism? Looking at a parallel plan
Understanding Parallelism
www.coeo.com
SQL Server Execution Model
3
14/10/2014
SQL Server Execution Model Session
Task Thread Scheduler Logical CPU Understanding Parallelism
www.coeo.com
SQL Server Execution Model Cost-based Optimizer • Many execution plans are created. The “cheapest” one is chosen to execute • Cost is an arbitrary value consistent throughout SQL Server • The cost of each operator in the plan is calculated as: • Row size * number of rows * base cost of the operator = estimated cost
• Cost of the plan is the total cost of all operators
Understanding Parallelism
www.coeo.com
4
14/10/2014
SQL Server Execution Model Statistics • Estimated cost is based on the estimated number of rows that will be returned • Statistics objects are maintained to provide fast row estimates • Contains a histogram of data distribution
Understanding Parallelism
www.coeo.com
How does SQL Server decide to use Parallelism?
5
14/10/2014
How does SQL Server decide to use Parallelism? Cost • If the query is expensive then parallel plans will be considered by the optimizer • Parallelism has an overhead • Serial plan will be used for expensive queries if the parallel plan is no cheaper
Understanding Parallelism
www.coeo.com
How does SQL Server decide to use Parallelism? Resources • The number of schedulers and DoP used to determine cost estimates during optimization • The decision to use parallelism isn’t determined during optimization but at runtime • Need to have enough worker threads available
• A cached parallel plan can still run serially if there aren’t enough workers available • Plan shape stays the same and the parallel operators will just be skipped Understanding Parallelism
www.coeo.com
6
14/10/2014
DEMO
Simple Parallelism Example
Parallel Operators
7
14/10/2014
Parallel Operators Gather • Combines multiple threads into one. Can be order preserving. PRODUCER CONSUMER
Repartition • Multiple threads in, multiple out. Used to re-distribute rows between threads.
Distribute • Single thread in, multiple out. Used to distribute rows between multiple threads. Understanding Parallelism
www.coeo.com
DEMO Looking at more parallel operators
8
14/10/2014
Options to Control Parallelism
Options to Control Parallelism Server level • Affinity Mask • Controls how many processors SQL Server can use • And therefore the number of schedulers
• Max Degree of Parallelism • Maximum number of threads to use in a parallel plan per operator
Understanding Parallelism
www.coeo.com
9
14/10/2014
Options to Control Parallelism Server level (cont.) • Cost Threshold for Parallelism • Minimum cost at which to consider parallelism • Increase cost to reduce the number of small queries that are run in parallel
• Max Worker Threads • By default is based on # of procs and architecture • SELECT max_workers_count FROM sys.dm_os_sys_info • If you’re running out of threads, DoP will automatically be reduced Understanding Parallelism
www.coeo.com
Options to Control Parallelism Query level • MAXDOP hint • Overrides the server level settings up or down
Understanding Parallelism
www.coeo.com
10
14/10/2014
What does a sub-optimal plan look like?
What does a sub-optimal plan look like? Serial Operators • Add additional overhead to switch between serial and parallel operators. Row Estimates • Poor estimates can lead to an un-even distribution of rows across threads.
Understanding Parallelism
www.coeo.com
11
14/10/2014
What does a sub-optimal plan look like? Things that will force a serial region • System table scans • “Backward” scan • Global Scalar Aggregate • Sequence Functions • Recursive queries • TOP operator • Table Valued Functions
Understanding Parallelism
www.coeo.com
What does a sub-optimal plan look like? Things that will force a serial plan • All T-SQL UDFs • CLR UDFs with data access • Global Scalar Aggregate • Sequence Functions • Recursive queries • TOP operator
• Table Valued Functions
Understanding Parallelism
www.coeo.com
12
14/10/2014
DEMO Sub-optimal plans
What does CXPACKET wait tell you?
13
14/10/2014
What does CXPACKET wait tell you? That you have long running parallel queries • CXPACKET is the synchronization between worker threads • Look for tasks in the same session that are NOT waiting for CXPACKET • Optimize the query
Understanding Parallelism
www.coeo.com
Summary
Understanding Parallelism
www.coeo.com
14
14/10/2014
Questions?
www.coeo.com
15