Master of None

Indexes are the database objects that enable SQL Server to satisfy each data access request from a client application with the minimum amount of effort, resulting in the maximum performance of individual requests while also reducing the impact of one request upon another. Prerequisites: Familiarity with the following relational database concepts: Table, row, primary key, foreign key  Read more…

By David Durant 2011/03/16

By this stage, you should be familiar with the basics of SQL Server indexes. We’ve discussed what an Index actually is, as well as some of the most common types you’re likely to encounter. Now that we’ve seen some simple examples of how Indexes can be useful, we’re going to delve deeper into nonclustered indexes, as we’ll see how they can improve the performance of more complex queries.  Read more…

By David Durant |

Now that we’ve seen the basics of indexing, and taken a deeper dive into Nonclustered Indexes, this Level will focus on searching the table, which will, in turn, will lead us to a discussion of clustered indexes.  Read more…

By David Durant 2011/06/22

We’ve now seen how indexed and non-indexed tables perform in queries, and established “logical reads” as the metric for query performance. Now it is time to explain why logical reads are an excellent metric and also explain what is actually being read.   Read more…

By David Durant |

Included columns enable nonclustered indexes to become covering indexes for a variety of queries, improving the performance of those queries and with the only overhead being an increase the size of an index.  Read more…

By David Durant 2011/07/13 |

A nonclustered index entry consists of search key columns, included columns, and the bookmark. The bookmark value will be either a RID or the clustered index’s key, depending upon whether the table is a heap or a clustered index. Choosing the best clustered index for a table requires that you follow three guidelines to ensure that the index key will make a good bookmark.  Read more…

By David Durant 2011/08/03 |

A filtered index eliminates unusable entries from the index, producing an index that is as beneficial as, but much smaller than, an unfiltered index. If a certain subset of a table’s data is frequently requested, a filtered index can also be a covering index; resulting in a considerable reduction in IO.  Read more…

By David Durant 2011/08/24 |

Indexes that ensure data integrity in addition to locating rows.  Read more…

By David Durant 2011/09/14 |

Determining how, and if, SQL Server is using your indexes.  Read more…

By David Durant 2011/10/05 |

A detailed look the B-Tree structure – Leaf level and non-leaf level.  Read more…

By David Durant 2012/01/20

Internal and external fragmentation – Causes, cures, and when not to care  Read more…

By David Durant 2012/02/01 |

Options and impacts when creating, altering and dropping an index  Read more…

By David Durant 2012/02/17 |

What happens to your indexes during normal database activity?  Read more…

By David Durant 2012/02/29 |

A look at the information that helps SQL Server make better decisions.  Read more…

By David Durant 2012/03/16

Dos and don’ts that lead to a healthy, well-performing database.  Read more…

By David Durant 2012/03/28