Tags
26 performance tuning questions and solutions, Avoid SQL Server functions in the WHERE clause for Performance, Avoid Using Function in WHERE Clause – Scan to Seek, Avoid Using Functions in WHERE Clause tutorial and example, Functions in the WHERE Clause, How to avoid convert function in the where clause, How to tune SQL queries, Increase SQL Server performance avoid using functions in Where Clause, Interview questions for SQL Server Performance Tuning, Looking for SQL Optimization Interview Questions, performance sql server, Performance tips for faster SQL queries, Performance Tuning, Performance Tuning for SQL Server, Query Optimization, Query Performance Tuning, sql - How do I avoid functions like UPPER in where clause, SQL Complex Queries, SQL Optimization Interview Questions, sql performance, sql performance and tuning, sql performance explained pdf, sql performance tips, SQL Performance Tuning, sql performance tuning and optimization, SQL Performance Tuning and Optimization - Where CTEs are stored ?, sql performance tuning interview questions, sql performance tuning tips, SQL Query Optimizer, SQL Query Tuning or Query Optimization, SQL Server 2008 Query Performance Tuning Distilled, SQL SERVER Interview questions, SQL Server Optimization - Parallel Execution Plans, SQL server optimization interview questions and answers, sql server performance query, sql server performance slow, SQL Server Performance Tuning, SQL Server Performance Tuning Tips, SQL Server Query Optimization: Parallel Plans, SQL SERVER Tips, SQL Tuning Overview, SQL Where Clause Performance, T-SQL Best Practices - Don't Use Scalar Value Functions in Where Clause, Tips for SQL Database Tuning and Performance, Top 10 performance tuning tips for relational databases, WHERE Clause Functions: A Word of Caution, Where CTEs are stored ?</, Which one is better for performance Table Variables or Temp Tables – Only in terms of performance optimization?
SQL Server Optimization – Parallel Execution Plans
What is Parallelism in execution plans?
Basics-
A parallel plan is any plan where at least a single iterator is split into multiple threads and these threads run in parallel. Please note that Not all operators are parallel aware. Meaning not everything can be execute in parallel.
This means SQL Server internally manages of the things in parallel while executing a query. Please note that there is no plan which is 100% parallel. We can have a plan which is 100% serial and we can have plan where some of the things happened in parallel. We will at least have one iterator which works in serial manner. That is the first operator. The first operator can be Select, Insert and Delete.
Now how SQL Server decides that it has go with the parallel plan or serial plan. Also note that sometimes parallel plans are better and sometimes serial plans are better because for parallelism also we have to pay for the cost of dividing the iterator task (shifting data) into multiple threads and then we have gather all the threads and make them one for the next iterator at some in time.
During query optimization, SQL Server checks queries that might benefit from parallel execution. SQL Server inserts exchange operators into the query execution plan to prepare the query for parallel execution.
An exchange operator is an operator in a query execution plan that provides process management, data redistribution, and flow control. The exchange operator includes the Distribute Streams, Repartition Streams, and Gather Streams logical operators as subtypes, one or more of which can appear in the Showplan output of a query plan for a parallel query.
The SQL Server query optimizer does not use a parallel execution plan for a query if any one of the following conditions is true:
- The serial execution cost of the query is not high enough to consider an alternative, parallel execution plan.
- A serial execution plan is considered faster than any possible parallel execution plan for the particular query.
- The query contains scalar or relational operators that cannot be run in parallel. Certain operators can cause a section of the query plan to run in serial mode, or the whole plan to run in serial mode.
Parallel aware Iterators are
- Clustered Index Seek
- Clustered Index Scan
- Parallelism ( Exchange Operators )
- Distribute Streams
- Repartition Streams and
- Gather Streams
In the next post ( Parallelism – Part II ) I will write some more notes on parallelism.
Cheers!. Thanks for reading !
-Pawan Khowal
MSBISkills.com
Improving my SQL BI Skills 
Hi There,
Gratitude for putting up this prolific article! You truly make everything a cake walk. Genuinely good stuff, saving time and energy.
I have an MS SQL Server 2012 database on a remote server, and since moving to a new laptop, I have been unable to connect to the database using SQL Server Management Studio 2012. I’m pretty sure this has something to do with my local machine, as I have never had a problem before. I installed SQL Server 2012 and SQL Management Studio 2012, and both seem to be working ok. I can connect to the local SQL database using management Studio, however when I try to connect to my remote database, I’m getting the following error:
Cannot connect to 104.238.72.41.\MSSQLSERVER2012
A network-related or instance-specific error…. error: 26 – Error Locating Server/Instance Specified).
I have checked that SQL server is running locally, and allowed ports 1433 and 1434 through my firewall. I’ve checked and double-checked the server name and login details. The online application which connects to the database (www.conservationneeds.org) is not experiencing any problems.
Once again thanks for your tutorial.
Obrigado,
Merry
LikeLike