SQL Queries, execution plans and "Parallelism"

sql-server sql-execution-plan
Christian Wattengård picture Christian Wattengård · Oct 5, 2010 · Viewed 8.1k times · Source

So I'm (still) going through some slow legacy sql views used to do calculate some averages and standarddeviations on a (sometimes) large set of data. What I end up with are views joining views joining views etc.

So I though I would review the execution plan for my query. And it immediately suggested a missing index, which I then implemented. But it's still unbearably slow (so slow it times out the VB6 app querying it for data ;) )

So upon studying the execution plan further, I see that what costs the most (about 8% each in my case) are "Paralellism" cases. Mostly "Distribute Streams" and "Repartition Streams". What are these?

Answer

Joe Stefanelli picture Joe Stefanelli · Oct 5, 2010

Distribute Streams and Repartion Streams are operations that occur when the SQL optimizer chooses to use Parallel Query Processing. If you suspect that this is causing an issue with your query, you can force SQL Server to only use one CPU with the MAXDOP query hint, as illustrated below.

select *
    from sys.tables
    option (maxdop 1)

Related questions

How do I obtain a Query Execution Plan in SQL Server?

In Microsoft SQL Server how can I get a query execution plan for a query / stored procedure?

Read More
Measuring Query Performance : "Execution Plan Query Cost" vs "Time Taken"

I'm trying to determine the relative performance of two different queries and have two ways of measuring this available to me: 1. Run both and time each query 2. Run both and get "Query Cost" from the actual execution plan Here is …

Read More
SQL Server Plans : difference between Index Scan / Index Seek

In a SQL Server Execution plan what is the difference between an Index Scan and an Index Seek I'm on SQL Server 2005.

Read More