Applications Systems
AppInsight for SQL (Expensive Queries) is not displaying login and host
This article resolves the issue in AppInsight for SQL where Expensive Queries resource is not displaying the Login and Host for SQL Queries.
First published date
Last published date
Overview
Product section
Cause
Resolution
The following is a Query running with SAM:
Note: This contains some things that cannot run in SQL Server Management Studio directly as this is edited by Orion before being run (Variables for Unmanaged Databases etc.).
-- Scripts are not supported under any SolarWinds support program or service.
-- Scripts are provided AS IS without warranty of any kind. SolarWinds further
-- disclaims all warranties including, without limitation, any implied warranties
-- of merchantability or of fitness for a particular purpose. The risk arising
-- out of the use or performance of the scripts and documentation stays with you.
-- In no event shall SolarWinds or anyone else involved in the creation,
-- production, or delivery of the scripts be liable for any damages whatsoever
-- (including, without limitation, damages for loss of business profits, business
-- interruption, loss of business information, or other pecuniary loss) arising
-- out of the use of or inability to use the scripts or documentation.
SELECT
CONVERT(uniqueidentifier, HASHBYTES('MD2', rankTable.[sql_handle])) as [qhash],
rankTable.[sql_handle] as [qhandle],
st.[text],
rankTable.[plan_count],
rankTable.[execution_count],
DATEADD(second, DATEDIFF(second, GETDATE(), GETUTCDATE()), rankTable.[last_execution_time]) AS [last_execution_time],
rankTable.[avg_worker_time],
rankTable.[last_worker_time],
rankTable.[avg_physical_reads],
rankTable.[avg_logical_reads],
rankTable.[avg_logical_writes],
rankTable.[avg_elapsed_time],
rankTable.[last_elapsed_time],
s.[session_id],
s.[login_name],
s.[host_name],
s.[host_process_id],
rankTable.[dbid],
rankTable.[dbname]
FROM (
SELECT
innerTable.*,
[plan].[dbid],
[plan].[dbname],
ROW_NUMBER() OVER (PARTITION BY [plan].[dbid] ORDER BY [avg_worker_time] DESC) AS [_rank_db_worker_time],
ROW_NUMBER() OVER (PARTITION BY [plan].[dbid] ORDER BY [avg_logical_reads] DESC) AS [_rank_db_logical_reads],
ROW_NUMBER() OVER (PARTITION BY [plan].[dbid] ORDER BY [avg_logical_writes] DESC) AS [_rank_db_logical_writes],
ROW_NUMBER() OVER (ORDER BY [avg_physical_reads] DESC) AS [_rank_physical_reads],
ROW_NUMBER() OVER (ORDER BY [avg_elapsed_time] DESC) AS [_rank_elapsed_time]
FROM (
SELECT
qs.[sql_handle],
SUM(qs_plan.[plan_count]) as [plan_count],
SUM(qs.[execution_count]) as [execution_count],
MAX(qs.[last_execution_time]) as [last_execution_time],
SUM(qs.[total_worker_time]) / SUM(qs.execution_count) / 1000 as [avg_worker_time],
AVG(qs.[last_worker_time]) / 1000 as [last_worker_time],
SUM(qs.[total_physical_reads]) / SUM(qs.execution_count) as [avg_physical_reads],
SUM(qs.[total_logical_reads]) / SUM(qs.execution_count) as [avg_logical_reads],
SUM(qs.[total_logical_writes]) / SUM(qs.execution_count) as [avg_logical_writes],
SUM(qs.[total_elapsed_time]) / SUM(qs.execution_count) / 1000 as [avg_elapsed_time],
AVG(qs.[last_elapsed_time]) / 1000 as [last_elapsed_time],
MAX ([plan_handle]) as [plan_handle]
FROM sys.dm_exec_query_stats AS qs
LEFT JOIN (
SELECT
COUNT(DISTINCT [plan_handle]) as [plan_count],
[query_hash],
[query_plan_hash]
FROM sys.dm_exec_query_stats
GROUP BY [query_hash], [query_plan_hash]
) as [qs_plan]
ON qs_plan.[query_hash] = qs.[query_hash]
AND qs_plan.[query_plan_hash] = qs.[query_plan_hash]
WHERE [total_worker_time] > 0
AND [last_execution_time] > DATEADD(SECOND,-5 * ${Frequency},GETDATE())
GROUP BY qs.[sql_handle]
) AS [innerTable]
OUTER APPLY (
SELECT
value AS [dbid],
DB_NAME(CONVERT(int, value)) as [dbname]
FROM sys.dm_exec_plan_attributes(innerTable.plan_handle)
WHERE [attribute] = 'dbid'
) AS [plan]
) AS [rankTable]
CROSS APPLY sys.dm_exec_sql_text(rankTable.[sql_handle]) st
LEFT JOIN sys.dm_exec_requests r
ON rankTable.[sql_handle] = r.[sql_handle]
LEFT JOIN sys.dm_exec_sessions s
ON s.[session_id] = r.[session_id]
WHERE
rankTable.[dbname] NOT IN (${UnmanagedDatabases})
AND (
[_rank_db_worker_time] <= ${RowCount}
OR [_rank_db_logical_reads] <= ${RowCount}
OR [_rank_db_logical_writes] <= ${RowCount}
OR [_rank_physical_reads] <= ${RowCount}
OR [_rank_elapsed_time] <= ${RowCount}
)
In that query we are looking at sys.dm_exec_query_stats. This pulls the SQL Query Hash, execution time, execution count etc.
These are cumulative and aggregated statistics that aren't by session/connection.
Try and match the Query Hash against the current sessions (Through multiple joins on System Views).
This will only work for where the query was running at the time of the poll, otherwise it will not be able to pull back the specific User/Hostname Information.
We have another product, Database Performance Analyzer, which polls the Database every second which can facilitate the level of information you're looking for. This is because DPA is polling in such a way that it can get this level of detail with causing minimal overhead on the Database. SAM is not capable of performing this level of polling due to the overhead it would have on the target SQL Server.