Submit a ticketCall us

AnnouncementsChange Is Inevitable

Get valuable help when it comes to tracking and monitoring changes. SolarWinds® Server Configuration Monitor (SCM) is designed to help you: detect, track, and receive alerts when changes occur, correlate system performance against configuration changes, compare server and application configuration against custom baselines, and verify application and system changes.

Learn more.

Home > Success Center > Server & Application Monitor (SAM) > SAM - Knowledgebase Articles > AppInsight for SQL (Expensive Queries) is not displaying login and host

AppInsight for SQL (Expensive Queries) is not displaying login and host

Created by Tiarnan Stacke, last modified by MindTouch on Jun 23, 2016

Views: 983 Votes: 1 Revisions: 4

Overview

This article provides brief information and steps to resolve the issue in AppInsight for SQL where Expensive Queries resource is not displaying the Login and Host for SQL Queries.

Environment

SAM version 6.0 and later

Cause 

The SQL Server only provides Login and Host information for Queries which are currently run.

 

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.).

 

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.

 

 

 

 

Last modified

Tags

Classifications

Public