SQL Server Query Optimization

Meena
MCA, AKTU, Lucknow
Topic to be Covered

1. Why Query optimization Required ? 10. Statistics and Their Role

2. Impact of Query Optimization 11. Best Practices in Query Writing

3. SQL Server Query Lifecycle 12. Best Practices for Maintenance

4. Execution Plan

5. Common cause of slow queries in SQL Server

6. Tools to Identify the slow queries in SQL Server

7. Index and its Optimization in SQL Server

8. Index seek Vs Index Scan in SQL Server

9. Parameter Sniffing Issue

Why Query optimization Required ?

Query Optimization in databases is the process of choosing the most efficient way to execute a SQL query so that it runs
faster and uses fewer resources (CPU, memory, disk I/O).
Why Query Optimization Matters?
[Link]
1. Faster Processing time: Faster query execution consequently Processing
reduces page load time in applications.

2. Resource Efficiency : Faster query execution enhance the resource efficiency

5.
• As Optimized queries use less CPU, memory, and I/O. Maintainability
[Link]
Efficiency
• It also prevents blocking and deadlocks in multi-user environments.
& Reliability
Why
Optimization
3. Improved Scalability: A well-optimized query can handle thousands of Required ?
users without hardware upgrades.
• It also ensures smooth performance as data grows
[Link] [Link]
4. Hardware Cost Savings: Faster queries avoid expensive hardware upgrades. Cost Saving Scalability

5. Maintainability & Reliability : It enhance debug, monitor, and maintain applications

Impact of Query Optimization

1. Performance : Optimized queries Increases performance

• As it run faster (seconds vs. minutes).
• It reduces CPU, memory, and I/O usage.
•It supports higher concurrency (more users at the same time).

2. Cost : Optimized queries Reduces the Cost

• It avoid frequent hardware upgrades.
• Efficient queries lowers operational costs.

3. User Experience : Optimized queries Improves User Satisfaction

and trust
•Due to faster application response times.
•Due to Less chance of timeouts, errors, or hangs.
SQL Server Query Life Cycle
When we run a SQL query, it goes through following stages inside SQL Server:
1. Parsing
•SQL statement is checked for syntax errors. • Parsing
1
•Query is broken into tokens (keywords, identifiers, operators).
2. Binding (Algebrizer)
•Checks semantic correctness: • Binding
•Are tables/columns valid? 2
•Are data types compatible?
3. Optimization (Query Optimizer)
• Optimization
•The most important step. 3
•Generates multiple possible execution plans.
•Chooses the lowest-cost plan based on statistics, indexes, and available paths.
4. Execution Plan Caching • Execution Plan Caching
4
•The chosen execution plan may be stored in the Plan Cache for reuse.
•Improves performance for repeated queries.
5. Execution • Execution
5
•The chosen plan is executed by the SQL Server Engine.
•Operators like Index Seek, Nested Loop Join, etc. are performed.
•Data is fetched from Buffer Pool (memory) or disk (I/O). • Results Returned
6. Results Returned 6
•Processed data is sent back to the client/application
Execution Plan

What is an Execution Plan?

Execution Plan is a roadmap showing how SQL Server will execute (or executed) a query.
Types of Execution Plans
1. Estimated Execution Plan
•Generated before query execution.
•Based on statistics and indexes.
•Shows the expected operators, costs, and row estimates.
•Useful for analyzing query performance without running it.

2. Actual Execution Plan

•Generated after query execution.
•Contains everything in the estimated plan plus runtime info:
•Actual number of rows processed.
•Actual execution warnings.
•Helps identify misestimations (bad statistics, parameter sniffing).

Execution Plan
Execution Plan Contd..
Estimated Query Execution Plan of Bad Query (Without Index) Query Execution Plan of Good/Optimized Query
Create NonClustered Index IX_Person_MiddleName on [Link] (MiddleName)
include (BusinessEntityID,PersonType,FirstName,LastName)
Select * from [Link] where MiddleName=‘L’
Select BusinessEntityID,PersonType,FirstName,MiddleName,LastName from [Link]
where MiddleName=‘L’;
Execution Plan Contd..

Important Parameters of Estimated Execution Plan

1. Estimated Subtree Cost : it Represents SQL Server’s internal estimate of how expensive
the query operation is.
•Higher values → more expensive operations.
•Useful to compare between different plans.

2. Estimated I/O Cost : it is the Cost SQL Server expects for disk I/O (reading/writing pages)
for that operation.
•Higher I/O cost usually means scans instead of seeks.

3. Estimated CPU Cost : It is the Cost SQL Server expects for CPU processing of that operator.
•Useful to identify CPU-heavy operations (e.g., complex sorts, hash joins)

4. Estimated Data Size: It is approximate row size in bytes processed by that operator.
•Helps you understand memory usage.
Common Causes of Slow Queries in SQL Server

1. Missing or Improper Indexes on tables

•Queries doing Table Scans instead of Index Seeks.
•No covering index for frequently accessed columns.
•Fragmented or unused indexes.
2. Outdated or Missing Statistics
•SQL Server relies on statistics for row estimates.
•Wrong estimates → bad execution plan
(e.g., Hash Join instead of Nested Loop).
3. Non-SARGable Queries
•Conditions that prevent index usage, like:
WHERE YEAR(OrderDate) = 2025 Functions,
wildcards at the start (LIKE '%abc'), or implicit conversions.
4. Too Many Joins or Complex Query Design
•Excessive joins or deeply nested subqueries.
•Using cursors instead of set-based operations.
5. Parameter Sniffing : The Query plan generated for one
parameter may not work well for others.
•Causes inconsistent performance.
Common Causes of Slow Queries in SQL Server contd..

6. Large Data Volume without Filtering

•Using SELECT * instead of selecting only required columns.
•No WHERE clause → pulls unnecessary rows.

7. Blocking and Deadlocks

•Queries waiting for locks due to other long-running transactions.
•Poor transaction handling.

8. Inefficient Joins and Data Types

•Mismatched data types (implicit conversions).
•Wrong join type (e.g., Cartesian product).

9. TempDB Contention
• Heavy use of temp tables, table variables.

10. Poor Query Plan Reuse / Cache Issues

•Recompilations or too many ad-hoc queries filling plan cache.
Tools to Identify Slow Queries in SQL Server

1. SQL Server Profiler is a Real-time monitoring tool (GUI).

• It Captures query execution, duration, CPU, reads/writes.
• It is Good for quick troubleshooting.
• it can cause overhead in production (use carefully).
2. Extended Events (XEvents) it is a Lightweight replacement for Profiler.
• It is Flexible and less resource-intensive.
• It can track Long-running queries, Deadlocks, Wait statistics
3. Query Store (SQL Server 2016+) : it Stores historical query performance data.
•It helps to find:
• Queries with regressed plans (suddenly slower).
• Top resource-consuming queries.
Tools to Identify Slow Queries in SQL Server contd..

4. Dynamic Management Views (DMVs) are System views that expose performance data. SQL Server Provides 7 type of
DMV
1. System Information
2. Session & Connection Monitoring
3. Performance & Waits
4. Execution & Plan Caching
5. Database & Transaction
6. Indexes & Missing Indexes
7. Memory & Buffer
Examples of user of DMV: Find top 10 resource-consuming queries

SELECT TOP 10
qs.total_elapsed_time/qs.execution_count AS AvgTime,
qs.execution_count, qs.total_logical_reads,
qs.total_worker_time, [Link]
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
ORDER BY AvgTime DESC;
Index and its Optimization in SQL Server
Index is a database object that improves the speed of data retrieval operations on a table or view by providing a quick lookup path.
•Indexes is a Roadmaps for SQL Server to find data quickly.
•Without indexes there will Table Scan (checks every row).
•With proper indexes there will be Index Seek (fast, targeted lookup).
Clustered
There are 4 important Types of Indexes
1. Clustered Index
•It defines the physical order of data in a table.
•It can be created one per table.
•It is best for columns used in range queries (e.g., OrderDate).
2. Non-Clustered Index Column
Type of Non-Cluster
•It is a separate structure with pointers to the data. Store
•It is good for search/filter columns.
3. Covering Index
Index
It Includes additional columns (via INCLUDE) so SQL Server
doesn’t need a lookup. Example:

CREATE NONCLUSTERED INDEX IX_Sales_Customer Covering

ON Sales(CustomerID) INCLUDE (OrderDate, TotalAmount);
4. Column store Index
It Stores data column-wise (not row-wise).
It is used for Optimizing analytics and aggregation queries.
Index Seek vs. Index Scan vs. Table Scan
1. Index Seek (Best Performance )
•SQL Server navigates directly to the matching rows using the index.
•It is very fast → like using the index of a book to find a topic.
•It is used when query predicate is SARGable (search argument optimized).
Example:
SELECT * FROM [Link] WHERE BusinessEntityID = 100;
Uses Index Seek on the clustered index (PK_Person_BusinessEntityID).
2. Index Scan (Medium Performance )
SQL Server reads all rows in the index to find matches.
It is faster than Table Scan, but not as good as Index Seek.
Happens when: Predicate is not selective enough (too many rows match).
Example:
SELECT * FROM [Link] WHERE MiddleName LIKE 'A%';
Uses Index Scan (if index on MiddleName exists, but many rows match).

3. Table Scan (Worst Performance)

•SQL Server reads every row in the table.
•Very costly on large tables.
•Happens when: No index on the filter column and Query uses non-SARGable expressions.
Example:
SELECT * FROM [Link] WHERE YEAR(ModifiedDate) = 2014;
Table Scan because YEAR() function makes the predicate non-SARGable.
Parameter sniffing issues

Parameter Sniffing :
When a stored procedure runs for the first time, SQL Server "sniffs" (detects) the parameter values passed.
It then generates an execution plan optimized for those specific parameter values and caches it.
Future executions reuse this same cached plan, even if later parameters are very different.
This can lead to bad performance when parameter values vary a lot (skewed data distribution).

Example
Stored Procedure
CREATE PROCEDURE GetOrdersByCustomer
@CustomerID INT
AS
BEGIN
SELECT * FROM Orders WHERE CustomerID = @CustomerID;
END

Case 1: First execution with @CustomerID = 1 (small customer, few rows)

•SQL Server builds an execution plan optimized for few rows (nested loop join) and Plan cached.
Parameter sniffing issues contd..

Case 2: Next execution with @CustomerID = 500 (large customer, millions of rows)
SQL Server reuses the same plan even though a hash join or index scan would be faster, in this case Performance drops
dramatically.

How to Fix this issue

1. Use OPTION (RECOMPILE): it Forces SQL Server to build a fresh plan every time.

SELECT *FROM Orders WHERE CustomerID = @CustomerID OPTION (RECOMPILE);

Statistics and Their Role

Statistics in SQL Server are metadata objects that describe the distribution of values in a table’s column(s).
They are used by the Query Optimizer to estimate the number of rows that will match a query condition.
Each statistic contains:
Histogram → shows data distribution across column values.
Density information → helps predict selectivity for joins and filters.

Role of Statistics in Query Optimization

1. Statistics used for Cardinality Estimation

•The optimizer uses statistics to estimate how many rows a query will return.
•Example: If only 1% of rows match a condition, the optimizer may choose an Index Seek instead of a Table Scan.

2. Statistics is used for Execution Plan Selection

•Based on statistics, SQL Server decides join strategies (Nested Loop, Merge Join, Hash Join) and index usage.
•Accurate statistics = accurate execution plan = better performance.

3. Statistics Impact directly on Performance

•Up-to-date statistics ensure SQL Server avoids costly scans.
•Outdated statistics can lead to poor choices, like full table scans or wrong join types.
Statistics and Their Role contd..

Example
SELECT * FROM Orders WHERE OrderDate >= '2025-01-01';

•If statistics show only 5% of rows are recent, SQL Server may use an Index Seek.
• If statistics are outdated and SQL Server assumes 90% of rows qualify, it may incorrectly choose a scan, slowing the
overall performance.

How to Fix this issue for single table

UPDATE STATISTICS Orders WITH FULLSCAN;

1. Enable at the Database Level
By default, most databases have Auto Update Statistics ON, but you can explicitly enable it:

-- Enable Auto Update Statistics for a specific database

ALTER DATABASE YourDatabaseName SET AUTO_UPDATE_STATISTICS ON;
Best Practices for Query Writing

a) Avoid SELECT * (bad Query: select * from Employees;)

Good Query: SELECT EmployeeID, FirstName, LastName, HireDate FROM Employees;
As SQL Server has to fetch all columns, even if many are not required. This increases I/O, memory usage, and network
traffic.
b) Use Alias Columns for Clarity
SELECT [Link], [Link], [Link] FROM Employees e;

c) Use TOP with ORDER BY for Limited Rows

SELECT TOP 10 EmployeeID, FirstName, LastName FROM Employees ORDER BY HireDate DESC;

e) Use Derived Tables or CTEs (Common Table Expression), which is a temporary result set that we can reference within a
SELECT, INSERT, UPDATE, or DELETE statement

WITH RecentHires AS
(
SELECT EmployeeID, FirstName, HireDate FROM Employees
WHERE HireDate >= '2025-01-01‘
)
SELECT EmployeeID, FirstName FROM RecentHires;
Best Practices for Query Writing contd..

b) Ensure SARGability (Search Argument)

A SARGable query is one where SQL Server can use an index seek instead of scanning the whole table.

Non-SARGable (functions on column)

SELECT * FROM Employees WHERE YEAR(HireDate) = 2025;

-- SQL Server must compute YEAR(HireDate) for every row → can't use index

SARGable
SELECT * FROM Employees WHERE HireDate >= '2025-01-01' AND HireDate < '2026-01-01';

Example-1 : Non-SARGable (function on indexed column)

SELECT * FROM Customers WHERE LEFT(Phone, 3) = '415';

SARGable
SELECT * FROM Customers WHERE Phone LIKE '415%';
Best Practices for Query Writing contd..

Example-2: Non-SARGable (NOT, <> on indexed column)

SELECT * FROM Employees WHERE Salary <> 50000;

SARGable (if possible, use ranges)

SELECT * FROM Employees WHERE Salary < 50000 OR Salary > 50000;

SARGability Best Practices

1. Avoid wrapping indexed columns with functions (YEAR(), CAST(), UPPER(), etc.).
2. Rewrite filters into range conditions.
3. Use search-friendly operators: =, <, >, BETWEEN, IN, LIKE 'abc%'.
4. Avoid NOT, <>, NOT IN, and leading wildcards (LIKE '%abc').
5. Ensure data types match in predicates (avoid implicit conversions).
6. Avoid function in where clause
Best Practices for Maintenance

1. Reorganize Index Quick Guide

Use when fragmentation is 5% – 30%. Fragmentation % Action
Defragments at the page level (lightweight). < 5% Do nothing
5–30% Reorganize Index
example: ALTER INDEX IX_Sales_Customer ON [Link] REORGANIZE; >30% Rebuild Index

2. Rebuild Index
Use when fragmentation is >30%.
Drops and recreates the index (more costly).
example: ALTER INDEX IX_Sales_Customer ON [Link] REBUILD;
3. Update Statistics
Keeps row distribution info fresh for optimizer.
example: UPDATE STATISTICS [Link] IX_Sales_Customer;

4. Maintenance Strategy
Run Index Reorganize/Rebuild jobs during off-peak hours.
Use SQL Server Agent jobs to automate maintenance.
Avoid over-maintenance → frequent rebuilds can waste resources.