SQL Performance Tuning

SQL performance tuning is the process of optimizing queries to make database operations faster and more efficient. It helps reduce response time, lower server load, and improve overall database performance.These are the key elements that influence how fast or slow a SQL query executes.

• Table Size: Large tables take more time to scan and process.
• Joins: Complex joins increase query execution time.
• Aggregations: Functions like SUM, COUNT, and AVG require more processing.
• Concurrency: Many users running queries at the same time slow down the database.

Ways to Find Slow SQL Queries in SQL Server

These are methods used to identify and analyze queries that take a long time to execute.

1. Creating an Execution Plan

SQL Server Management Studio allows users to view the execution plan, which details how SQL Server processes a query. This plan helps identify inefficiencies like missing indexes or unnecessary table scans. To create an execution plan:

• Start by selecting "Database Engine Query" from the toolbar of SQL Server Management Studio.
• Enter the query after that, and then select "Include Actual Execution Plan" from the Query option.
• It's time to run your query at this point. You can do that by pressing F5 or the "Execute" toolbar button.
• The execution plan will then be shown in the results pane, under the "Execution Pane" tab, in SQL Server Management Studio.

2. Monitor Resource Usage

Monitoring CPU, memory, and disk usage helps maintain SQL Server performance and identify possible bottlenecks.

It helps in tasks such as:

• Tracking system resources like CPU, memory, and disk.
• Identifying performance issues using monitoring tools.
• Comparing Windows and SQL Server activity to detect slowdowns.
• Ensuring smooth and efficient database operation.

3. Use SQL DMVs to Find Slow Queries

SQL Server provides DMVs to help monitor and analyze database performance by tracking queries, execution plans, and resource usage.They are used to:

• Find slow-running queries.
• View execution plans.
• Monitor CPU, memory, and I/O usage.

SQL Query Optimization Techniques

Poor or inefficient queries can slow down the database and affect other users, so it is important to optimize queries for better performance.

1. SELECT fields instead of using SELECT *

Using SELECT * retrieves all columns, which can slow down queries.It is better to select only the required columns to improve performance.

Example:

Inefficient:

Select * from GeeksTable;

Efficient:

SELECT FirstName, LastName, 
Address, City, State, Zip FROM GeeksTable;

2. Avoid SELECT DISTINCT

Using SELECT DISTINCT removes duplicates but requires extra processing.It is better to write queries that return unique results naturally for better performance.

Inefficient:

SELECT DISTINCT FirstName, LastName,
State FROM GeeksTable;

Efficient:

SELECT FirstName, LastName,
State FROM GeeksTable WHERE State IS NOT NULL;

3. Use INNER JOIN Instead of WHERE for Joins

Joining tables using the WHERE clause can lead to inefficiencies and unnecessary computations. It's more efficient to use INNER JOIN or LEFT JOIN for combining tables.

Inefficient:

FROM GFG1, GFG2
WHERE GFG1.CustomerID = GFG2.CustomerID

Efficient:

FROM GFG1
INNER JOIN GFG2 ON GFG1.CustomerID = GFG2.CustomerID

4. Use WHERE Instead of HAVING

Use WHERE to filter data before grouping instead of HAVING, as it is faster and more efficient. For example: To find sales per client in 2019, filter the year in WHERE before using GROUP BY.

Inefficient:

SELECT GFG1.CustomerID, GFG1.Name, GFG1.LastSaleDate
FROM GFG1 
INNER JOIN GFG2
ON GFG1.CustomerID = GFG2.CustomerID
GROUP BY GFG1.CustomerID, GFG1.Name, GFG1.LastSaleDate
HAVING GFG2.LastSaleDate BETWEEN '2019-01-01' AND '2019-12-31';

Efficient:

SELECT GFG1.CustomerID, GFG1.Name, GFG1.LastSaleDate
FROM GFG1 
INNER JOIN GFG2
ON GFG1.CustomerID = GFG2.CustomerID
WHERE GFG2.LastSaleDate BETWEEN '2019-01-01' AND '2019-12-31'
GROUP BY GFG1.CustomerID, GFG1.Name, GFG1.LastSaleDate;

6. Run Queries During Off-Peak Hours

Running heavy queries during off-peak hours reduces the load on the database, minimizing the impact on other users. About planning any query to run at a time when it won't be as busy in order to reduce the impact of our analytical queries on the database. When the number of concurrent users is at its lowest, which is often overnight, the query should be executed.

Index Tuning

Index tuning is the process of optimizing indexes to improve query performance. It helps the database retrieve data faster without requiring users to manually adjust indexes.These points help you create efficient and effective indexes for better database performance.

• Use short indexes to save space and speed up searches.
• Choose columns with unique values for better filtering.
• Use clustered indexes on frequently accessed data.
• Avoid using frequently changing columns in clustered indexes.