SQL execution plans and Indexes

In order to appreciate the on what indexes are to offer, we need first to understand what are execution plans.

Execution plan - To be able to execute queries, the SQL Server Database Engine must analyze the statement to determine an efficient way to access the required data and process it. This analysis is handled by a component called the query optimizer. The input to query optimizer consists of the query, the database scheme(table and index definitions), and the database statistics.

In short they have written a good algorithm based on statistics for the SQL Server Database choose the most efficient path on executing our query.

The sql server also store execution plan on cache that’s why they can reuse the same plan executing familiar queries.

The Query optimizer chooses a query plan using a set of heuristics to balance compilation time and plan optimality in order to find a good query plan.

The sql server also store execution plan on cache that’s why they can reuse the same plan executing familiar queries.

A query execution plan is the definition of:

• The sequence in which the source tables are accessed.

Typically, there are many sequences in which the database server can access the base tables to build the result set. For example, if a SELECT statement references three tables, the database server could first access TableA, use the data from TableA to extract matching rows from TableB, and then use the data from TableB to extract data from TableC.

• The methods used to extract data from each table. (SQL Server’s index usage)

There are different methods for accessing the data in each table.

1.) If only a few rows with specific key values are required, the database server can use an index.

2.) If all rows in the table are required, the database server can ignore the indexes and perform a table scan.

3.) If all rows in a table are required but there is an index whose key columns are in an ORDER BY, performing an index scan instead of a table scan might save a separate sort of the result set.

4.) if a table is very small, table scans might be the most efficient method for almost all access to the table.

Indexes -the simplest explanation for this one is the bookmark of your databases for faster search.

Indexes -the simplest explanation for this one is the bookmark of your databases for faster search.

clustered index - determines how the data rows are physically stored in the table. only one clustered index per table often this is the PK (primary key).

non-clustered index - Separate lookup structure, it’s like when you use the non-clustered index its just a pointer pointing to the column you are looking. Used to speed up queries on columns.

Now that we’ve defined the key terms, let’s move on to design guidelines and best practices. In software engineering, every decision comes with trade-offs and indexing is no exception.

Designing the right indexes for a database and its workload is a delicate balance between query performance, update overhead, and storage cost.

• Narrow indexes (indexes with few key columns) use less storage and have lower maintenance costs.

• Wide indexes (indexes with many columns) can improve performance for more queries but require more storage and higher update overhead.

Finding the most efficient set of indexes often requires experimentation and testing multiple designs against your workload.

A common design mistake is to create many indexes speculatively to "give the optimizer choices". The resulting over indexing slows down data modifications and can cause concurrency problems.

A simple explanation why over index will cause concurrency problem is that because for our every write the table itself is modified and every index on that table could be affected and also must be updated. Now, each index requires locks to keep the index consistent.

More indexes ⇒ more locks equals more chance of concurrency problems.

Types of Scans

When SQL Server (or any database engine) executes a query, it decides how to retrieve the data from a table or an index. The most common access methods are:

1. Table Scan –

   The database reads every row in the table to find the matching data.

   🟠 Happens when there’s no useful index or the optimizer estimates that scanning the entire table is faster than using an index.

2. Index Scan –

   The database reads all rows in the index, not just the ones it needs.

   🟡 More efficient than a table scan because indexes are smaller and ordered, but still involves reading many rows.

3. Index Seek –

   The database goes directly to the exact rows it needs by using the index key.

   🟢 This is the most efficient access method and happens when your query is SARGable (Search Argument Able) — meaning it uses predicates like = or BETWEEN that can take advantage of the index.

   A high level representation on how execution plan work.

   Note: Then SQL Server has an automatic caching when you executed your query the 2nd time. The database doesn’t have to go to the disk the 2nd time because it can either get the data from its own cache.

Things to remember on designing your indexes

1.) Indexes are most beneficial on columns that are frequently used in read operations and not heavily updated or inserted. (Indexes help reads but slow down writes)

2.) An index might be useful for columns with many distinct data values, but less so for columns with many duplicate values. For columns with many NULLs or those that have well-defined subsets of data, you can use a filtered index.

3.) ****If the column has lots of duplicate values, the index almost become useless.

4.) Determine which index options can enhance performance

5.) Check if an existing one already does almost the same job, and if it does, maybe just modify it instead of adding a duplicate.

6.) Avoid over- Indexing.

7.) You can add indexes on your views.

8.) Create non-clustered indexes on the columns that are frequently used in predicates and join expressions in queries. These are your SARGable columns**. (Search Argumentable Columns) However, you should avoid adding unnecessary column to indexes. Adding too many index columns can adversely affect disk space and index update performance.

9.) Covering indexes can improve query performance because all the data needed to meet the requirements of the query exists within the index itself. With that logic it’s useful because your execution plans might go for Index Scans.

Now’ let’s dive into examples.

Here I created a table copy one with index and other is none.

Retrieving employee email without Index.

Here It’s doing a full - table scan, right-clicking the type of scan and selecting properties will give you this information.

Now here is the table with the Index execution plan. Take a look at the actual number of rows read.

By hovering also on the Execution plan where Index Seek happened, you can check the Index that is being used and the object

.

Now, let’s check what’s wrong with this code?

It’s converting the department entity into to lower before comparing it to the parameter department. A very bad practice indeed.

The best optimize way is to simply change the way your database handle collation. You can simply check your SQL collation if it’s case sensitive or not.

Now here I’m pretty sure the query gets slower here.

Now let’s dive into the execution plan. Even though the execution plan showed an Index Scan that is much better than the full table scan. It didn’t much help because take a look. We are reading 10,000 records total imitating the behavior of just a full table scan.

This topic is still quite broad, and there are still gaps that need to be filled — such as understanding how execution plans are stored and reused from the cache, and how different index strategies affect those cached plans. While this post only scratches the surface, it sets a good foundation for exploring query performance tuning in more depth. I’ll be continuing this series as I dig deeper into how SQL Server optimizes and executes queries behind the scenes.

Sources:

https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-index-design-guide?view=sql-server-ver17&utm_source=chatgpt.com

https://learn.microsoft.com/en-us/sql/relational-databases/performance/execution-plans?view=sql-server-ver17

https://learn.microsoft.com/en-us/sql/relational-databases/indexes/tune-nonclustered-missing-index-suggestions?view=sql-server-ver17&source=docs

https://www.youtube.com/watch?v=O7AzUDogXsw