Don't blame SQL Server if your SQL Statements perform badly! But where do you start to investigate the problem? This article is a primer on were to start looking when your SQL runs slowly.  Read more...

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

The most common T-SQL command in use has to be the SELECT statement, it is the bedrock of any SQL Professional's day. Sometimes it's used to snatch some data from a table or two while some quick investigation is done, other times it is at the heart of a stored procedure or view that will inform business decisions for coming months or even years.  Read more...

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

A table that does not have a clustered index is referred to as a Heap. While a lot has been written about index fragmentation and how to defrag indexes, there is not much that talks about how to defrag a heap table.  Read more...

At some time or another every DBA has been faced with the challenge of solving a deadlock issue in their SQL Server database. In the following tip we will look at how indexes and more specifically clustered indexes on the right columns can help reduce the chance of your applications receiving this dreaded error  Read more...

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

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

Are your index statistics up to date? The SQL Server database engine uses statistics to determine which execution plan to use when a query is compiled. Index statistics are used to help the SQL Server engine determine the cardinality of different column values in a SQL Server table. If you want to give SQL Server a fighting chance at picking an optimal execution plan then you need to make sure you maintain your index statistics.  Read more...

This article shows an automated reorg/rebuild index for remote servers with notifications for the DBA.  Read more...

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

If you think about it carefully, you’ll realise, that index maintenance is necessary and that it makes sense, and that SQL Server is not doing this task automatically according to the book.  Read more...

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

Learn how to add those indexes you need in part 3 of Matt Perdeck's 8 part series on improving data access. This is based on the book ASP.NET Site Performance Secrets.  Read more...

In the first part of this series, learn how to pinpoint missing indexes in your databases.  Read more...

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

This article has a description of an index utilization tool that Ron Johnson uses to analyze the effectiveness and use of indexes in a system.  Read more...

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

This article demonstrates an Index Management strategy to tackle day to day performance issues due to improper indexes.  Read more...

This article describes a real-world example of performance tuning index maintenance for a large table of approximately 1 billion rows.   Read more...