Understanding Parallelism and the CXPACKET wait type

14/10/2014 Understanding Parallelism and the CXPACKET wait type Christian Bolton [email protected] @christianbolton www.coeo.com Agenda • What is ...
Author: Kevin Taylor
0 downloads 4 Views 1MB Size
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