How to Optimize Queries in SQL Server

Galaxy Glossary

How do I optimize slow SQL Server queries?

Improves query speed by rewriting SQL, adding proper indexes, and analyzing execution plans.

Sign up for the latest in SQL knowledge from the Galaxy Team!
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.

Description

What is query optimization in SQL Server?

SQL Server’s query optimizer chooses the fastest execution plan for a statement. Tweaking indexes, statistics, and query design guides the optimizer toward cheaper plans and shorter runtimes.

How do I view an execution plan?

Run SET SHOWPLAN_XML ON; or click the "Actual Execution Plan" button in SSMS, then execute your query. SQL Server returns XML or graphical output showing operators, costs, and missing-index suggestions.

When should I create an index?

Add a non-clustered index when a column appears in WHERE, JOIN, or ORDER BY clauses and the query filters on fewer than 30–40% of rows. Cover queries by including frequently selected columns in the index.

Example index for an ecommerce filter

CREATE NONCLUSTERED INDEX IX_Orders_CustDate ON Orders(customer_id, order_date) INCLUDE(total_amount);

How do I detect missing indexes?

Use DMVs sys.dm_db_missing_index_details, sys.dm_db_missing_index_groups, and sys.dm_db_missing_index_group_stats. Sort by avg_total_user_cost * avg_user_impact * user_seeks to prioritize high-impact recommendations.

How to rewrite queries for SARGability?

A SARGable predicate lets SQL Server seek rather than scan. Avoid functions on indexed columns, implicit conversions, and "<>" or "OR" across different columns.

Non-SARGable vs. SARGable

-- BadSELECT * FROM Customers WHERE YEAR(created_at)=2023;-- GoodSELECT * FROM Customers WHERE created_at >= '2023-01-01' AND created_at < '2024-01-01';

Best practices checklist

1) Keep statistics current (UPDATE STATISTICS). 2) Remove unused indexes. 3) Use appropriate data types and lengths. 4) Limit result sets with SELECT list and pagination. 5) Measure with SET STATISTICS IO, TIME ON.

Common mistakes

Using SELECT * forces wider I/O and prevents covering indexes. List needed columns instead.

Over-indexing slows writes and bloats storage. Add indexes only for frequent, high-cost queries.

FAQs

Does parameter sniffing hurt performance?

Sometimes. Use OPTION (RECOMPILE) or optimized plan guides when data skew causes bad cached plans.

Should I enable forced parameterization?

For high-throughput OLTP apps, it reduces compilation overhead but may hide sub-optimal plans. Test first.

How often should I update statistics?

Daily for volatile tables or after loading ≥20% new rows; otherwise, auto-update is usually sufficient.

Why How to Optimize Queries in SQL Server is important

How to Optimize Queries in SQL Server Example Usage


-- Find top 5 customers by spend last month, using a covering index
CREATE NONCLUSTERED INDEX IX_Orders_Date_Customer_Total
ON Orders(order_date, customer_id)
INCLUDE(total_amount);

SELECT TOP 5 c.id, c.name, SUM(o.total_amount) AS revenue
FROM Customers c
JOIN Orders o ON o.customer_id = c.id
WHERE o.order_date BETWEEN DATEADD(month,-1,GETDATE()) AND GETDATE()
GROUP BY c.id, c.name
ORDER BY revenue DESC;

How to Optimize Queries in SQL Server Syntax


-- Show estimated plan without running the query
SET SHOWPLAN_ALL ON;
GO
SELECT customer_id, COUNT(*)
FROM Orders
WHERE order_date >= '2024-01-01'
GROUP BY customer_id;
GO
SET SHOWPLAN_ALL OFF;

-- Create a covering index for frequent lookups
CREATE NONCLUSTERED INDEX IX_OrderItems_Order_Product
ON OrderItems(order_id, product_id)
INCLUDE(quantity);

-- Update statistics to help the optimizer
UPDATE STATISTICS Products;

Common Mistakes

Frequently Asked Questions (FAQs)

Can filtered indexes improve performance?

Yes. Filtered indexes cover only rows that meet a predicate, reducing size and improving seek speed for highly selective queries.

Is WITH (NOLOCK) a safe optimization?

It avoids locking but allows dirty reads and phantom rows. Use read-committed snapshot isolation instead for consistent, non-blocking reads.

How do I reduce recompilations?

Parameterize queries, avoid OPTION (RECOMPILE) unless necessary, and keep schema-binding stable to prevent unnecessary recompiles.

Want to learn about other SQL terms?

dbt tests

dbt tests are declarative assertions in dbt that automatically validate data quality during model builds.

dbt utils

dbt utils is a community-maintained dbt package that adds time-saving utility macros for analytics engineers, such as generating surrogate keys, safely unioning models, and automating date spines.

dbt Tutorial Glossary: Build, Test & Deploy SQL Models

dbt is an open-source framework that lets data teams transform raw warehouse tables into tested, documented, production-ready datasets using version-controlled SQL.

Company
BlogFeaturesPricingAboutCareersFeature Requests
Resources
Resource LibraryDocsSupportBrand Kit
Explore
Product TourData Job BoardRecruitersConsultantsStartup ToolsMemes
Learn
Learn SQLBeginner ResourcesData ToolsSQL Interview PrepCommon ErrorsSQL OptimizationSQL In Other LanguagesGlossary
Comparisons
DatagripTablePlusDBeaverBeekeeper StudiopgAdminMySQL WorkbenchSQL Server Management StudioPosticoDbVisualizerNao LabsCursor
HexBrieferPopSQLSherloqBlazeSQLSweep AIWaii AIOuterbase
© 2025 Galaxy Software Inc.
hello@getgalaxy.io
Terms of ServicePrivacy PolicySecurity
Reddit IconProduct Hunt IconTwitter / X IconLinkedIn Icon