[11.1.125 Pro] - FR: Session Monitor

[11.1.125 Pro] - FR: Session Monitor

SA clears result set filters upon refresh. Could you make it re-apply them after refreshing the contents, please?

Acknowledged. Thank you for letting us know. It appears that the Auto-filter feature made into the menu as a generic feature available in all data grids but it's not directly supported by the monitor, that's why filters get cleared after each refresh, unlike grid sorting. I hope this will be fixed soon; the issue can be tracked under #SA0038899

This issue has been fixed in the latest version 11.2 which is now available for download

Could you check this in 13.5.73 Pro, please? It appears to be severely broken.

Could you check this in 13.5.73 Pro, please? It appears to be severely broken.

It's marked as fixed in 11.2 (technically in internal version 11.1.125, which made into 11.2). I tried with 13.5.73, with 1 to 3 session monitoring tabs opened for different database server instances with different filters, and didn't notice any issues.

Could you please check what's different in your case? If you have lots of connections, does the monitor screen complete refreshing within refresh cycles? If the cycles are too tight, that may cause collisions with filtering or conditional formatting (UI drawing not finished before new refresh starting, they need the hidden rows to work correctly, and could break if the data changes in flight).

It does not take anything that drastic, like refresh cycles overlapping, because auto-refresh is disabled. Yet it still does not work.

I turn on Auto-Filtering, set Auto-refresh to Don't refresh, and then pick one column (eg. Database) to filter on a single value. The result set is filtered on it, so far, so good. I manually hit the refresh button. Now the grid contains the row that was allowed through the filter, as before, and a couple of (probably new) rows that should have been filtered out. Instead, they're there, and if I click on the filter dropdown again, those have their checkboxes checked.

My first thought was maybe my custom monitor query had something to do with it, but this still happened with the factory one, too.

Check out this short video >>>here<<<.

Thank you very much for the additional information. We're reviewing your screen capture.

We think this is related to a type of custom query you are using for the session monitor, the columns appr to be different and in a different order comparing to the predefined query.

Could you please share your custom query we can test with?

	SysOp wrote:
	We think this is related to a type of custom query you are using for the session monitor, the columns appr to be different and in a different order comparing to the predefined query. Could you please share your custom query we can test with?

Sure, here it comes. However, as I mentioned, this also occurs when I revert the monitor query back to its factory default.

Code:

-- CAPABILITIES: XSUHAQDO%TZECBW
DECLARE @_current_login SYSNAME = original_login();

SELECT sq1.[session_id] AS [Session ID]
,(CASE WHEN sq1.[blocked_session_ids] IS NOT NULL OR sq1.[blocking_session_id] IS NOT NULL THEN 1 ELSE 0 END) AS [F]
,sq1.[blocked_session_ids] AS [bsid]
,sq1.[blocking_session_id] AS [bbsid]
,sq1.[wait_type]
,sq1.[wait_time] AS [wait_ms]
,sq1.[DB] AS [DB S / R]
,sq1.[program_name]
,sq1.[obj_name]
,sq1.[time_s]
,sq1.[query_text]
,sq1.[USER_NAME] AS [user]
,sq1.[reads] AS [R]
,sq1.[logical_reads] AS [L R P]
,CASE
WHEN [logical_reads] * 8.0 / 1000 < 1000 THEN CAST([logical_reads] * 8.0 / 1000 AS decimal(6 ,2)) -- ' MB'
WHEN [logical_reads] * 8.0 / 1000 / 1000 < 1000 THEN CAST([logical_reads] * 8.0 / 1000 / 1000 AS decimal(6 ,2)) -- ' GB'
WHEN [logical_reads] * 8.0 / 1000 / 1000 / 1000 < 1000 THEN CAST([logical_reads] * 8.0 / 1000 / 1000 / 1000 AS decimal(6 ,2)) -- ' TB'
WHEN [logical_reads] * 8.0 / 1000 / 1000 / 1000 / 1000 < 1000 THEN CAST([logical_reads] * 8.0 / 1000 / 1000 / 1000 / 1000 AS decimal(6 ,2)) -- ' PB'
END AS [L R A]
,CASE
WHEN [logical_reads] * 8.0 / 1000 < 1000 THEN 'MB'
WHEN [logical_reads] * 8.0 / 1000 / 1000 < 1000 THEN 'GB'
WHEN [logical_reads] * 8.0 / 1000 / 1000 / 1000 < 1000 THEN 'TB'
WHEN [logical_reads] * 8.0 / 1000 / 1000 / 1000 / 1000 <1000> 0 THEN CASE er.statement_end_offset
WHEN -1 THEN SUBSTRING(st.text ,(er.statement_start_offset / 2) + 1 ,2147483647)
ELSE SUBSTRING(st.text ,(er.statement_start_offset / 2) + 1 ,(er.statement_end_offset - er.statement_start_offset) / 2)
END
ELSE CASE er.statement_end_offset
WHEN -1 THEN RTRIM(LTRIM(st.text))
ELSE LEFT(st.text ,(er.statement_end_offset / 2) + 1)
END
END AS query_text
,DB_NAME(ISNULL(er.database_id ,ses.database_id)) AS DB_NAME
,OBJECT_NAME(st.objectid ,st.dbid) AS obj_name
,COALESCE(
er.percent_complete
,(
SELECT CONVERT(DECIMAL(5 ,2) ,(([RowsProcessed] * 1.0) / [TotalRows]) * 100) AS [PercentComplete]
FROM (
SELECT *
,([TotalRows] - [RowsProcessed]) AS [RowsLeft]
,([ElapsedMS] / 1000.0) AS [ElapsedSeconds]
FROM (
SELECT SUM(qp.[row_count]) AS [RowsProcessed]
,SUM(qp.[estimate_row_count]) AS [TotalRows]
,MAX(qp.last_active_time) - MIN(qp.first_active_time) AS [ElapsedMS]
,MAX(IIF(qp.[close_time] = 0 AND qp.[first_row_time] > 0 ,[physical_operator_name] ,N'<Transition>')) AS [CurrentStep]
FROM sys.dm_exec_query_profiles AS qp
INNER JOIN sys.dm_exec_requests AS der ON der.[session_id] = qp.[session_id]
WHERE qp.[physical_operator_name] IN (N'Table Scan', N'Clustered Index Scan', N'Index Scan', N'Sort')
AND der.[session_id] = ses.[session_id]
AND der.[command] IN ('CREATE INDEX', 'ALTER INDEX', 'ALTER TABLE')
) AS agg
) AS comp
)
) AS [percent_complete]
,er.start_time
,CONVERT(varchar(30) ,CONVERT(TIME ,DATEADD(ss ,DATEDIFF(ss ,er.start_time ,CURRENT_TIMESTAMP) ,0) ,108)) AS [time_ms]
,CONVERT(int ,ROUND(er.total_elapsed_time / 1000.0 ,0)) AS time_s
,COALESCE(
(
SELECT DATEADD(SECOND ,(([ElapsedSeconds] / [RowsProcessed]) * [RowsLeft]) ,GETDATE()) AS [EstimatedCompletionTime]
FROM (
SELECT *
,([TotalRows] - [RowsProcessed]) AS [RowsLeft]
,([ElapsedMS] / 1000.0) AS [ElapsedSeconds]
FROM (
SELECT SUM(qp.[row_count]) AS [RowsProcessed]
,SUM(qp.[estimate_row_count]) AS [TotalRows]
,MAX(qp.last_active_time) - MIN(qp.first_active_time) AS [ElapsedMS]
,MAX(IIF(qp.[close_time] = 0 AND qp.[first_row_time] > 0 ,[physical_operator_name] ,N'<Transition>')) AS [CurrentStep]
FROM sys.dm_exec_query_profiles AS qp
INNER JOIN sys.dm_exec_requests AS der ON der.[session_id] = qp.[session_id]
WHERE qp.[physical_operator_name] IN (N'Table Scan', N'Clustered Index Scan', N'Index Scan', N'Sort')
AND der.[session_id] = ses.[session_id]
AND der.[command] IN ('CREATE INDEX', 'ALTER INDEX', 'ALTER TABLE')
) AS agg
) AS comp
)
,DATEADD(ms ,er.estimated_completion_time ,CURRENT_TIMESTAMP)
) AS estimated_completion_time
,CONVERT(varchar(30) ,CONVERT(TIME ,DATEADD(ms ,er.cpu_time ,0) ,114)) AS cpu_time/*,
'X' -- session id
+ 'S' -- session status
+ 'U' -- user name
+ 'H' -- host name
+ 'A' -- program name
+ 'Q' -- sql statement text
+ 'D' -- database name
+ 'O' -- object name
+ '%' -- percent complete
+ 'T' -- start time
+ 'Z' -- elapsed time and elapsed time in seconds
+ 'E' -- ETA
+ 'C' -- cpu time
+ 'B' -- blocking session
+ 'W' -- wait type
AS capabilities_mask,
-1 AS session_serial*/
,ses.[context_info]
,COALESCE(NULLIF(er.blocking_session_id ,0) ,ses.session_id) AS [devious_plan]
,er.[reads]
,er.[writes]
,er.[logical_reads]
,er.[row_count]
,DB_NAME(ses.[database_id]) + CASE WHEN ses.[database_id] != er.[database_id] THEN ' / ' + DB_NAME(er.[database_id]) ELSE '' END AS [DB]
FROM sys.dm_exec_sessions AS ses
LEFT JOIN sys.dm_exec_requests AS er ON er.session_id = ses.session_id
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) AS st
WHERE ses.session_id > 50 -- non-system processes
AND (ses.status = 'running' OR :FILTER_TYPE = 'ALL') -- show active only
) AS sq1
ORDER BY
(CASE WHEN sq1.[status] = 'running' THEN 0 ELSE 1 END)
,(CASE WHEN sq1.[USER_NAME] = @_current_login THEN 0 ELSE 1 END)
,(CASE WHEN sq1.[blocked_session_ids] IS NOT NULL OR sq1.[blocking_session_id] IS NOT NULL THEN 1 ELSE 0 END) DESC
,[sq1].[blocked_session_ids] DESC
,[sq1].[blocking_session_id] DESC
,[sq1].[wait_time] DESC
,[sq1].[cpu_time] DESC
,[sq1].[time_ms] DESC
,[sq1].[devious_plan]

Thank you. The special CAPABILITIES comment describing the meaning and usage of the columns returned by the query isn't matching the query result. What impact it has on the broken filtering isn't obvious and needs investigating. We will need some time to investigate that.