The Query Workload (Wait Stats) dashboard displays a breakdown of where time is being spent in queries executed.
SQLGrease measures query performance using the concept of wait event analysis. Wait event analysis gives visibility into where a query is spending its time. An executing query is either using CPU or is waiting on something.
As of SQL Server 2017 there are over 900 wait types. While SQLGrease collects details on all 900+ wait types, the plugin groups these wait types into the following categories:
By analyzing query performance with this approach, SQLGrease can provide details of where queries are spending time executing. SQLGrease does not collect this info from sys.dm_os_wait_stats. That approach, while simple, genereates a lot of information that is not impacting query performance. Instead SQLGrease collects wait stats at the executing query level - this provides actionable information to tune with.
The plugin exposes this information via the Query Workload (Waits) dashboard. At the top of the dashboard is a chart (Total Query Workload) displaying a breakdown of CPU Time as well as waits. These statistics are at an aggregate level for all executions of the query within the displayed timeframe. More granular statistics are available in the SQLGrease UI.
Below the Total Query Workload chart is a breakdown of the top 10 queries in the monitored interval impacted by the waits displayed in the chart. These statistics are at an aggregate level for all executions of the query within the displayed timeframe. More granular statistics are available in the SQLGrease UI.
The query text displayed has several components to it.
The Query Hash is an identifier for queries used in SQL Server. SQLGrease keys all of its query performance metrics on query hashes. You can use this value to locate queries found from the dashboard in SQLGrease. Additionally, since this is also recognized by SQL Server, you can directly interrogate SQL Server DMV's with this value.
If the query is related to a stored procedure, the stored procedure name will be displayed.
The query is the last part of the name.
The query text may be truncated based on a 250 character limit. If the query is truncated it will display elipsis where it was truncated. Additionally, certain characters will be stripped due to New Relic limitations on metric naming. The following characters will be removed if they are part of a query:
Full query text is available in the SQLGrease UI.