Sometimes you might have faced the issues of slow SQL performance due to some queries. This slowdown might have also impacted your operations to a major extent. And every time you might have felt the need to improve the performance of your SQL database. Well, you can easily improve the performance of your SQL database by tuning it.
What is SQL Performance Tuning?
SQL performance tuning, are several techniques used to ensure that the queries of the SQL database run in the fastest possible span to give maximum productivity.
Why is SQL Performance Tuning important?
While the smaller databases might not find SQL tuning significant, but when the databases are very huge ranging up to some Terabytes (TBs), then SQL performance tuning plays a major role in cutting down the query runtime resulting in a much-improved efficiency, making operations a lot faster than before.
Thus, for better performing fast operations, SQL performance tuning of databases is important.
How to do SQL Performance Tuning?
Here are a few ways you can use for doing SQL performance tuning of your database to it speed up:
- Ensure a database server health check-up using DMVs
Use DMVs (Dynamic Management Views), to ensure server health and find the areas of slowdown. It gives the analytics and information about the active sessions and connections, letting you know the objects which consume most memory causing a slowdown.
You can use the below query to know the memory consumption of the given object, to identify areas of slowdown.
Select * from $System.discover_object_memory_usage
- Avoid using SELECT * instead use SELECT fields
Using SELECT * (Select All) means calling of all the available data in the given database, which is often a stringent and more time-consuming process, often calling fields which are actually not required. Thereby, causing inefficiency and consuming more time.
If you use SELECT fields query that would mean you call only the required fields. This helps in significant optimization especially when the databases have a large number of rows and fields.
Avoid
SELECT *
FROM Users
Instead, use
SELECT FirstName, LastName, Address, City, State, Zip
FROM Users
- Avoid using SELECT DISTINCT instead use SELECT for more fields
If you are using SELECT DISTINCT for removing duplicate queries, a lot of processing is required to call the query. As the SELECT DISTINCT query GROUPs all fields to create distinct results, it requires more processing time and can cause a slowdown when the database is significantly large.
Instead, if you opt to remove duplicate fields by calling SELECT for more fields, you tend to get unduplicated records without much processing, optimizing your database performance.
Avoid
SELECT DISTINCT FirstName, LastName, State
FROM Users
Instead, use
SELECT FirstName, LastName, Address, City, State, Zip
FROM Users
- Prefer using Parameterized Queries
Using Parameterized Queries can improve the performance of the database, as they compile the query only once while executing the compiled plan multiple times. This can save time for recompiling each time, thereby improving performance.
Prefer
SELECT “User ID” FROM User WHERE “Sales ID” = ?
- Prefer INNER JOIN to create joins over WHERE
Using WHERE to create joins result in a Cartesian of CROSS JOIN, where all the possible combinations of variables are created, causing a lot of unnecessary processing. This impacts more especially when the number of variables is more, like that in bigger databases.
While using INNER JOIN can be used to optimize the query as it does not create a Cartesian join.
Avoid
SELECT Users.UserID, Users.Name, Sales.LastSaleDate
FROM Users, Sales
WHERE Users.UserID = Sales.UserID4
Instead, use
SELECT Users.UserID, Users.Name, Sales.LastSaleDate
FROM Users
INNER JOIN Sales
ON Users.UserID = Sales.UserID
- Prefer using LIMIT for sample queries
You can limit the number of results when using sample queries. Using LIMIT will run only the given number of outcomes, instead of calling the complete data.
Prefer
SELECT FirstName, LastName, Address, City, State, Zip
FROM Users
LIMIT 20
The Bottom Line
SQL Performance Tuning is very helpful in optimizing database performance significantly by decreasing the processing time, especially for large databases, improving overall efficiency.
If you are stuck with performance improvement, get in touch with me. I will help you unlock the true performance of your database.
Eric Vanier
Database PerformanceTechnical Blog Writer - I love Data
