Indexes for Analysts — What to Know

You're not a DBA. But knowing 6 index facts will speed up half your queries. FIND_VIDEO: search 'database index analyst tutorial' — recommended channel: EverSQL / Crunchy Data. Aim for 10 min or under.

19 minutesVideo LessonPDF notes

Watch Indexes for Analysts — What to Know on YouTube

🎯 Free Guest Mode: You are learning for free. Sign in to save your completion progress and quiz answers.

Ready to continue?

Mark this lesson as complete when you're ready to proceed.

Key moments

The Dangers of Over-Indexing — Establishes why excessive indexes harm write operations and derail execution plan generation.
Phase 1: OLAP vs OLTP Strategy — Contrasts analytical and transactional workloads to choose between columnstore and clustered index designs.
Phase 2: Pattern-Based Indexing — Demonstrates analyzing codebase scripts and usage metrics to map tables to optimal index types.
Phase 3: Scenario-Based Tuning — Explains identifying slow queries and inspecting execution plans before and after adding targeted indexes.

PDF notes

Bottom line: You will design a structured, four-phase database indexing strategy tailored to OLAP and OLTP workloads. You will learn to identify high-impact tables and query bottlenecks to apply the correct index types without falling into the performance trap of over-indexing.

This lesson on Indexes for Analysts — What to Know is hands-on and example-driven. You will design a structured, four-phase database indexing strategy tailored to OLAP and OLTP workloads. You will learn to identify high-impact tables and query bottlenecks to apply the correct index types without falling into the performance trap of over-indexing.

What You'll Be Able To Do

Differentiate indexing requirements between OLAP read-heavy aggregations and OLTP write-heavy transactions.
Formulate an organizational indexing strategy that prevents database engine execution plan confusion.
Identify frequently queried tables, filter predicates, and join keys across application SQL scripts.
Select appropriate index types including clustered, nonclustered, columnstore, filtered, and unique indexes.
Evaluate execution plans before and after adding indexes to resolve full table scans and expensive operators.

Detailed Concept Walkthrough

1. The Golden Rule: Avoid Over-Indexing

Indexes carry an operational cost; adding too many degrades write operations and confuses the query optimizer. The optimal strategy follows a 'less is more' approach with targeted, high-value indexes.

Mechanism: Every INSERT, UPDATE, and DELETE operation forces the database engine to rearrange, re-sort, and rewrite index pages across all associated indexes.
Under the Hood: A proliferation of redundant indexes drastically expands the search space for the query optimizer, increasing execution plan generation time and raising the risk of choosing a suboptimal plan.
Best Practice: Establish an explicit team guideline prohibiting speculative indexing and prioritize a minimal set of highly selective indexes.

-- Inspect index write vs read overhead before adding new indexes
SELECT 
    OBJECT_NAME(s.object_id) AS TableName,
    i.name AS IndexName,
    s.user_updates AS WriteOperations,
    (s.user_seeks + s.user_scans + s.user_lookups) AS ReadOperations
FROM sys.dm_db_index_usage_stats s
JOIN sys.indexes i ON s.object_id = i.object_id AND s.index_id = i.index_id
WHERE s.database_id = DB_ID();

Key Takeaway: Over-indexing degrades write performance and leads the query optimizer toward inefficient execution plans.

2. Phase 1: Initial Strategy by Workload Type

System architecture dictates the indexing baseline, distinguishing between analytical batch workloads and normalized transactional operations.

OLAP Workloads: OLAP data warehouses rely on denormalized fact and dimension schemas where read performance and aggregation over large datasets dominate system cost.
OLTP Workloads: OLTP transactional systems consist of highly normalized tables handling continuous concurrent reads and writes, making write latency and foreign key navigation the primary focus.
Syntax Rule: Apply clustered columnstore indexes on large fact tables for analytical aggregation, and standard clustered indexes on transactional primary keys for point lookups and joins.

-- OLAP: Create clustered columnstore index on large fact table
CREATE CLUSTERED COLUMNSTORE INDEX cci_FactInternetSales 
ON FactInternetSales;

-- OLTP: Create standard clustered index on primary key
CREATE CLUSTERED INDEX cx_Orders_OrderID 
ON Orders(OrderID);

Key Takeaway: Target columnstore indexes for OLAP fact tables and clustered primary key indexes for normalized OLTP schemas.

3. Phase 2: Usage Pattern Index Selection

Index design must reflect actual SQL access patterns discovered by auditing join conditions, filter clauses, and aggregation targets across codebase scripts.

Execution Flow: Analyze historical queries and application scripts to identify top accessed tables (e.g., FactInternetSales) and frequently filtered fields (e.g., OrderDateKey).
Index Matching: Assign clustered indexes to primary keys, nonclustered indexes to foreign keys and WHERE filters, columnstore indexes to fact tables, filtered indexes to fixed partition slices, and unique indexes to enforce deduplication.
Best Practice: Leverage static analysis or LLM-assisted script auditing to extract table occurrence counts, predicate filters, and group-by columns across all repository queries.

-- Nonclustered index for frequently filtered and joined columns
CREATE NONCLUSTERED INDEX ix_FactInternetSales_OrderDateKey
ON FactInternetSales(OrderDateKey)
INCLUDE (SalesAmount);

-- Filtered index targeting a specific subset / recent data
CREATE NONCLUSTERED INDEX ix_FactInternetSales_CurrentYear
ON FactInternetSales(OrderDateKey, CustomerKey)
WHERE OrderDateKey >= 20240101;

Key Takeaway: Map index types directly to demonstrated query usage patterns rather than theoretical access paths.

4. Phase 3: Scenario-Based Bottleneck Resolution

Scenario-based indexing isolates verified slow-running queries and inspects their physical execution plans to replace expensive operations with index seeks.

Identification: Collect slow queries reported by users, captured in application logs, or flagged by database query profiling.
Plan Inspection: Examine execution plans to pinpoint performance anti-patterns, specifically full table scans, clustered index scans, or unindexed nested loop joins.
Verification: Deploy candidate indexes and re-run execution plans to verify that the query engine switches from expensive full scans to efficient index seeks.

-- Identify slow queries with high worker time or execution count
SELECT TOP 10
    qs.total_worker_time / qs.execution_count AS AvgCPUTime,
    qs.execution_count AS ExecutionCount,
    SUBSTRING(qt.text, (qs.statement_start_offset/2)+1, 
        ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(qt.text)
         ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) AS QueryText
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
ORDER BY AvgCPUTime DESC;

Key Takeaway: Validate index effectiveness by comparing execution plans before and after deployment to confirm scan-to-seek transitions.

Topics Covered in Indexes for Analysts — What to Know

The Dangers of Over-Indexing (0:00 - 1:15) — Establishes why excessive indexes harm write operations and derail execution plan generation.
Phase 1: OLAP vs OLTP Strategy (1:16 - 3:00) — Contrasts analytical and transactional workloads to choose between columnstore and clustered index designs.
Phase 2: Pattern-Based Indexing (3:01 - 4:30) — Demonstrates analyzing codebase scripts and usage metrics to map tables to optimal index types.
Phase 3: Scenario-Based Tuning (4:31 - 5:45) — Explains identifying slow queries and inspecting execution plans before and after adding targeted indexes.

SQL Cheat Sheet

CREATE CLUSTERED COLUMNSTORE INDEX — Optimizes large fact tables for analytical aggregations
```
CREATE CLUSTERED COLUMNSTORE INDEX cci_Sales ON FactInternetSales;
```
CREATE CLUSTERED INDEX — Physically sorts table data on primary key
```
CREATE CLUSTERED INDEX cx_Orders_ID ON Orders(OrderID);
```
CREATE NONCLUSTERED INDEX — Creates secondary lookup structure for filters/joins
```
CREATE NONCLUSTERED INDEX ix_Date ON Orders(OrderDateKey);
```

CREATE NONCLUSTERED INDEX ... WHERE — Indexes a specific subset of table rows

CREATE NONCLUSTERED INDEX ix_Active ON Orders(Status) WHERE Status = 'Pending';

CREATE UNIQUE INDEX — Enforces uniqueness and speeds lookups on keys
```
CREATE UNIQUE INDEX ux_Customer_Email ON Customers(Email);
```

Comparison Table

Dimension	OLAP Databases	OLTP Databases
Primary Workload	Heavy read queries, aggregations	Frequent insert, update, delete
Main Objective	Optimize read/reporting query performance	Optimize transactional write latency
Data Model	Denormalized facts and dimensions	Highly normalized relational tables
Recommended Index	Clustered columnstore indexes	Clustered primary keys, selective nonclustered
Write Sensitivity	Batch ETL writes (usually nightly)	Continuous writes requiring lean indexing

Common Pitfalls

Mistake: Adding indexes on every column to speed up queries. Avoid: Index only proven filter and join columns to prevent write degradation.
Mistake: Applying OLTP rowstore index patterns to large OLAP fact tables. Avoid: Use clustered columnstore indexes for heavy analytical fact table aggregations.
Mistake: Deploying an index without verifying its execution plan impact. Avoid: Compare before and after execution plans to verify scans become seeks.

FAQs

Why does having too many indexes slow down database writes? Every INSERT, UPDATE, or DELETE requires the database to update, resort, and rebalance all associated index structures.
How can having many indexes cause the database to pick a bad execution plan? An excessive number of indexes complicates optimizer cost calculations, leading to slower plan generation or selection of suboptimal indexes.
When should I use a filtered index instead of a standard nonclustered index? Use filtered indexes when queries consistently target a predictable subset of data, such as active records or a specific date window.
Is adding indexes the only way to fix a slow query? No, query refactoring, schema adjustments, and ETL tuning are also critical performance optimization techniques.

Common pitfalls

Mistake: Adding indexes on every column to speed up queries. Avoid: Index only proven filter and join columns to prevent write degradation.
Mistake: Applying OLTP rowstore index patterns to large OLAP fact tables. Avoid: Use clustered columnstore indexes for heavy analytical fact table aggregations.
Mistake: Deploying an index without verifying its execution plan impact. Avoid: Compare before and after execution plans to verify scans become seeks.

All right, friends. So now for each SQL data project, we have to make sure that we create a clear guidance about the index strategy. And everyone in the team has to commit and follow the strategy in order to make sure that each index that is created in the project fulfill a purpose. And that's because without a clear strategy about the indexing, I'm going to promise you there will be a lot of redundancy, unused indexes, a waste of storage, and the whole system of your project is going to be slow and bad. So now what we going to do, I'm going to show you my indexing strategy that I usually follow in my projects. But I'm going to tell you from now, there is like not one strategy that can fit any project and any scenario. That's why the team of each project should brainstorm in order to make their own strategy. So now let's have a look to my indexing strategy. And now if I have to pick only one recommendation from me to you in this indexing tutorial, I'm going to have this advice for you: Avoid over-indexing. Over-indexing is the biggest mistake and trap that a lot of developers do, where they think adding more indexes, that sounds like we are speeding up things and our query is going to be fast. But I have to tell you, this exactly lead to the opposite. And here's why. As we learned, each time you add a new data to your table, your index has to get updated, sorted, rearranged. That's means having too many indexes, what's going to happen? Your INSERT, UPDATE, DELETE operations going to be slow. And this means your database is slower and not faster. And one more very important reason why over-indexing is bad is you make the database confused while creating the execution plan. As we learned, the SQL database has to create the best execution plan for your query. And if you have a lot of indexes in your database, it's going to make the process of creating an execution plan complicated for the database, which makes this, of course, for database harder to choose the best path and index. And as well you open the door for bad execution plans, and this means it's going to slow the query because first the database has to create execution plan before executing your query. So, again, it has a bad effect for the performance. And as well, there is another bad thing. It's going to make it harder for the database to decide what is the best execution plan for your query. And having too many indexes might make the SQL database choosing a really bad execution plan. So, over-indexing confuse the execution plan and as well makes the query slower. So, that's why I call this a golden rule, and you have to commit to it. Just avoid over-indexing because it is double-edged sword. And here exactly you have to have the mindset of less is more. So having a few effective indexes is way better than having a lot of indexes. So keep it in mind and write it in your development guideline for the team with big statement: Avoid over-indexing. So this is the first statement in your indexing strategy. So now let's check the rest. All right. So now we can split the indexing strategy into four phases. And each phase has multiple steps. So now the first step is we going to go and create an initial indexing strategy. So now once you start a new SQL project, you have to define the objectives of the projects very clearly. So that means we have to make it clear what we are focusing on, what we want to achieve. And in order to define the goal of your indexing strategy, you have to understand your system. We have mainly two types of databases. In one hand, we have OLAP databases. It stands for Online Analytical Processing. The purpose of this database is for data analytics. And an example for that is the data warehouse. So in data warehousing, we go and extract the data from multiple sources and then we prepare it and transform it and put it in one big storage. And we call this process an ETL process. And then the front end, we have like reports and dashboards, where the data is summarized and aggregated and presented for the end user. And these reports could be used from users in order to analyze and have insights about the data. And now in order to generate those reports, there will be like heavy reading on the data warehouse database. So that means there will be huge queries that's going to access the database in order to aggregate and prepare the data for the visualizations. But now in the other hand, we have the OLTP systems, Online Transactional Processing. It is like an e-commerce, finance, banking, where you have at the back end a database where the data is stored. And on the front end, we have like an applications for the end users. So now as the users are interacting with the app, this going to cause write operations on the database, so inserting new data or changing data. And as well there will be read operations on the database in order to show the data in the app. So we have both write and read. So now, of course, we have to ask ourself, what is the goal? What do we want to achieve? And here mainly there is like two strategy. Either you want to improve the read performance or the write performance. Now if you are looking to the OLAP system, here's really you have to understand the project. Where is the struggle? Sometimes it could be like the ETL process itself is slow. And mainly the ETL is writing data from the sources in the data warehouse. And maybe you have scenario where it takes like every day 10 hours. And 10 hours is, of course, a problem because you cannot wait so long in order to get a new data, fresh data, to the report every day. So you can make the goal of the project is to optimize the write performance. You want to speed up the ETL. But actually most of those projects having another issue. Well, it is the read operation on the database because data warehouses normally have really big datasets. And at the front end, the reports generate large, complex queries on the database. So that means the read process going to be the pain point in each OLAP system. So normally the big goal in each OLAP system going to be how to optimize the read performance. But now in the right hand with the OLTP, we have different nature of database and scenario. What can happen, you will not have like big queries from the apps. You're going to have like many query, many transactions happening between the application and the database. So you're going to have like massive amount of read and write transactions. So the whole time we are reading, writing, reading, writing, and so on. But with the OLAP, we have like something bigger and slower because in the ETL, we usually run it only once. That means we are writing only once a new data to the database. And this happens usually at the night. But on the transactional systems, you have a lot of read/writes all time. Again, depend on the project, but usually the main pain points in the OLTP is the write operation. So it could be like this. If you are building OLTP system, the main goal is to optimize the write performance. Now, of course, the question is how to do that, how we going to optimize that? Well, again, we have to understand the nature of the database. What do we have in the OLAP systems is usually like a data model where you have very big fact tables, and around the facts we have like multiple dimensions that are connected to the facts. So those fact tables are really big tables in the database, and each time they are used in order to build a report. And a report's going to be using all time those facts in order to prepare the data for the visualizations, and a lot of aggregations query going to be done on the facts. And now, of course, you have to answer now the question: Which type of index should we use in this scenario? Well, we have a perfect one called a columnstore index. So the best practice here is, and you can make it as a strategy for the whole project, that we make all fact tables as a columnstore index because this is what we are doing in the OLAP: We are aggregating large datasets. But now the data model and the scenario is completely different at the right side. Here we going to have like a lot of tables and they have like different sizes and so on. And there are like a lot of relationship between all those tables. So it is completely connected. So you have a lot of like primary keys and foreign keys relationships between them. And normally those tables are completely normalized table, so they are like small pieces. But on the left side, we have denormalized tables as the facts. So here is like one strategy that we can follow in the indexing of the OLTP is that we create clustered index for each primary key of our tables. This, of course, going to improve a lot of stuff like searching, sorting, and as well joining tables together. But, of course, since we are focusing on optimizing the write performance on the OLTP, you have to be more sensitive by adding new indexes compared to the OLAP because each index you add, it could be a reason why the data is written very slowly. So in the OLTP, you have to be way more careful adding indexes. So now, as you can see, you have to understand the nature of your projects, you have to understand what is the main issue. Once you understand your project, you can go and define like a goal for optimizing the system. So either read or write or maybe both of them, and with that you are making like the initial strategy of indexing your system. All right. So with that, we have an initial strategy for our indexing, and we have a rough plan. Now in the next phase, we have usage patterns indexing. So now we're going to do a deep dive into our project. And the first thing that you have to do is that we have to identify the frequently used tables and columns. So that means you have to go and check the queries used in your projects in order to understand, okay, what is the most important table that is used in many queries? Like, for example, here we have the FactInternetSales. It is used like in many, many queries in our scripts. So here you are like developing a feeling about what are the most important frequently used tables. And not only that, you can go and check how we are filtering the data on those queries. So, for example, we have over here we are filtering by the OrderDateKey. Is this kind of filtering is used like in multiple queries? So as you can see, we have like here a couple of queries where we are doing always the same, where we are filtering the data by the date. So with that, we understand there is like a pattern inside our projects where this column is used mainly on filtering and as well for aggregating. So that means you do a deep dive in order to understand what are the most and frequently used tables and columns inside your scripts. And now, of course, what I usually do, I go and use the help of the AI and ChatGPT, where I give it my code and then ask questions about it. For example, this prompt, it says: "Analyze the following SQL queries and generate a report on table and column usage statistics, and for each table provide the total number of times the table is used across all queries, a breakdown for each column in the table showing the number of times each column appears, and I would like to see as well the primary usage of each column (filtering, joining, grouping, and so on)." And in the output, as you can see, we got like nice statistics about my scripts. So, as you can see, the most used fact table is FactInternetSales. It is like 13 times used in the projects. And then we can see like statistics about each column that is inside these facts. So most of the time is the Sales is used for aggregating. And as we saw, the OrderDateKey is used like five times for filtering, and the other keys is used for joining tables. So, as you can see, it's amazing, right? Now we can identify which tables are important, which columns as well are important, and we can like base on those informations maybe derive our indexing for our database. So with that, we have identified our frequently used tables and columns, and now the next step is that we have to go and choose the right index type. And as we learned before, we have multiple types of indexes, and that's really depend on the usage and the scenario. So, for examples, if your columns are primary keys, then go with a clustered index. And if you are using columns that are not primary key, where you are doing joining, filtering, then think about the nonclustered index. And, of course, if the table is very big, as we said, you can go and use the columnstore index. And if you are targeting always like a subset of data only like one-year informations, then you can think about the filtered index. And the last one, if you have like unique column where you don't have any duplicates, then you can go and apply a unique index. So it depends on the scenario and the usages, you have to choose the right index. And, of course, the last step in this phase is that you have to go and test your index whether everything is working fine. So that's all for the phase two. Then we go to phase three: scenario-based indexing. So here we have to tackle and focus on specific issues, to specific pain points. So that means we have first to identify the slow queries. So it could be reported from users or the team is doing like analyzing on the logs in to understand which queries are causing like performance issues. And now once you get a list of slow queries, then you have to analyze them one by one. And it is time to dig into the execution plans. So, as we learned, we can check how SQL is implementing our queries and start looking for areas, for example, where the SQL is doing a full scan of the tables or maybe using expensive operations like nested loop joins and so on. So once you understood where is exactly the pain point, the next step is that you have to go and choose the right index. So which type of indexes we're going to use in order to optimize the query? And once you go and create the index, the last step is that you have to go and test it. So you're going to run again the execution plan in order to make sure that your query is using the index that you have just created. So that means you have to go and compare the execution plans before and after. And if you see that there is no benefit, then something is wrong. That means you have to go and investigate more and analyze the execution query and maybe choose a better index way. And you have to do this process for each slow query until you get all your queries fast. But, of course, don't forget indexing is not the only method on how to optimize the speed of queries. So, as you can see, through these three phases, we went from a very generic methods on how to index our system to something very specific and scenario-based. So, as you can see, as we moving in the phases, we are doing more deep dive into our projects. All right. So now moving to the last phase, we have the monitoring and maintenance of our indexes. As we learned, the job doesn't stop by just creating and implementing indexes. We have to be responsible by keeping eye on the health of our indexes. And here the databases offers a lot of statistics and metadata about your data that you could use in this phase. So the first step is to monitor the usage of the indexes. And as we learned, we can use the Dynamic Management Views or functions that we can find in the system schema, where we can see the number of usage of each index and when the last time our queries did use the indexes. So with that, we can go and find out all those indexes that we have created and never been used in our projects. And now the next step is that we can go and monitor the missing indexes. So here we can go and check what are the recommendations from the database, where the database is reporting missing indexes from the execution plan. And, again, we can go and use those Dynamic Management Views or functions in order to see more details. And as well we can go and monitor whether we have duplicates in the indexing. It happens a lot if you have like a lot of developers in your team, so it could be that they are working parallely to optimize the performance of slow queries and then go and create multiple indexes for the same column. So this is something that we can go and check whether we have duplicates in our indexes. And if you have duplicates, then you have to go and find how you can go and consolidate them. Then the next step, we have to go and update the statistics. So, as we learned, statistics are very important for the execution plan because the database engine use those informations to decide the best execution plan for your query. And if the statistics are old, then the database going to make wrong decisions about how to execute your query, which might leads to bad performance. So here again we have like special functions in order to monitor the statistics. But here my recommendation is that each weekend have a job that go and create all the statistics of your database. And the last step, we don't have to forget about monitoring the fragmentations. As we learned, over the time as you are doing modifications on the tables, what could happen the order of the data pages could get wrong or there are like free spaces on the data page that are not used. So we have like fragmentations in the index. And the same thing, we have to monitor the fragmentations of each tables. And here if the percentage is between 0 and 10, then there is no issue. But if the fragmentation is between 10 and 30, then we have to go and reorganize the index. And if it's more than 30, then this is alerting. You have to go and rebuild the whole index. And usually for the monitoring, I go and build like automated dashboards in Power BI or Tableau, where I go and extract all those metadata and create a nice dashboard in order to monitor the health of the database. Or you can go and buy some other tools that are advanced in order to do those stuff. All right. So this is my indexing strategy that I usually follow in my projects. And, as you can see, each phase builds upon the previous one, moving from a general strategy to more targeted, refined, specific strategy, where we define first the goal of the indexing strategy of the projects, and as we move with the phases, we going to be targeting more specific scenarios. And this cycle keep repeating, it's not only one time. So you have to keep discussing, is the goal still suitable for the projects? You have to keep analyzing the frequently used tables and columns, and keep searching and finding those slow queries, and always keep an eye by monitoring the indexes. And, of course, I can only keep repeating this: Avoid over-indexing. If you liked this video and you want me to create more content like this, I'm going to really appreciate it if you support the channel by subscribing, liking, sharing, commenting. All those stuff going to help the channel with the YouTube algorithm, and as well my content going to reach to the others. So thank you so much for watching, and I will see you in the next tutorial. Bye.

Frequently asked questions

Why does having too many indexes slow down database writes?

Every INSERT, UPDATE, or DELETE requires the database to update, resort, and rebalance all associated index structures.

How can having many indexes cause the database to pick a bad execution plan?

An excessive number of indexes complicates optimizer cost calculations, leading to slower plan generation or selection of suboptimal indexes.

When should I use a filtered index instead of a standard nonclustered index?

Use filtered indexes when queries consistently target a predictable subset of data, such as active records or a specific date window.

Is adding indexes the only way to fix a slow query?

No, query refactoring, schema adjustments, and ETL tuning are also critical performance optimization techniques.

How was this lesson?

Your feedback helps us refine explanations and catch bugs.