非聚集索引扫描问题：无法对ipReq执行Table Seek致性能下降

Hey there, let's break down why your non-clustered index on ipReq isn't being used for a Table Seek—this is a super common issue, and I've worked through it plenty of times. Here are the most likely reasons and how to check each one:

Common Causes & Fixes #

1. Your Query Needs Columns Not Covered by the Index #

Non-clustered indexes only contain the key columns (and any included columns you specify). If your query pulls back columns that aren't in the index, SQL Server might choose a Table Scan instead of a Seek + Key Lookup—especially if the lookup would require fetching a large portion of the table.

For example, if your query is:

SELECT * FROM YourTable WHERE ipReq = '192.168.1.1'

And your index only includes ipReq, the optimizer has to jump back to the clustered index (or heap) to get the other columns. If enough rows match the ipReq filter, scanning the whole table becomes cheaper.

Fix: Either add the needed columns to the index as included columns:

CREATE NONCLUSTERED INDEX IX_YourTable_ipReq ON YourTable(ipReq) INCLUDE (Column1, Column2);

Or rewrite your query to only select columns present in the index.

2. Outdated Statistics Are Misleading the Optimizer #

SQL Server relies on up-to-date statistics to estimate how many rows will match your ipReq filter. If stats are old, the optimizer might miscalculate the cost of a Seek vs. Scan. For example, if ipReq has a lot of duplicate values (like 40% of the table shares the same IP), the optimizer will skip the index because scanning is more efficient.

Fix: Update the statistics for your table:

UPDATE STATISTICS YourTable WITH FULLSCAN;

Then re-run your query and check the execution plan again.

3. The ipReq Column Has Low Selectivity #

Selectivity refers to how unique the values in ipReq are. If most rows have the same (or similar) ipReq values, the index's selectivity is low. The optimizer will decide that a Table Scan is faster than using the index, since it would have to retrieve so many rows anyway.

Check: Run this query to see the selectivity of ipReq:

SELECT 
    COUNT(DISTINCT ipReq) AS UniqueIPs,
    COUNT(*) AS TotalRows,
    CAST(COUNT(DISTINCT ipReq) AS FLOAT)/COUNT(*) AS SelectivityRatio
FROM YourTable;

If the ratio is very low (e.g., < 0.1), a single-column index on ipReq might not be useful. Consider combining it with another high-selectivity column to make a composite index.

4. Your Query Is Breaking Index Usage with Functions/Implicit Conversions #

If you're wrapping ipReq in a function (like CAST, CONVERT, or string functions) in your WHERE clause, the index can't be used because the function alters the column's value. Similarly, if the data type of your filter parameter doesn't match ipReq (e.g., ipReq is VARCHAR but you're passing an NVARCHAR parameter), an implicit conversion happens, which also invalidates the index.

Bad Example:

SELECT * FROM YourTable WHERE CAST(ipReq AS NVARCHAR(50)) = '192.168.1.1'

Fix: Rewrite the query to use the column directly without functions, and ensure parameter data types match the column's data type.

5. Index Fragmentation Is Too High #

Over time, non-clustered indexes can become fragmented—this means the index pages are out of order, making a Table Seek slower. If fragmentation is severe (usually >30%), the optimizer might opt for a scan instead.

Check: Run this to see fragmentation levels:

SELECT 
    name AS IndexName,
    avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(
    DB_ID(),
    OBJECT_ID('YourTable'),
    NULL,
    NULL,
    'DETAILED'
)
WHERE index_id > 0; -- Exclude heap

Fix: Rebuild or reorganize the index:

-- For high fragmentation (>30%)
ALTER INDEX IX_YourTable_ipReq ON YourTable REBUILD;

-- For low-to-moderate fragmentation (5-30%)
ALTER INDEX IX_YourTable_ipReq ON YourTable REORGANIZE;

Next Steps to Diagnose Further #

If none of the above fixes work, sharing these details will help narrow it down:

• The exact query you're running
• The full CREATE INDEX statement for your non-clustered index
• A screenshot or XML of the actual execution plan
• The approximate number of rows in your table, and how many rows typically match your ipReq filter

内容的提问来源于stack exchange，提问作者Dhruv