Decide, create, and verify an index

An index suggestion becomes a decision only after it is compared with existing structures, tested with the same query, and documented with both its benefit and its added cost. In this test, the candidate reduces reads for one specific query; the result preserves it as a useful hypothesis, not as approval for every workload.

Review what already exists

Before creating a structure, retrieve the key columns, included columns, and space used by each index on the table:

SELECT
    i.name AS IndexName,
    i.type_desc AS IndexType,
    ic.key_ordinal AS KeyOrder,
    ic.is_included_column AS IsIncluded,
    c.name AS ColumnName,
    sz.UsedPages,
    CONVERT(DECIMAL(12, 2), sz.UsedPages * 8.0 / 1024.0) AS SizeMB
FROM sys.indexes AS i
INNER JOIN sys.index_columns AS ic
    ON ic.object_id = i.object_id
   AND ic.index_id = i.index_id
INNER JOIN sys.columns AS c
    ON c.object_id = ic.object_id
   AND c.column_id = ic.column_id
CROSS APPLY
(
    SELECT SUM(ps.used_page_count) AS UsedPages
    FROM sys.dm_db_partition_stats AS ps
    WHERE ps.object_id = i.object_id
      AND ps.index_id = i.index_id
) AS sz
WHERE i.object_id = OBJECT_ID(N'dbo.PedidoLaboratorio')
  AND i.index_id > 0
ORDER BY
    i.name,
    ic.is_included_column,
    ic.key_ordinal,
    ic.index_column_id;

1. 1
   Current key

   FechaPedido is the first key column of the nonclustered index.

2. 2
   Included data

   IdCliente, Estado, and Total are stored at the leaf level, but they do not determine how the index is traversed.

The existing index contains the three columns required by the query, but its key begins with FechaPedido. Because the query does not filter by date, having Estado and Total as included columns does not provide direct navigation to the requested range.

This is an overlap in coverage, not an overlap in navigation. Two indexes overlap in coverage when they store similar data; they overlap in navigation when their leading keys support similar searches. The distinction prevents two structures from being treated as equivalent merely because they repeat column names.

Preserve a baseline before the change

The comparison uses exactly the same query and values before and after:

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

SELECT TOP (10)
    p.IdCliente,
    COUNT_BIG(*) AS Orders,
    SUM(p.Total) AS Amount
FROM dbo.PedidoLaboratorio AS p
WHERE p.Estado = 'Enviado'
  AND p.Total BETWEEN 1200.00 AND 1210.00
GROUP BY p.IdCliente
ORDER BY Amount DESC;

SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

Enable Include Actual Execution Plan with Ctrl+M, run the query, and preserve both the Execution plan and Messages tabs.

1. 1
   Automatic hypothesis

   The green line again proposes a key on Estado and Total.

2. 2
   Executed access

   The available plan traverses the current index with an Index Scan.

The baseline records 482 logical reads on PedidoLaboratorio. A logical read is one 8 KB page requested from the data cache; it is not one row and does not by itself indicate a physical disk read.

Create a deliberate candidate

Run the statement only where structures can be created and removed safely for a test:

CREATE NONCLUSTERED INDEX IX_PedidoLaboratorio_Estado_Total
ON dbo.PedidoLaboratorio (Estado, Total)
INCLUDE (IdCliente);

The key places the equality on Estado first and the range on Total second. IdCliente remains an included column because the query needs it to group and return the result, but not to locate the range.

After creating the candidate, retrieve its definition and size:

SELECT
    i.name AS IndexName,
    ic.key_ordinal AS KeyOrder,
    ic.is_included_column AS IsIncluded,
    c.name AS ColumnName,
    sz.UsedPages,
    CONVERT(DECIMAL(12, 2), sz.UsedPages * 8.0 / 1024.0) AS SizeMB
FROM sys.indexes AS i
INNER JOIN sys.index_columns AS ic
    ON ic.object_id = i.object_id
   AND ic.index_id = i.index_id
INNER JOIN sys.columns AS c
    ON c.object_id = ic.object_id
   AND c.column_id = ic.column_id
CROSS APPLY
(
    SELECT SUM(ps.used_page_count) AS UsedPages
    FROM sys.dm_db_partition_stats AS ps
    WHERE ps.object_id = i.object_id
      AND ps.index_id = i.index_id
) AS sz
WHERE i.object_id = OBJECT_ID(N'dbo.PedidoLaboratorio')
  AND i.name = N'IX_PedidoLaboratorio_Estado_Total'
ORDER BY
    ic.is_included_column,
    ic.key_ordinal,
    ic.index_column_id;

1. 1
   Traversable key

   Estado has key order 1 and Total has key order 2.

2. 2
   Space cost

   The test structure uses 447 pages, about 3.49 MB, and includes IdCliente.

Size is not decorative information. Every additional index requires storage, memory when its pages enter cache, and maintenance when a write affects its columns.

Repeat the query without changing its conditions

Run the same query again with SET STATISTICS IO, SET STATISTICS TIME, and the actual plan enabled. Do not change the values, projection, grouping, or ordering between measurements.

1. 1
   New access

   The plan uses an Index Seek on the candidate to locate the status and range of totals.

The rest of the tree does not disappear: the query still groups, sums, and sorts. The change addresses row access; it does not replace the work needed to produce the final result.

Compare reads and preserve the result

1. 1
   Before

   The query records 482 logical reads on the table.

2. 2
   After

   The same query records 3 logical reads while the candidate is available.

Both executions return the same clients, counts, and amounts. That equality is part of the test: an alternative that changes the result is not an equivalent optimization.

The reduction in reads demonstrates a benefit for this query and these values. Timings from a single execution are not converted into an improvement percentage: cache state, compilation, concurrent activity, and clock resolution can change them.

Observe use and maintenance during the test

sys.dm_db_index_usage_stats separates seeks, scans, lookups, and maintenance operations observed since its counters were last reset:

SELECT
    i.name AS IndexName,
    COALESCE(ius.user_seeks, 0) AS Seeks,
    COALESCE(ius.user_scans, 0) AS Scans,
    COALESCE(ius.user_lookups, 0) AS Lookups,
    COALESCE(ius.user_updates, 0) AS MaintenanceOperations,
    ius.last_user_seek AS LastSeek
FROM sys.indexes AS i
LEFT JOIN sys.dm_db_index_usage_stats AS ius
    ON ius.database_id = DB_ID()
   AND ius.object_id = i.object_id
   AND ius.index_id = i.index_id
WHERE i.object_id = OBJECT_ID(N'dbo.PedidoLaboratorio')
  AND i.name = N'IX_PedidoLaboratorio_Estado_Total';

1. 1
   Short window

   The candidate records one seek and no maintenance operation during this test.

user_updates counts operations that required index maintenance; it does not count modified rows. A zero here only means no writes occurred during the candidate’s short lifetime. It does not prove the index is free for a workload with INSERT, UPDATE, or DELETE.

Decide whether to reject, combine, or test

• Reject: when the query is not relevant, the measured benefit does not appear, added storage or write cost is not justified, or the suggestion depends on an exceptional observation.
• Combine: when an existing index can support several queries through a tested adjustment instead of adding near-duplicates. Changing an existing index still requires checking all of its consumers.
• Test: when there is a concrete hypothesis, a repeatable baseline, a representative query, a safe environment, and a defined restoration step.

In this test, the candidate deserves further investigation because it reduces reads and preserves the result. Different parameters, writes, other queries, storage at scale, blocking, and plan stability remain to be evaluated before retaining it for an actual workload.

Restore the initial state

The test ends by removing the temporary structure:

DROP INDEX IX_PedidoLaboratorio_Estado_Total
ON dbo.PedidoLaboratorio;

SELECT
    CASE WHEN EXISTS
    (
        SELECT 1
        FROM sys.indexes
        WHERE object_id = OBJECT_ID(N'dbo.PedidoLaboratorio')
          AND name = N'IX_PedidoLaboratorio_Estado_Total'
    ) THEN 1 ELSE 0 END AS CandidateExists,
    COUNT_BIG(*) AS OrderRows
FROM dbo.PedidoLaboratorio;

1. 1
   Restored state

   CandidatoExiste = 0 confirms removal and FilasPedido = 100000 preserves the table total shown in the evidence.

Restoration is also part of the evidence. Creating an index for observation and leaving it active by default would turn a test into an unevaluated operational change.

Next reasonable check

The next entry starts from a symptom and organizes the complete path: evidence, plan, signal, possible explanation, and controlled test. The goal is to state a testable hypothesis before choosing a correction.

Related topics

• Understand the CREATE INDEX generated by SSMS: separate keys, included columns, and pending decisions.
• Read a Missing Index suggestion in a plan: identify where the proposal comes from.
• Reads with SET STATISTICS IO: interpret pages requested during execution.
• Review index usage: compare accumulated reads and maintenance.
• Review missing-index suggestions: inspect observations accumulated by the DMVs.