SQL Server Slow Queries (Beta)
Page description
This page shows the queries on an instance that Coyote Monitor has analyzed and found room for improvement in, so you can see which ones consume the most resources and what can be done to fix them. A slow or expensive query is one that takes a long time to finish, uses a lot of CPU, or reads a lot of data: while it runs it competes with the rest of the server's activity, and if it also runs frequently, its impact multiplies.
The panel is used in two steps. First you choose which metric to sort by (CPU, reads, duration…) and the panel lists the 20 most prominent queries for that metric. Then you select one of them and get its full detail: the original text, a documented version, a proposed optimized version, specific recommendations, an estimated improvement, and a graphical viewer for its execution plan.
The analysis and optimization proposals are generated by an automated system (AI) from the query and its execution plan. They are an aid to decide where to start, not a change that applies itself: the final decision and validation remain yours.
Variables
| Variable | Description |
|---|---|
$Server_Name | Selects the instance whose queries you want to review. Values correspond to the instances configured in the monitoring environment. |
Comments | Language (Spanish or English) of the texts generated by the analysis: documented query, optimized query, recommendations and estimated improvement. The original query text and the execution plan are not translated. |
This dashboard does not depend on the time range picker: it always shows the most recent analysis available for the selected instance.
General Status
Status Header
- Description: Top bar with the instance name and a status badge summarizing how many queries with room for improvement it has, broken down by level, together with the date and time of the last analysis. On the right is a legend with the three improvement levels. Each level represents how much a query's performance could improve if the proposed changes are applied, and uses the same colors on the Select Query cards and in the Query List header.
Colors by value:
| Value | Color | Description |
|---|---|---|
| HIGH IMPROVEMENT | There is at least one HIGH-level query: this is where the most performance can be gained. | |
| MEDIUM IMPROVEMENT | No HIGH queries, but there are MEDIUM ones: noticeable improvement. | |
| LOW IMPROVEMENT | Only LOW-level queries: small improvement. | |
| NOTHING TO IMPROVE | The instance has no queries pending improvement. |
The badge always takes the color of the highest level present on the instance.
Query Selection
Sort bar and query list on the left. This is where you decide which metric drives the ranking and which query you want to study.
Sort By
- Description: Bar with seven buttons that lets you choose which metric the queries in the list are sorted by. The active button is highlighted with the green gradient; hovering over any of them shows a short explanation of what it measures. Clicking one instantly recalculates the Select Query list with that metric, and the detail panel highlights the value of that same metric in green. The CPU, reads, writes and duration metrics are per-execution averages; Executions is a count and Score is a point score.
| Button | What it measures | When to look at it |
|---|---|---|
| Score | Points summarizing the performance problems found in the query's execution plan. It starts at 1 and adds 1 for each type of problem detected: missing indexes, lookups, scans, sorts, join operators, spills to TempDB, memory grant warnings, implicit conversions, parallelism, user-defined functions… | To start with the queries that have the most room for improvement, even if they are not the slowest or the most frequent. It is the same Score as in the badges: above 8 is HIGH, 4 to 8 is MEDIUM and below 4 is LOW. |
| CPU Time | Processor time the query uses per execution. | When the server CPU is high: points to the queries that use the most processor. |
| Logical Reads | Data pages (8 KB each) read from the server's memory cache. | High values usually indicate missing indexes or inefficient execution plans that go through more data than needed. |
| Logical Writes | Data pages modified in memory. | Relevant for write-heavy queries (INSERT, UPDATE, DELETE). |
| Physical Reads | Pages read directly from disk because they were not in memory. | High values point to memory pressure or very large reads without a suitable index. |
| Duration | Total elapsed time from query start to finish. | Closest to what users actually feel: useful for finding slow queries. |
| Executions | Number of times the query has run. | A cheap query run thousands of times can weigh more than an expensive, occasional one. |
- Units: Executions in number of executions; Duration and CPU Time in milliseconds; reads and writes in data pages; Score in points (no unit).
Practical recommendation
If your concern is the slowness users perceive, start with Duration and CPU Time. If the problem is a constant load without one clearly slow query, try Executions: sometimes the culprit is a very light query fired massively.
Select Query
- Description: Vertical list with the 20 queries of the selected instance that have the highest value for the metric chosen in Sort By. Only queries where the analysis found room for improvement appear. The list is sorted from highest to lowest by the metric chosen in Sort By; if two queries tie on that metric, the one with the higher Score comes first.
- Interactivity: every card is clickable. Clicking it marks it with a green border and the detail panel on the right switches to that query, without reloading the page.
Each card shows:
- #Id: the query's identifier within Coyote Monitor.
- SCORE badge (for example, SCORE 8): a score summarizing how many performance problems were detected in the query's execution plan (missing indexes, inefficient accesses, warnings from SQL Server itself…). The higher it is, the more room for improvement. The badge color shows the improvement level: how much the query could gain if the proposed changes are applied.
- Database the query runs in.
- Chosen metric: the name and value of the metric the list is currently sorted by (for example,
CPU Time: 5723 msorExecutions: 133), to see at a glance how much each query weighs on that criterion. It changes when you click another Sort By button.
Colors by value:
| Value | Color | Description |
|---|---|---|
| HIGH (Score above 8) | High improvement: this is where the most performance can be gained. Review this query as soon as possible. | |
| MEDIUM (Score 4 to 8) | Medium improvement: worthwhile, not urgent. | |
| LOW (Score below 4) | Low improvement: limited impact, address when there is room. |
Query Detail
Query List
- Description: Main panel of the dashboard. Shows the full detail of the query selected in Select Query: a header with its key data, four text blocks with the analysis, and a graphical viewer for its execution plan.
- Units: Executions in number of executions; CPU Time and Duration in milliseconds; Logical Reads, Logical Writes and Physical Reads in data pages.
Header
Summarizes the query at a glance: its Id, the SCORE badge (with the same colors as the table above), the database and the estimated improvement the analysis expects if the proposed optimization is applied. On the right is the ◈ View Execution Plan button, which opens the plan viewer.
Below it, on a second line, are the values of the six Sort By metrics for that query: CPU Time, Logical Reads, Logical Writes, Physical Reads, Duration and Executions. The metric the list is currently sorted by is shaded in green, so you can find it easily and compare the query's other values. If the list is sorted by Score, what is highlighted (with a green border) is the SCORE badge on the first line, because Score is not one of the six metrics on this second line.
Analysis blocks
Four boxes, each with its own Copy button to copy its content to the clipboard (the button shows "Copied" for a few seconds as confirmation):
- Original Query: the query text as it runs on the server, formatted and syntax-highlighted so it is readable.
- AI Documented Query: the same query with comments explaining what each part does.
- AI Optimized Query: a proposed rewrite of the query intended to run better.
- Recommendations: list of suggested actions. Group headings appear in green and each recommendation has a purple side bar.
Practical recommendation
Treat the optimized query and the recommendations as a starting point, not a production-ready change. Test them first in a test environment, compare the execution plan and timings against the original query, and review the maintenance cost before creating a new index: every index speeds up reads but slows down writes to the table.
Execution plan viewer
The execution plan is the "map" SQL Server follows to resolve a query: it shows in which order it accesses the tables, which indexes it uses and how much each step costs. The ◈ View Execution Plan button opens it in a full-screen window with three areas.
Toolbar. Shows the query title and lets you close the window (Close), copy the plan XML (Copy Query Plan XML), zoom in (+), zoom out (−) and fit the plan to the window (Fit). The plan can also be dragged with the mouse and zoomed with the wheel.
Plan Insights. Three columns summarizing the plan:
- Runtime Summary: general plan data, such as the statement type (Statement Type), its total cost (Total Cost), compile time or maximum degree of parallelism (Max DOP). Only the items the plan includes are shown.
- Missing Index Suggestions: indexes SQL Server thinks would help this query, with the improvement percentage it estimates (Impact) and the suggested columns (EQUALITY, INEQUALITY and INCLUDE). If there are no suggestions, "No missing index suggestions" appears.
- Warnings: plan warnings, both global and per operator, such as spills to TempDB, memory grant problems, unreliable row estimates or joins without a condition. If there are none, "No warnings found" appears.
Plan diagram. Each operator is a node with its name, an icon and the cost percentage (Cost) it contributes. That percentage is relative to the most expensive operator in the plan, not to the total cost: the most expensive one marks 100%. Nodes with warnings also show "⚠ n warnings". Hovering a node shows a tooltip with its operator cost, subtree cost, estimated rows, whether it is parallel, and its warnings. Clicking a node smoothly zooms the view to it.
Colors by value (diagram nodes):
| Value | Color | Description |
|---|---|---|
| Statement (root node) | Summary of the whole statement: total cost and estimated rows. It does not count as an operator. | |
| > 50% | Very expensive operator: contributes more than half of the cost of the most expensive operator in the plan. This is the first place to look. | |
| > 20% | Noticeable cost, but not dominant. | |
| Rest of values | Low-cost operator. |
Practical recommendation
In the diagram, look for the red nodes first: they concentrate the cost. A high-cost Scan operator (a full read of a table or index), together with a suggestion in Missing Index Suggestions, usually means a suitable index would greatly reduce the query's work.