Slow queries are one of the most common problems in an organization that processes large amounts of data. For almost all customers, the most difficult issue I’ve worked on is how to find slow queries and determine the real cause of performance issues. Fortunately, in most cases the solution is straightforward.
I always recommend that you spend most of your time finding the real cause of the problem without thinking about possible solutions. Fortunately, there are certain tools and techniques that a developer or DBA should always use (at least) to understand slow queries.
Before going into details, I would like to point out that the tools and techniques I have mentioned here are intended for SQL developers who have no expertise in database management and for database administrators starting their careers.
Like every job in the world, you need special tools. In this article, we will only describe the tools already available on your system or you can download them for free. I won’t say that commercial vehicles don’t help. It is highly recommended to save time in in-depth analysis of large organizations. . Highlights slow queries to each server. However, for the purposes of this article, we will roll up our sleeves and learn the basics using the tools we already have.
The first tool I’d like to mention here is the built-in tool for SQL Server Management Studio. ” Activity Monitor”. You can verify this by right-clicking on the instance name in SQL Server Management Studio and selecting ” Activity Monitor”.
Informs you of the current activity of the SQL Server
The screenshot above shows the activity monitor overview window. This screen shows graphs of CPU time, pending jobs, and batch queries. In general, fewer songs result in better performance. Large organizations with high workloads can have many batch queries with high CPU time, but this does not necessarily indicate a performance issue.
By focusing on the process that grants access based on an overview, you can see all the processes running on the instance and better track the number of pending, blocked, or blocked processes. This way you can check if the request is slow due to a certain latency or if long queries are blocked by other processes. In this view, you can right-click on a process and select Details to see the current TSQL running for that session.
Blocked the queries that are actually suspended by another process running on the resource to which that process is attached. So, if the request is blocked by another process, check the Blocked column to see which root block caused the full lock. Consider only these queries, not all blocked transactions.
Use the simplest tools and techniques as possible
And a request waiting for a specific resource gives you information about what resource is waiting, so you can check the wait type and find a solution to that problem. Some of the more common wait statistics are listed in the SQL Server Wait Types section of Extended SQL Shack. Learn more about what to do next.
Provides information about active expensive and new expensive queries, high CPU queries, logical reading or elapsed time.
You can skip to each section for the current or most recent high value query. Sort by elapsed time, logical reading, CPU time and check execution schedule. In your execution plan, you can find out why these expensive queries are taking too much time so you can take appropriate steps to resolve the issue. Check it out as we’ll show you how to proceed with SQL Server Query Execution Scheduling later in this article.
The next tool is the query source, this is useful and you can save lives by calling in the middle of the night an hour ago to see why your SQL server is slowing down.
Summary of problem solve
In general, you cannot view query execution history prior to SQL Server 2016 without a standalone application or custom solution. So, in this context, query sources offer a lot of additional potential. Ed Pollack wrote here about the source of the question, a great resource for detailed research on the question catalog, so read this article.
If you have SQL Server 2016 or higher, first enable it in database properties. When you enable the query source, you have the following database properties:
After enabling the Query Store, you can expand the database object and go to the “Main Resource Consumption Inquiry”, as shown in the screenshot below.
Right-clicking on a resource query and selecting “Show Resource Query” takes you to a window that displays these resource-intensive queries. You can customize the display by selecting the appropriate “metric” such as duration, processor time, logical reads, or memory consumption. You can change it to Min, Mac or Aug. It is recommended to use the average stats with all of the above metrics to get the query.
The next step is to highlight queries that consume a lot of resources. When you highlight the chart values in the left pane (highlighted in the screenshot below), you’ll see a query execution plan in the bottom pane.
You can get the actual text of the query for further analysis by clicking on the button highlighted below in the Query Store window.
So, from now on, there are more ways to get inquiries about high resource usage. Now let’s see why the query is slow and how to determine which part of the query needs to be fixed (if necessary).
So, here we will look at an example of a query used in the Microsoft “WideWorldImporters” database. TSQL executes ” [Integration].[GetOrderUpdates]”.
It takes about 1 second to call this stored procedure and I won’t optimize it. It is intended to give an example of how you can know how these candles have been consumed. Also, you would like to know which part of the query is taking the most time and which table you need to focus on.
Here is the stored procedure call and result:
We are now called and we will dig deeper into it.
You should first enable query statistics for this session. Run TSQL “SET STATISTICS TIME, IO ON” to enable CPU and IO statistics for this query session.
After running the aforementioned TSQL to enable statistics, I get the IO and total CPU costs of each query table executed under the stored procedure on the message card as shown below.
You can run multiple queries within a stored procedure, so the statistics give you the time for each query and the total number of all queries at the end. So, for a stored procedure for total CPU time, it only considers the last CPU time, for each query it only considers that CPU time and excludes all the last CPU time.
On my daily job, I am a software engineer, programmer & computer technician. My passion is assembling PC hardware, studying Operating System and all things related to computers technology. I also love to make short films for YouTube as a producer. More at about me…