// 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.
[...]
// Week 4: Data Page Restrictions
You have just a few pages ahead of you, and then you have already passed the 1st month of the SQL Server Performance Tuning series - congratulations! Today I’m talking about limitations that you have with data pages, and why there are restrictions that you will love, while you will hate other restrictions.
As you have learned in week 2, a data page is always 8kb large, and you are able to store 8060 bytes of data on it.
[...]
// Week 3: Extent Management in SQL Server
Wow, it’s already the 3rd week in the SQLpassion Performance Tuning Training Plan! In the mean time you already have a very good understanding about how SQL Server works internally. Today I’m talking about Extent Management in SQL Server, because this is a very important topic, when we cover TempDb in week 23. On a very high level an extent is just a group of 8 pages of 8kb. An extent is therefore always a chunk of 64kb.
[...]
// Week 2: Data Pages - the Foundation of SQL Server
Last week we have laid out the foundation about how SQL Server executes queries. I have also already talked here a little bit about pages that are buffers of 8kb. Today we are further concentrating on these pages and drill into more details and what it means from a performance tuning perspective.
Pages are the foundation of SQL Server, everything in SQL Server is about pages. When we want to improve the performance of our queries, we try to lower down the page reads SQL Server needs for a specific query.
[...]
// Week 1: How SQL Server Executes a Query
Hello, and welcome to the first issue of the SQL Server Performance Tuning series. Before we go into the nasty details of performance tuning in SQL Server, I want to lay out today the foundation about how SQL Server executes a query. This is a very important part, because in the upcoming issues of the series we will further enhance our knowledge based these concepts.
The following pictures gives you an overview about the most important components within SQL Server, that are used when we are executing a query.
[...]