已配置各类索引但指定SQL查询仍运行缓慢的技术问询

Got it, let's dig into why this query is running slow and walk through concrete steps to diagnose and fix it. First, let's recap your setup to align:

• Clustered index on id
• Non-clustered indexes on ref_number, entered_by, manpower
• Full-text indexes on user_path and owner_path
• Your slow query filters on incoming_to, incoming_from, uses CONTAINS(user_path, 'Criteria'), and sorts by created_date, entered_by

1. First, Check the Actual Execution Plan #

This is the single most important step. In SQL Server Management Studio (SSMS), hit Ctrl+M to enable "Include Actual Execution Plan," then run your query. Look for these red flags:

• Full Table Scan: If the optimizer is scanning the entire t_document_holder table instead of using indexes, it means none of your existing indexes are helping narrow down rows efficiently.
• Key/RID Lookups: If the query uses an index but has to jump back to the clustered index to fetch the columns you're selecting (like file_name, created_date, etc.), this "bookmark lookup" adds massive overhead for large result sets.
• Explicit Sort Operator: If you see a Sort in the plan, SQL Server is wasting time ordering results after fetching them—we can eliminate this with the right index.

2. Add an Index for Your Filter Predicates #

Your WHERE clause uses incoming_to = 'Criteria' and incoming_from = 'Criteria'—these columns have no indexes at all. If these filters reduce your result set significantly, the optimizer can't efficiently narrow down rows before running the full-text search.

Create a covering non-clustered index that handles both your filters, sorting, and all selected columns to avoid lookups:

CREATE NONCLUSTERED INDEX IX_t_document_holder_Incoming_Filters_Covering
ON t_document_holder (incoming_to, incoming_from)
INCLUDE (created_date, entered_by, file_name, ref_number, manpower, user_path, owner_path);

This index lets the optimizer quickly find rows matching your incoming_to/incoming_from criteria, retrieve all needed columns directly from the index, and even avoid a full sort operation if the data aligns with your ORDER BY needs.

3. Validate Your Full-Text Index Health #

Even though you have a full-text index on user_path, it might be stale or misconfigured:

• Refresh the full-text index: If your table gets frequent writes, the full-text index might not be up-to-date. Refresh it manually with:

  ALTER FULLTEXT INDEX ON t_document_holder START FULL POPULATION;
  

• Refine your CONTAINS predicate: If 'Criteria' is a broad term returning tons of rows, try narrowing it down (e.g., use prefix matching like "Criteria*" if that makes sense for your data). Overly broad full-text searches can kill performance even with indexes.

4. Optimize the ORDER BY #

Your query sorts by created_date, entered_by. If your result set is large, sorting is expensive. The covering index I suggested in step 2 includes these columns, which lets the optimizer retrieve rows in the exact order you need without a separate Sort operation.

If you don't want a wide covering index, you could create an index on (created_date, entered_by) that includes your filter columns and user_path, but the covering index is more efficient for this specific query.

5. Update Table Statistics #

Outdated statistics can trick the query optimizer into making bad decisions (like choosing a full scan over an index). Update stats for your table with:

UPDATE STATISTICS t_document_holder WITH FULLSCAN;

This gives the optimizer accurate data about how rows are distributed in your table, so it can pick the best index strategy.

6. Check Result Set Size #

If your query returns thousands or millions of rows, even perfect indexes won't make it fast—transferring and rendering that much data takes time. If you don't need all results at once, add pagination:

SELECT id, file_name As 'Document', ref_number AS 'Ref Number', entered_by As 'Entered By', created_date As 'Created Date', manpower, user_path, owner_path 
FROM t_document_holder 
WHERE 1 = 1 AND incoming_to = 'Criteria' AND incoming_from = 'Criteria' AND CONTAINS ( user_path, 'Criteria' ) 
ORDER BY created_date, entered_by
OFFSET 0 ROWS FETCH NEXT 50 ROWS ONLY;

Or refine your filters to reduce the number of rows returned.

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