SQL Performance Tuning
: 7 Practical Tips for Developers
Being able to execute SQL performance tuning is a vital skill for software teams that rely on relational databases. However, vital is not the only adjective we can apply to it. Rare also comes to mind, unfortunately.
Many software professionals think they can leave all RDBMS configurations as they came by default. They are wrong. Often, the default settings that your RDBMS is configured with is far from optimal. Failure to optimize such settings results in performance issues that could have been easily avoided.
Some programmers, on the other hand, believe that while SQL performance tuning is important, only DBAs should. They are also wrong.
First of all, not every company will have a person with the official title of “DBA”. It depends on the size of the company, more than anything.
But even if you have a dedicated DBA on the team, that doesn’t mean you should overwhelm them with tasks that could have been done by the developers themselves. If a developer can diagnose and fix a slow query, then there’s no reason why they shouldn’t. The relevant word here, however, is may, most of the time, cannot.
How do we solve this problem? Simple: We equip developers with the knowledge they need to find slow SQL queries and perform performance tuning on SQL Server. In this post, we’ll give you seven tips for doing just that.
What is SQL Performance Tuning?
Before I showed you our list of tips you can use to perform SQL performance tuning in your software organization, I thought we should define SQL performance tuning.
So what is SQL performance tuning? I bet you already have an idea, even if it’s vague.
Simply put, SQL performance tuning is about making queries in a relationship database run as fast as possible.
As you’ll see in this post, SQL performance tuning is not a single tool or technique. Rather, it is a set of practices that makes use of a wide range of techniques, tools, and processes.
7 Ways to Find Slow SQL Queries
Without further ado, here are seven ways to find slow SQL queries in SQL Server
.
1. Generate an
actual execution plan
To diagnose slow queries, it is crucial to be able to generate graphical execution plans, which you can do by using SQL Server Management Studio. The actual execution plans are generated after the queries are executed. But how do you generate an execution plan?
Start by clicking “Database Engine Query” on the SQL Server Management Studio toolbar.
After that, enter the query and click “Include Actual Execution Plan” from the Query menu.
Finally, it’s time to run your query. To do this, click the “Run” toolbar button or press F5. SQL Server Management Studio will then display the execution plan in the results pane on the “Execution Dashboard” tab.
2. Monitoring
resource usage
Resource usage is an essential factor when it comes to the performance of a SQL database. Since you can’t improve what you don’t measure, you should definitely monitor resource usage.
So how can you do it?
If you are using Windows, use the System Monitor tool to measure SQL Server performance. Allows you to view SQL Server objects, performance counters, and the behavior of other objects.
Using System Monitor allows you to monitor Windows and SQL Server counters simultaneously, so you can check if there is any correlation between the performance of the two.
3. Use Database Engine Tuning Advisor Another important technique for SQL performance tuning is to analyze the performance of
Transact-SQL statements that are executed in
the database that you want to tune.
You can use Database Engine Tuning Advisor to analyze performance implications
.
But the tool goes further: it also recommends actions you should take based on your analysis. For example, you might advise you to create or delete indexes.
4. Find slow queries with
SQL DMV One of the
great features of SQL Server is all the dynamic management views (DMVs) that are built into it. There are dozens of them and they can provide a wealth of information on a wide range of topics.
There are several DMVs that provide data on query statistics, execution plans, recent inquiries, and more. These can be used together to provide some surprising insights.
For example, the following query can be used to find queries that use the most reads, writes, working time (CPU), and so on.
SELECT TOP 10 SUBSTRING(qt. TEXT, (qs.statement_start_offset/2)+1, ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(qt. TEXT) ELSE qs.statement_end_offset END – qs.statement_start_offset)/2)+1), qs.execution_count, qs.total_logical_reads, qs.last_logical_reads, qs.total_logical_writes, qs.last_logical_writes, qs.total_worker_time, qs.last_worker_time, qs.total_elapsed_time/1000000 total_elapsed_time_in_S, qs.last_elapsed_time/1000000 last_elapsed_time_in_S, qs.last_execution_time, qp.query_plan OF sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt CROSS APPLY sys. dm_exec_query_plan(qs.plan_handle) ORDER QP BY DEC qs.total_logical_reads – LOGICAL READS – SORT BY qs.total_logical_writes DESC – logical writes – SORT BY qs.total_worker_time DESC – CPU time
The result of the query will look something like this below. The image below is from a marketing app I made. You can see that a particular query (the top one) takes up all the resources.
Looking at this, I can copy that SQL query and see if there is any way to improve it, add an index, etc
.
Pros: Basic summary statistics always available. Cons: It doesn’t tell you what you’re calling for consultations. You cannot see when queries are called over time.
5. Query reporting through APM
solutions
One of
the great features of application performance management (APM) tools is the ability to track SQL queries. For example, Retrace tracks SQL queries against several database providers, including SQL Server. Retrace tells you how many times a query was executed, how long it takes on average, and what transactions called it.
This is valuable information for SQL performance tuning. APM solutions collect this data by creating lightweight performance profiles in your application code at run time.
Below is a screenshot of the Retrace application dashboard showing which SQL queries take the longest in a particular application.
<img src="https://stackify.com/wp-content/uploads/2017/06/img_5944220db8738.png" alt="Retrace View All SQL Queries
collects performance statistics about each SQL query that is executed. You can search for specific queries to find potential problems.
<img src="https://stackify.com/wp-content/uploads/2017/06/performance-tuning-in-sql-server-4-ways-to-find-slow-queries-12170.png" alt="Retrace SQL performance over time
When you select an individual query, you’ll see how often that query is called over time and how long it takes. You can also see which webpages use the SQL query and how it affects their performance.
RetraceSince Retrace is a lightweight code profiler and captures ASP.NET trace requests, it even shows you the full context of what your code is doing.
Below is a captured trace showing all the SQL queries and other details about what the code was doing. Retrace even displays log messages within this same view. Additionally, notice that it displays the address of the server and the name of the database that is running the query. You can also see how many records were returned.
Retracetransaction tracking As you can see, Retrace provides full SQL reporting capabilities as part of its APM capabilities. It also provides multiple monitoring and alerting features around SQL queries.
Pros: Detailed reports of all applications, by application and by query. Displays detailed transaction traces of how queries are used. It starts at just $10 a month. It is always running once installed.
Cons: Does not provide the number of reads or writes per query
. 6. SQL Server
Extended Events
SQL Server Profiler existed for a long time. It was a very useful tool to see in real time what SQL queries are running on your database, but it is currently deprecated. Microsoft replaced it with SQL Server Extended Events.
This will surely anger a lot of people, but I can understand why Microsoft is doing it. Extended Events works through Event Tracing (ETW).
This has been the common way for all Microsoft-related technologies to expose diagnostic data. ETW provides much more flexibility. As a developer, you could easily leverage SQL Server ETW events to collect data for custom uses. That’s really cool and really powerful.
MORE: Introducing SQL Server Extended Events
Pros: Easier to enable and leave running. Easier to develop customized solutions.
Cons: Since it’s fairly new, most people may not be aware of it.
7.SQL
Azure Query performance information
I’m going to assume that SQL Azure performance reports are based on extended events. In the Azure portal, you can access a wide range of optimization tips and performance reports that are very useful.
Note: These reporting capabilities are only available for databases hosted in SQL Azure.
In the following screenshot, you can see how SQL Azure makes it easy to use queries that use more CPU, data I/O, and log I/O. It has some great basic reports built in.
<img src="https://stackify.com/wp-content/uploads/2017/06/performance-tuning-in-sql-server-5-ways-to-find-slow-queries-12179.png" alt="You can also select an individual query and get more details to help with SQL performance tuning.
Pros: Great basic reports. Cons: Only works on Azure. There are no reports in multiple databases.
Summary
The next time you need to perform some performance tuning with SQL Server, you’ll have a few options at your disposal to consider. Chances are, you’ll use more than one of these tools depending on what you’re trying to accomplish.
Try Stackify’s free code profiler, Prefix, to write better code on your workstation. Prefix works with .NET, Java, PHP, Node.js, Ruby and Python.
If you’re using an APM solution like Retrace, be sure to check what kind of SQL performance functionality it has built in. If you don’t have an APM solution or aren’t sure what it is, be sure to read this: What is Application Performance Management and 10 Critical Features Developers Need in APM.
