SQL Relay London 30 October 2014

My slides from today are at http://www.smooth1.co.uk/sqlserver2014/2014NewFeatures.zip

Notes from Christian Boltons talk Understanding Parallelism

Troubleshoot at task level rather than session level.

session->task->worker thread->scheduler->Logical CPU

Query cost value is the same across all sql editions

The decision to use parallelism is at runtime not optimization time e.g. if not enough worker threads

To get the average query cost look at the the xml plan from the plan cache

Gather streams maintains sort order

Other parallelism operators are Repartition (Many to Many) and Distribute (One to Many)

Each parallel task is really a producer and a consumer as seen in dm_os_waiting_tasks

At startup -P option can adjust the number of schedulers to allow you to see how the optimizer will behave even on hardware which does not have that number of cpus, check from dm_os_schedulers.

Bitmap operators only appear in a parallel plan and can be used as a predicate to filter against another query plan leg when joining. This means estimates rows will NOT match actual rows as filtering is done at runtime hence is not in the estimate.

Does the query processor wait for the full bitmap to be built?
With parallism 8 do you get 8 bitmaps?

Do not set degree of parallelism 1 at the server level and not think about the impact – affects dbcc checkdb,index rebuilds.

Check dm_os_sys_info. Each worker thread takes up 2MB stack outside the sql server memory.

CXPACKET wait just means 1 of the parallel tasks is finished and waiting for the others to complete – not necessarily bad, just means lots of long running parallel queries.

Can adjust query cost threshold for parallelism,default is 5, increase to reduce number of parallel plan executions if this is flooding the server.

Claire Mora – T-SQL Performance Issues : What you should know to find and resolve them

Consider sql cruise, may be next year!

Use a Row Generator to create sample test data

Avoid parallel plans with a sort.

For complex queries consider Divide and Conquer http://www.sqlservercentral.com/articles/T-SQL/107611/

Data Bubbles – thick lines in ssms query plan

Quirky Update – http://www.sqlservercentral.com/articles/T-SQL/68467/

Test against all Version and Editions planned for deployment.

Advertisement

---