General Steps for SQL Server Performance Tuning

1. What is Performance Tuning?

It is the process of optimizing SQL queries and the database structure to enhance
speed, efficiency, and the overall responsiveness of the database system.

2. Main Areas of Performance Tuning:

2.1 Index Optimization

• Difference between Clustered and Non-Clustered Indexes.

Practical Example:

-- Clustered Index

CREATE CLUSTERED INDEX IX_Students_ID

ON Students(StudentID);

-- Non-Clustered Index

CREATE NONCLUSTERED INDEX IX_Students_Name

ON Students(FirstName);

• Covering Indexes.
A covering index is an index that contains all the columns needed by the
query, so SQL Server doesn’t need to go back to the base table to fetch any
additional columns.

Practical Example:

CREATE NONCLUSTERED INDEX IX_Orders_Covering

ON Orders(CustomerID, OrderDate)

INCLUDE (TotalAmount);

▪ CustomerID and OrderDate are the indexed columns.

▪ TotalAmount is included in the index using INCLUDE to cover the query.
o Benefit: The query becomes faster because it finds all required data
within the index itself.

• Filtered Indexes.
 A filtered index is an index created on a subset of data based on a specific
condition.
It is useful when you frequently query a specific portion of the data (e.g., status
= Active).

Practical Example:
CREATE NONCLUSTERED INDEX IX_Active_Students
ON Students(Status)
WHERE Status = 'Active';
This index covers only the active students, therefore:
• The index size is smaller.
• Performance is faster for queries targeting Status = 'Active'.

• Missing Indexes.
These are indexes that SQL Server suggests when it notices that queries are
running slowly and resorting to scanning the entire table.
You can find them through the Execution Plan.

How to identify missing indexes:

1. Run the query in SQL Server Management Studio (SSMS).
2. Enable Include Actual Execution Plan.
3. After execution, if a missing index is detected, you’ll see a suggestion
in the plan.

Example of a query where SQL Server might suggest an index:

SELECT *
FROM Students
If there’s no index on LastName, SQL Server will suggest creating one to
speed up the search.
2.2 Query Optimization
• Avoid using SELECT *.
• Use EXISTS instead of IN where applicable.
• Review WHERE and JOIN conditions: are they all necessary?
• Use SET NOCOUNT ON to reduce network traffic.

1. Avoid using SELECT *

• Don’t use SELECT * because it fetches all columns even if you don’t need
them.
• It’s better to specify only the needed columns to reduce data size and
improve speed.

Example:
Wrong:
SELECT * FROM Students;

Correct:
SELECT StudentID, FirstName, LastName FROM Students;

2. Use EXISTS instead of IN (when applicable)

• EXISTS is faster than IN when checking for the existence of a record because
it stops at the first match.
• IN checks all values even if a match is found early.

Example:
-- Faster
SELECT * FROM Students s
WHERE EXISTS (
SELECT 1 FROM Enrollments e
WHERE e.StudentID = s.StudentID
);

-- Slower
SELECT * FROM Students
WHERE StudentID IN (
SELECT StudentID FROM Enrollments
);
3. Review WHERE and JOIN conditions
• Make sure all conditions in WHERE and JOIN are truly necessary.
• Unnecessary conditions can slow down the query or change its results.

Example:
-- Don’t add unnecessary condition
SELECT *
FROM Students s
JOIN Enrollments e ON s.StudentID = e.StudentID
WHERE s.Status = 'Active';
If the condition on the column is not important, it’s better to remove it.

4. Use SET NOCOUNT ON

• When a query runs, SQL Server sends a message about the number of rows
affected.
• SET NOCOUNT ON prevents sending this message, reducing network traffic
and improving performance in large operations.

Example:
SET NOCOUNT ON;
SELECT * FROM Students;
SET NOCOUNT OFF;
1. How SQL Server Executes a Query
When you run a query, SQL Server creates a plan that shows how it will access the
data. This plan includes details such as:
• Which tables are read
• Whether it uses an index or scans the whole table
• How joins are done
• Sorting or filtering steps
This plan helps you understand the query’s performance.

2. Table Scan vs. Index Seek

• Table Scan:
SQL Server reads every row in the entire table because it cannot use an index
efficiently.
This can be very slow on large tables.
• Index Seek:
SQL Server uses an index to jump directly to the rows that satisfy the query
condition.
This is much faster and preferred.

3. Examples
Assume you have a table Students with columns StudentID, FirstName, and
LastName.
Example of Table Scan (no index):
SELECT * FROM Students WHERE LastName = 'Smith';
• If there is no index on LastName, SQL Server will do a Table Scan — reading
every row to find matches.
Creating an index to enable Index Seek:
CREATE NONCLUSTERED INDEX IX_Students_LastName ON Students(LastName);
Example of Index Seek (with index):
SELECT * FROM Students WHERE LastName = 'Smith';
• Now SQL Server uses Index Seek, quickly locating only the rows where
LastName = 'Smith'.
4. Estimated vs. Actual Execution Plans
• Estimated Execution Plan:
Shows SQL Server’s prediction of how it will execute the query before
running it.
Useful to check performance without running the query.
• Actual Execution Plan:
Shows exactly what SQL Server did after running the query, including actual
row counts and timing.
Useful to debug and optimize real query behavior.

2.4 Statistics
• What are Statistics?
Statistics are data about the distribution of values in your database tables and
indexes. They help SQL Server estimate how many rows will match a query
condition.
• Why are they important?
SQL Server uses statistics to decide the most efficient execution plan (for
example, whether to use an index or do a table scan).
• Updating Statistics
Statistics should be kept up-to-date, especially after large inserts, updates, or
deletes, so SQL Server has accurate information.

Example:
If SQL Server knows that only a few rows match a condition, it may choose to use an
index (Index Seek). But if statistics say most rows match, it might do a Table Scan
instead.

How to update statistics manually:

UPDATE STATISTICS TableName;
Or update statistics for all tables in the database:
EXEC sp_updatestats;

T.Al-Anowd Fawaz