// Week 19: Lock Escalations
Hello and welcome back to the SQL Server Performance Tuning seriesn. In the last 2 weeks we have talked about pessimistic and optimistic locking in SQL Server. Today I want to talk about one phenomenon in SQL Server that is specific to locking: so-called Lock Escalations. Before we go into the details of that problem, I want to talk a little bit more about the Lock Hierarchy that SQL Server uses internally.
[...]
// Week 18: Optimistic Concurrency
Hello and welcome back to the SQL Server Performance Tuning series. Last week I have kicked-off the 5th month of the training plan by talking about pessimistic concurrency. Today we will continue by talking about Optimistic Concurrency.
Row Versioning Optimistic concurrency was introduced back with SQL Server 2005 and is based on the principle of Row Versioning. The idea behind row versioning is that readers (SELECT queries) don’t acquire shared locks anymore.
[...]
// Week 17: Pessimistic Concurrency
Hello and welcome back to the SQL Server Performance Tuning series. Today marks the beginning of the 5th month where we will talk about Locking, Blocking, and Deadlocking in SQL Server.
SQL Server provides you a pessimistic and a optimistic concurrency model that define how concurrent queries are executed. In todays installment I want to give you an overview about the various isolation levels that are part of the pessimistic concurrency model, and next week I will talk in more details about the optimistic isolation levels that were introduced back with SQL Server 2005.
[...]
// Week 16: Cardinality Estimation From SQL Server 2014
Hello and welcome back to the SQL Server Performance Tuning series. In the last week we have talked about some problems with the cardinality estimation process in SQL Server. Today we will continue, and we will have a more detailed look on the shiny new Cardinality Estimator that is introduced with SQL Server 2014.
The new Cardinality Estimator One of the enhancements in SQL Server 2014 is a new Cardinality Estimator.
[...]
// Week 15: Problems With Cardinality Estimation
Hello and welcome back to the SQL Server Performance Tuning series. In the last 2 weeks you have read about statistics, why they are important and how they look like internally in SQL Server. In today’s installment I’m talking about a few limitations that the current Cardinality Estimation has, and how you can overcome these problems by applying various techniques.
Cardinality Estimation Errors As you have seen last week, SQL Server uses the Histogram and the Density Vector for the Cardinality Estimation, when an execution plan gets compiled.
[...]
// Week 14: Inside Statistics
Welcome back to the SQL Server Performance Tuning series. Today I want to talk in more details how Statistics internally are represented by SQL Server. Imagine the following problem: the “Estimated Number of Rows” in an operator in the Execution Plan is 42, but you know that 42 isn’t the correct answer to this query. But how can you interpret the statistics to understand from where the estimation is coming from?
[...]
// Week 13: Statistics
Welcome to the 4th month of the SQL Server Performance Tuning series. This month is all about Statistics in SQL Server, and how they help the Query Optimizer to produce a good-enough Execution Plan. Statistics are mainly used by the Query Optimizer to estimate how many rows would be returned from a query. It’s just an estimation, nothing more.
Statistics Overview SQL Server uses within the Statistics object a so-called Histogram, which describes within a maximum of 200 Steps the data distribution for a given column.
[...]
// Week 12: Parallel Execution Plans
In this installment of the SQL Server Performance Tuning series I want to talk in more details about Parallel Execution Plans in SQL Server. Executing a query with a parallel execution plan means that multiple threads are used by SQL Server to perform the necessary operators from the execution plan. In the first step I will give you a general introduction to the most common operators used in a parallel execution plan, and afterwards we talk in more details about how SQL Server decides if a parallel plan make sense.
[...]
// Week 11: Recompilations
Today I want to talk about Recompilations in the SQL Server Performance Tuning series. A recompilation happens when you execute a query, and another activity within SQL Server invalidates the remaining part of the execution plan. In that case SQL Server has to ensure the correctness of your execution plan, and a recompilation is triggered. Recompilations are introducing an additional CPU overhead to your SQL Server.
What are Recompilations? In the first step I want to lay out the differentiation between compilations and recompilations in SQL Server.
[...]
// Week 10: Plan Caching
In this installment of the SQL Server Performance Tuning series I want to talk more about Plan Caching and its side-effects in SQL Server. As you have already learned in the last week, every logical query submitted to SQL Server is compiled into a physical execution plan. That execution plan is afterwards cached in the so-called Plan Cache for further reuse. Let’s talk in the first step about adhoc SQL statements and which side-effects and performance problems they can introduce.
[...]