Submit a ticketCall us

Get a crash course on Network Monitoring delivered right to your inbox
This free 7-day email course provides a primer to the philosophy, theory, and fundamental concepts involved in IT monitoring. Lessons will explain not only how to perform various monitoring tasks, but why and when you should use them. Sign up now.

Home > Success Center > Server & Application Monitor (SAM) > 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: 24 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
02:45, 23 Jun 2016

Tags

Classifications

Public