Indexes

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 »
Ed Pollack

SQL Server reporting – SQL Server Index Utilization

August 24, 2017 by

Understanding indexing needs allows us to ensure that important processes run efficiently and that our server hardware is not being over-taxed by poorly performing queries.

Collecting metrics on SQL Server index usage and missing index needs is critical to making smart decisions. To be truly proactive, though, we need to create a framework that allows us to quickly, efficiently, and regularly report on changes in indexing needs. With a system in place that can let us know when changes are needed, we can stay ahead of the optimization game and keep our customers happy!

Read more »
Ahmad Yaseen

How to control online Index Rebuild Locking using SQL Server 2014 Managed Lock Priority

July 18, 2017 by

When you perform a SQL Server Online Index Rebuild operation, introduced for the first time in SQL Server 2005, the index will not be taken down. But at a specific point, in which the new index new is built and switched from the old structure of the index, a special kind of lock, Schema Modification (SCH-M), will be granted. This lock may cause blocking if your database server is busy.

Read more »
Ed Pollack

Collecting, aggregating, and analyzing missing SQL Server Index Stats

June 27, 2017 by

Description

Indexing is key to efficient query execution. Determining what indexes are needed to satisfy production needs is often a game of cat and mouse in which we are forced to react to performance problems after they are brought to our attention. Being able to proactively monitor index needs and respond effectively before complaints are received can save us immense time while preventing costly performance messes.

Read more »
Page 1 of 212