THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Page Free Space

See also my SQL Server technical articles on

Browse by Tags

All Tags » Parallelism

  • SQL Intersection Conference, Las Vegas MGM Grand 10-13 November 2014

    I am very pleased to announce that I will be speaking at the SQL Intersection conference in Las Vegas again this year. This time around, I am giving a full-day workshop, "Mastering SQL Server Execution Plan Analysis" as well as a two-part session, Read More...
  • Parallel Execution Plans Suck

    Summary: A deep dive into SQL Server parallelism, and a potential performance problem with parallel plans that use TOP. There was an interesting question asked by Mark Storey-Smith on back in October 2011. He was looking at the execution Read More...
  • Forcing a Parallel Query Execution Plan

    This post is for SQL Server developers who have experienced the special kind of frustration, which only comes from spending hours trying to convince the query optimizer to generate a parallel execution plan.  This situation often occurs when making Read More...
  • How Parallelism Works in SQL Server

    You might have noticed that January was a quiet blogging month for me. Part of the reason was that I was working on a series of articles for Simple Talk, examining how parallel query execution really works. The first part is published today at: Read More...
  • Myth: SQL Server Caches a Serial Plan with every Parallel Plan

    Many people believe that whenever SQL Server creates an execution plan that uses parallelism, an alternative serial plan is also cached. The idea seems to be that the execution engine then decides between the parallel and serial alternatives at runtime. I’ve seen this on forums, in blogs, and even in books. In fairness, a lot of the official documentation is not as clear as it might be on the subject. In this post I will show that only a single (parallel) plan is cached. I will also show that SQL Server can execute a parallel plan on a single thread… Before diving into the demonstration, I want to quickly run through some background information about the SQL Server plan cache. Compiled Plans Queries are expensive to compile and optimize, so SQL Server uses caching to improve efficiency through plan reuse. The server optimizes an entire batch all at once, and the result is known as a compiled plan (or sometimes ‘query plan’). The dynamic management view sys.dm_exec_cached_plans contains one row for each compiled plan, with a plan_handle that uniquely identifies the compiled plan among those currently in cache (plan handles can be reused over time). This plan handle can be passed to the dynamic management function sys.dm_exec_query_plan to show the compiled plan in XML format. When displayed in Management Studio, we can click on this XML representation to view the familiar graphical plan. A compiled plan is a compile-time object – no user or runtime context is stored. You might find it helpful to think of the compiled plan as a template – or perhaps as being similar to the estimated execution plans seen in Management Studio. Execution Contexts An execution context (or ‘executable plan’) - internally known as an MXC – is generated when a compiled plan is prepared for execution. Execution contexts contain specific runtime information, for a single execution, for a single user. Read More...
Privacy Statement