// 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.
[...]
// Week 9: Understanding Execution Plans
Welcome back to the SQL Server Performance Tuning series. Today marks the start of the 3rd month of the training plan, which is all about Execution Plans in SQL Server. Execution Plans are the most important concept that you have to understand in SQL Server to make effective changes to improve performance for your queries. For that reason I’m giving you today a general introduction to Execution Plans in SQL Server, and how you can interpret and read them.
[...]
// Week 8: Covering Indexes & Tipping Point
Time goes by - in a few minutes you have already successfully passed the 2nd month of the SQL Server Performance Tuning series! In todays installment I want to talk a little bit more about Non-Clustered Indexes and some few negative side-effects that you can introduce with them.
Last week I have already talked about the Bookmark Lookup in SQL Server, and that they can be very dangerous. A Bookmark Lookup occurs, when SQL Server accesses a Non-Clustered Index in the Execution Plan, and additional columns must be retrieved from the underlying table (because they are not part of the Non-Clustered Index).
[...]
// Week 7: Non-Clustered Indexes
In the last week I have talked about Clustered Indexes in SQL Server. When you define a Clustered Index on your table, you are physically sorting your table data on the provided Clustered Key column(s). In addition to a Clustered Index, you can also define multiple Non-Clustered Indexes (up to 999) on a table in SQL Server.
A Non-Clustered Index is just a secondary index that you can define on some columns of your table.
[...]
// Week 6: Clustered Indexes
In the last week I have introduced heap tables to you. As we have said, a table in SQL Server can be a Heap Table or a Clustered Table - a table with a Clustered Index defined on it. And today we will have a more detailed look on Clustered Indexes, and how to choose the right Clustered Key.
Normally you can assume that your tables have already a Clustered Index defined, because every time when you create a Primary Key constraint in SQL Server, the constraint is (by default) enforced through a Unique Clustered Index.
[...]
// Week 5: Heap Tables
Welcome to the second month of the SQL Server Performance Tuning series. This month will be one of the most interesting and challenging ones - it’s the month where we are talking exclusively about Indexing, Indexing, and Indexing in SQL Server. But it’s worthwhile - trust me.
Today I’m covering so-called Heap Tables, and in the following 3 weeks we are talking about Clustered Indexes, Non-Clustered Indexes, and Indexing Strategies for your SQL Server database.
[...]