intro
Data technologies have gone through great advancements over the past decades enabling businesses to easily own and operate databases on the cloud and scale up their resources in a few clicks. Faded by technological improvement, people sometimes neglect the basic but essential techniques that can make their database fast and reliable. In this article, we will learn the top five query-tuning techniques for Microsoft SQL Server.
Detect slow queries
To tune slow queries, you first need to find them. You will need to examine them one by one and prioritize tuning. Before selecting slow queries, prepare a speed threshold to only include tuning candidates. To query slow queries that are slower than your threshold, check the query below.
1
SELECT
2
req.session_id
3
, req.total_elapsed_time AS duration_ms
4
, req.cpu_time AS cpu_time_ms
5
, req.total_elapsed_time - req.cpu_time AS wait_time
6
, req.logical_reads
7
, SUBSTRING (REPLACE (REPLACE (SUBSTRING (ST.text, (req.statement_start_offset/2) + 1,
8
((CASE statement_end_offset
9
WHEN -1
10
THEN DATALENGTH(ST.text)
11
ELSE req.statement_end_offset
12
END - req.statement_start_offset)/2) + 1) , CHAR(10), ' '), CHAR(13), ' '),
13
1, 512) AS statement_text
14
FROM sys.dm_exec_requests AS req
15
CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) AS ST
16
WHERE total_elapsed_time > {YOUR_THRESHOLD}
17
ORDER BY total_elapsed_time DESC;
This query gives you a list of elapsed time of each query. The slowest query will appear at the top. You can add your threshold figure in the where clause to, for example, select the top five slowest queries.
Start with the basics
Once you find the queries to be tuned, check if the queries are following the basic rules for performance.
Use the where condition to limit scanning scope
The goal of running a query is to get the information you need. When you run a query without a condition, the database needs to scan the whole area of a table, which leads to a slower query response. If possible, use the where clause to precisely aim at the data you need.
Don't select everything
People often use the star symbol (*) for convenience. However, if a table consists of many columns and holds a large number of records, selecting all of the columns and rows will consume more resources. Instead of using select all, specify the column names that you want.
If you use a correlated subquery (or a repeating subquery), the subquery you use gets executed repeatedly. The sample below shows you what a correlated subquery looks like.
1
SELECT column1, column2, ....
2
FROM table1 outer
3
WHERE column1 operator
4
(SELECT column1, column2
5
FROM table2
6
WHERE expr1 = outer.expr2);
The subquery after the operator is run repeatedly until it satisfies the WHERE condition. Instead of using the correlated subquery, consider using the inner join.
Try to avoid the HAVINGclause and use the WHEREstatement
When you run an aggregated query using GROUP BY, you can add a condition using HAVING. It is recommended to use HAVING only on an aggregated field and not to use it when you can replace it with a where condition. A HAVING clause is calculated after a where clause, so it is recommended to limit data scanning prior to the HAVING clause.
Use inner join instead of two tables in the where clause
You can put two tables in the where clause to use like JOIN. Although it is grammatically accepted, it creates inefficient query execution. Cartesian Join or CROSS JOIN refers to the SQL computation that requires a combination of all possible variables. When you use two tables in the where clause, Cartesian Join kicks in, which consumes a lot of resources. Instead, try to use INNER JOIN.
Use EXPLAIN to find pain points
You can use the EXPLAIN command to diagnose your queries. The command explains your query and shows you how a query will be executed step-by-step. You can use the result to find inefficient steps. The syntax of EXPLAIN is simple.
1
EXPLAIN
2
{YOUR_QUERY}
Put the command, EXPLAIN, above your query and then execute it. In the {YOUR_QUERY} section, you can put not only a SELECT query but also an UPDATE, INSERT, or DELETE statement.
But, it is not straightforward to interpret the result returned from the explain query since it throws you all kinds of text and numbers. Instead, try a visual interpretation like DbVisualizer. When you get visual interpretation, it becomes much easier and intuitive to interpret query steps and pinpoint where to focus.
When you execute an SQL in DbVisualizer, you can see its explain plan at the bottom section as the image above. When you select the Graph View option highlighted in a red box, you can see graph view.
In this graph view, each node in the visual presentation contains more detailed information such as different types of costs, arguments, and more.
Index your tables
Indexing your tables can speed up your query performance. It is like how the index at the end of a book works. By referring to the index, you can go to the page and find the information you need. To efficiently index your tables, there are several points you need to consider.
If you are looking for an easy and powerful SQL client and database manager, then you've got to try DbVisualizer. It connects to nearly any database.
Use visualization tools like DbVisualizer
When you perform query tuning on your own, it can be pretty challenging and time-consuming. DbVisualizer is a universal tool that can meet all your database needs from running queries to database management and query tuning. It beautifully displays database system data for users to interpret information more easily. Its optimization feature can help you to achieve your tuning goals and make your database operate efficiently.
In addition to query tuning, there are more tasks that can benefit from visualization by DbVisualizer. Among many, you can utilize its visual query builder and automatic ERD generation.
The visual query builder lets you simply click the columns you want to select and choose the operations you want to perform. With this visual feature, you can more intuitively build your query.
The ERD generation feature visually displays the relations of your tables. Using the auto-generated diagram, you can efficiently document your table designs and share your ideas with team members.
Conclusion
In this article, we learned the top five query tuning techniques for Microsoft SQL Server. By optimizing your queries, you can more reliably and efficiently serve data requests that come from your online products, dashboards, ad-hoc queries, and other sources. Also, you can reduce costs for upgrading database resources. DbVisualizer can help you to achieve these objectives by providing a single point for users to perform various tasks of the database. Find out more features of DbVisualizer.
