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.
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.
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.
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).
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.
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.
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.
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.
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.
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.