SQL Server Insights
Use Insights to find which databases, logins and applications kept one SQL Server instance busy, and which statements need attention.
Before you begin
- You need the View SQL Server and View Insights permissions.
- The lists come from the statement counters of SQL Server, which Scops reads every 10 minutes. The Activity chart comes from the samples.
Choose the window
Click 1D for the last 24 hours or 1W for the last 7 days, one column per hour. For other whole hours, up to 7 days, click the calendar button, select the start and the end, and click Apply. To read the newest hours again, click 1D or 1W again.
Narrow to a database, login, application or hour
In the Activity header, select Database, Login or Application.
To show only one of them in the lists, click its legend row. You cannot select Other.
To measure the lists over one hour, click its column.
To undo a click, click its chip in the header.
The lists then show only the statements that Scops saw running for that value. Their numbers stay the totals of the instance, because SQL Server counts a statement, not who ran it. The red dashed line on the chart is the number of cores.
Find the heaviest statements
Heaviest Statements lists up to 100 statements that cost the instance the most. Rank them by Total Duration (the default), CPU Time, Logical Reads, Writes, Executions, Memory Grant or Spills. To find inefficient statements, rank by Reads per Row or Reads per Execution.
| Mark | Meaning |
|---|---|
| A red arrow after the query hash | The statement is also in Plan Changes: it runs slower on the plan that it uses now. |
| Plans in amber | More than one plan in the window. |
| encrypted | An encrypted module: SQL Server gives no text for it. |
Click a row to open its statement details for the same window or hour.
Find statements on a slower plan
Plan Changes lists the statements that are at least 1.2 times slower on the plan in use than on their other plans. Both must have 10 executions or more. Scops compares the 7 days up to the end of the window.
Slower By compares Other Plans with Now, per execution. It is amber from 1.5×, red from 3×.
Find new statements
New Statements lists up to 100 statements counted for the first time in the window, for example after a release. Scops compares the window with up to 15 days before it. If it has less than one day of history before the window, the tab shows Cannot tell yet.
Find statements without parameters
Repeated Literals lists the statements that SQL Server receives as many texts that differ only in a literal. Each copy is compiled again and takes room in the plan cache. A statement shows when it has 5 or more Variants in one 10-minute interval and 10 or more executions in the window.
Find implicit conversions
Implicit Conversions lists the statements whose plans convert a column. Affects shows the result:
- Seek Plan, in amber: the index on the column is scanned or not used.
- Cardinality Estimate: the optimizer cannot estimate the rows, so the plan can be wrong.
Open a row to see each conversion with its table, column and plan. Click the statement to open its details.
Find missing indexes
Missing Indexes lists the indexes that the optimizer asked for in the plans of the window. Impact is the decrease of the statement cost that the optimizer estimated. Open a row to see the Equality, Inequality and Include columns and the statements that asked for the index.
The request of the optimizer is a hint, not a script to run. Compare it with the indexes that the table has before you create an index.
Notes under a list
| Note | Meaning |
|---|---|
| Covers | The time that the counters of the list cover. |
| intervals lost part of their numbers | Some statements ran and left the plan cache between two readings. Their numbers are not in the lists. |
| Counters read every 20 minutes | The plan cache is large, so Scops reads it less often than every 10 minutes. |
| By the hour before | Before this time, the lists come from hourly data. A statement that was not one of the largest of an hour has no name for that hour. |
| plans not read yet | On Implicit Conversions and Missing Indexes: Scops did not read some plans, so their findings are not known. |
Related
Updated November 22, 2026