Indexes

Ahmad Yaseen

SQL Server indexes – series intro

June 29, 2018 by

Description

In this series, we will dive deeply in the SQL Server Indexing field, starting from the surface by understanding the internal structure of the SQL Server tables and indexes, going deeper by describing the guidelines and best practices that we can follow to design the most efficient index and what operations can be performed on the created indexes. Having these knowledges about the SQL Server indexes, we have all the tools that help us in testing the lower part of the ocean and dive deeper with the two main types of the SQL Server Indexes; the Clustered and Non-Clustered, and the other types of indexes that can be customized to serve us improving your environment. After that, the adventure becomes more interesting when learning how to use this knowledge to tune the performance of our queries and touch the bottom of the ocean. In our way back to the surface, and before celebrating our achievements, we will collect statistical information about these indexes and use this information to maintain the indexes to take benefits from it continuously and gain the best application performance.

Read more »
Ahmad Yaseen

Top 25 interview questions and answers about SQL Server indexes

June 25, 2018 by

Q1: What is the difference between a Heap table and a Clustered table? How can we identify if the table is a heap table?

Heap table is a table in which, the data rows are not stored in any particular order within each data page. In addition, there is no particular order to control the data page sequence, that is not linked in a linked list. This is due to the fact that the heap table contains no clustered index.

Read more »
Ahmad Yaseen

Maintaining SQL Server indexes

May 31, 2018 by

In the previous articles of this series (see the full article TOC at bottom), we discussed the internal structure of SQL Server tables and indexes, the guidelines that you can follow in order to design a proper index, the list of operations that can be performed on the SQL Server indexes, how to design effective Clustered and Non-clustered indexes, the different types of SQL Server indexes (above and beyond Clustered and Non-clustered indexes classification), how to tune the performance of the inefficient queries using different types of SQL Server Indexes and finally, how to gather statistical information about index structure and the index usage. In this article, the last article in this series, we will discuss how to benefit from the previously gathered index information in maintaining SQL Server indexes.

Read more »
Ahmad Yaseen

Gathering SQL Server indexes statistics and usage information

May 24, 2018 by

In the previous articles of this series (see the full article TOC at bottom), we discussed the internal structure of both the SQL Server tables and indexes, the best practices that you can follow in order to design a proper index, list of operations that you can perform on the SQL Server indexes, how to design effective Clustered and Non-clustered indexes, the different types of SQL Server indexes, above and beyond Clustered and Non-clustered indexes classification and finally how to tune the performance of the bad queries using the different types of SQL Server Indexes. In this article, we will discuss how to gather statistical information about the index structure and the index usage information.

Read more »
Ahmad Yaseen

Tracing and tuning queries using SQL Server indexes

May 18, 2018 by

In the previous articles of this series (see the full article TOC at bottom), we discussed the internal structure of the SQL Server tables and indexes, the best practices to follow when designing a proper index, the group of operations that you can perform on the SQL Server indexes, how to design effective Clustered and Non-clustered indexes and finally the different types of SQL Server indexes, above and beyond Clustered and Non-clustered indexes classification. In this article, we will discuss how to tune the performance of the bad queries using SQL Server Indexes.

Read more »
Ahmad Yaseen

Working with different SQL Server indexes types

May 8, 2018 by

In the previous articles of this series (see the full article TOC at bottom), we discussed the internal structure of both SQL Server tables and indexes, the main guidelines that you can follow to design a proper index, the list of operations that can be performed on the SQL Server indexes, and finally how to design effective Clustered and Non-clustered indexes that the SQL Server Query Optimizer will always take benefits from, in speeding up the data retrieval process, which is the main goal of creating an index. In this article, we will go through the different types of SQL Server indexes, above and beyond Clustered and Non-clustered indexes classification, and when to use them.

Read more »
Ahmad Yaseen

Designing effective SQL Server non-clustered indexes

May 7, 2018 by

In the previous articles of this series (see below for the full index of articles), we went through the internal structure of SQL Server tables and indexes, listed a number of guidelines that help in designing a proper index, discussed the operations that can be performed on SQL Server indexes and finally showed how to design and create a SQL Server Clustered index to speed up data retrieval operations. In this article, we will see how to design an effective Non-clustered index that will improve the performance of frequently used queries that are not covered with a Clustered index and, in doing so, enhance the overall system performance.

Read more »
Ahmad Yaseen

Designing effective SQL Server clustered indexes

May 3, 2018 by

In the previous articles of this series (see bottom for a full index), we described, in detail, the structure of SQL Server tables and indexes, the basics and guidelines that help us in designing a proper index and finally the list of operations that can be performed on the SQL Server indexes. In this article, we will see how we could design an effective clustered index that the SQL Server Query Optimizer will always take benefits from, in speeding up the data retrieval process, which is the main goal of building an index.

Read more »
Ahmad Yaseen

SQL Server index operations

April 19, 2018 by

In the previous articles of this series, we described the structure of the SQL Server tables and indexes, the main concepts that are used to describe the index and the basics and guidelines that are used to design the proper index. In this article, we will go through the operations that can be performed on the SQL Server indexes.

Creating Indexes

Before creating an index, it is better to follow the index design guidelines and best practices that are described in the previous article, to determine the columns that will participate in the index, the type of the created index, the suitable index options, such as the FillFactor or Sort in TempDB, and the storage location of that index.

Read more »
Ahmad Yaseen

SQL Server index structure and concepts

March 19, 2018 by

In my previous article, SQL Server Table Structure Overview, we described, in detail, the difference between Heap table structure, in which the data pages are not sorted in any ordering criteria and the pages itself are not sorted or linked between each other, and Clustered tables, in which the data is sorted within the data pages and the pages will be also linked in a double linked list, based on the index key. In this article, we will go through the structure of the SQL Server index, itself.

Read more »
Ahmad Yaseen

SQL Server table structure overview

March 7, 2018 by

Microsoft SQL Server is a relational database management systems (RDBMS) that, at its fundamental level, stores the data in tables. The tables are the database objects that behave as containers for the data, in which the data will be logically organized in rows and columns format. Each row is considered as an entity that is described by the columns that hold the attributes of the entity. For example, the customers table contains one row for each customer, and each customer is described by the table columns that hold the customer information, such as the CustomerName and CustomerAddress. The table rows have no predefined order, so that, to display the data in a specific order, you would need to specify the order that the rows will be returned in. Tables can be also used as a security boundary/mechanism, where database users can be granted permissions at the table level.

Read more »
Ayman Elnory

SQL Server Query Execution Plans for beginners – Clustered Index Operators

March 5, 2018 by

We have discussed how to created estimated execution plans and actual execution plans in various formats in my previous article SQL Server Query Execution Plan for beginners – Types and Options.

In this article we will continue discussing the various execution plan operators related to clustered indexes, and what they do, when do they appear and what happens when they do.

Read more »
Gerald Britton

Top 10 questions and answers about SQL Server Indexes

October 5, 2017 by

Introduction

Without a doubt, few technologies in SQL Server cause as much confusion and the spread of misinformation than indexes. This article looks at some of the most asked questions and a few that should be asked but often are not. We’ll be using SQL Server 2016 for the examples and a free tool, for SQL Server query execution plan analysis, ApexSQL Plan, to explore the effects of indexes on a typical business problem: A table of customers.

Read more »
Page 1 of 3123