Speeding up a SQL Server query is an evidence-based process. An index hint or server upgrade should not be the first response to an unexplained delay.
Measure the query
Run in a controlled environment and record elapsed time, CPU time, logical reads, row counts, and the actual plan. Compare the same inputs and concurrency conditions before and after changes.
Check the shape of the SQL
Avoid SELECT * on large tables; verify join conditions and filtering; use appropriate data types and parameters; and watch for implicit conversions or functions applied to searchable columns.
Investigate access paths
Index keys, included columns, selectivity, statistics, and predicates must work together. An index that accelerates SELECT may increase INSERT and UPDATE costs.
What tools contribute
Query Store records query performance over time; dynamic management views expose current and accumulated engine state; Extended Events captures selected runtime events; Performance Monitor provides host context.
Example diagnostic setting
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT ProductID, ProductName
FROM dbo.Products
WHERE CategoryID = 2;
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;Hints are last resorts
A plan hint can restrict the optimizer and become counterproductive after data or version changes. Test and document any hint, with a clear removal path.
AI, sources and reuse: This is an AI-generated rewrite informed by the original Open Education post (#69270), with additional explanations and examples. The new article text may be copied, adapted, and republished with or without a reference to this site. Third-party resources and trademarks keep their own rights. AI can make mistakes. Verify version support, code correctness, and security practices against Microsoft documentation, and test with nonproduction data first.
