Being able to execute a SQL performance tuning is a vital skill for software teams that rely on relational databases. Vital isn’t the only adjective that we can apply to it, though. Rare also comes to mind, unfortunately. Many software...
Being able to execute a SQL performance tuning is a vital skill for software teams that rely on relational databases. Vital isn’t the only adjective that we can apply to it, though. Rare also comes to mind, unfortunately.
Many software professionals think that they can just leave all the RDBMS settings as they came by default. They’re wrong. Often, the default settings your RDBMS comes configured with are far from being the optimal ones. Not optimizing such settings result in performance issues that could’ve been easily avoided.
Some programmers, on the other hand, believe that even though SQL performance tuning is important, only DBAs should do it. They’re wrong as well.
First of all, not all companies will even have a person with the official title “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’ve been performed by the developers themselves. If a developer can diagnose and fix a slow query, then there’s no reason why they shouldn’t do it. The relevant word here, though, is can—most of the time, they can’t.
How do we fix this problem? Simple: we equip developers with the knowledge they need to find slow SQL queries and do performance tuning in SQL Server. In this post, we’ll give you seven tips to do just that.
Tip: Find application errors and performance problems instantly with Stackify Retrace Troubleshooting and optimizing your code is easy with integrated errors, logs and code level performance insights. Try today for free What Is SQL Performance Tuning? Before we show you our list of tips you can use to do SQL performance tuning on your software organization, I figured we should define SQL performance tuning.
So what is SQL performance tuning? I bet you already have an idea, even if it’s a vague one.
In a nutshell, SQL performance tuning consists of making queries of a relation 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’s a set of practices that makes uses of a wide array 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.
Begin by clicking on “Database Engine Query”, on the SQL Server Management Studio toolbar.
After that, enter the query and click “Include Actual Execution Plan” on the Query menu.
Finally, it’s time to run your query. You do that by clicking on the “Execute” toolbar button or pressing F5. Then, SQL Server Management Studio will display the execution plan in the results pane, under the “Execution Pane” tab.
So how can you do it?
If you’re using Windows, use the System Monitor tool to measure the performance of SQL Server. It enables 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 verify if there’s any correlation between the performance of the two.
You can use the Database Engine Tuning Advisor to analyze the performance implications.
But the tool goes beyond that: it also recommends actions you should take based on its analysis. For instance, it might advise you to create or remove indexes.
There are several DMVs that provide data about query stats, execution plans, recent queries and much more. These can be used together to provide some amazing insights.
For example, the query below can be used to find the queries that use the most reads, writes, worker time (CPU), etc.
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 FROM 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) qp ORDER BY qs.total_logical_reads DESC -- logical reads -- ORDER BY qs.total_logical_writes DESC -- logical writes -- ORDER 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 one particular query (the top one) takes up all the resources.
By looking at this, I can copy that SQL query and see if there is some way to improve it, add an index, etc.
Find slow SQL queries with DMVs Pros: Always available basic rollup statistics. Cons: Doesn’t tell you what is calling the queries. Can’t visualize when the queries are being called over time.
This is valuable information for SQL performance tuning. APM solutions collect this data by doing lightweight performance profiling against your application code at runtime.
Below is a screenshot from Retrace’s application dashboard showing which SQL queries take the longest for a particular application.
SQL Performance Tuning With Retrace Top Queries Retrace Top SQL Queries Retrace collects performance statistics about every SQL query being executed. You can search for specific queries to find potential problems.
Retrace View All SQL Queries Retrace View All SQL Queries By selecting an individual query, you see how often that query is called over time and how long it takes. You also see what webpages use the SQL query and how it impacts their performance.
Retrace SQL Performance Over Time Retrace SQL Performance Over Time Since Retrace is a lightweight code profiler and captures ASP.NET request traces, 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 shows log messages within this same view. Also, notice that it shows the server address and database name that’s executing the query. You can also see how many records were returned.
Retrace Web Transaction Trace Retrace Web Transaction Trace As you can see, Retrace provides comprehensive SQL reporting capabilities as part of its APM capabilities. It also provides multiple monitoring and alerting features around SQL queries.
Pros: Detailed reporting across apps, per app, and per query. Shows transaction traces detailed how queries are used. Starts at just $10 a month. Is always running once installed.
Cons: Doesn’t provide the number of reads or writes per query.
This is sure to anger a lot of people but I can understand why Microsoft is doing it. Extended Events works via 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, I could easily tap into ETW events from SQL Server to collect data for custom uses. That’s really cool and really powerful.
SQL Server Extended Events MORE: Introducing SQL Server Extended Events
Pros: Easier to enable and leave running. Easier to develop custom solutions with.
Cons: Since it is fairly new, most people may not be aware of it.
Note: These reporting capabilities are only available for databases hosted on SQL Azure.
In the screenshot below, you can see how SQL Azure makes it easy to use your queries that use the most CPU, Data IO, and Log IO. It has some great basic reporting built into it.
In the screenshot you can see how SQL Azure makes it easy to use your queries that use the most CPU, Data IO, and Log IO. It is has some great basic reporting built into it. SQL Azure Top Queries You can also select an individual query and get more details to help with SQL performance tuning.
You can also select an individual query and get more details to help with SQL performance tuning. SQL Azure Query Details Pros: Great basic reporting. Cons: Only works on Azure. No reporting across multiple databases.
Summary Next time you need to do some performance tuning with SQL Server, you’ll have a few options at your disposal to consider. Odds are that you’ll use more than one of these tools depending on what you are 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 that developers need in APM.
SQL stands for Structured Query Language. SQL is a scripting language expected to store, control, and inquiry information put away in social databases. The main manifestation of SQL showed up in 1974, when a gathering in IBM built up the principal model of a social database. The primary business social database was discharged by Relational Software later turning out to be Oracle.
To make the most out of the benefits of offshore software development, you should understand the crucial factors that affect offshore development.
3 practice tips to write SQL queries which are easier to read and modify even after six month and one year. Use this tips to write professional quality SQL.There is no doubt that writing code is more art than science and every coder cannot write beautiful code which is both readable and maintainable, even with the experience.
This article mentions the fundamentals of the SQL Substring function and how to improve its performance. SQL Server offers various built-in functions and these functions make complicated calculations easier for us.
SQL performance tuning can be a tricky thing. We spend so much time focusing on our business logic, it’s easy to leave the database behind in the dust. But it’s important to keep the DB in mind to maintain quality performance.