SQL monitor causing CPU pressure in Azure SQL Managed Instance

JoonaHook

Hi,

We are using latest SQL monitor and Managed Instance. SQL monitor repository is on a different single Azure Database. We are seeing SQL Monitor queries causing continuous 100% Cpu load on the instance.

I've checked executing queries waiting for CPU and there were always same SQL monitor queries using CPU. After I killed all these sessions CPU pressure dropped to normal picking up soon after. Only after I suspended monitoring instance became stable. I wonder is there configuration issue due to SQL monitor handling MI hosted databases as independent databases with own resources? I think it should treat MI as an instance same as OnPrem.

Most expensive query for example is:

WITH block_cte AS  (      SELECT session_id, CAST(0 AS BIGINT) [wait_time], session_id [root_spid]      FROM @proc     WHERE blocking_session_id = 0      UNION ALL      SELECT blocked.session_id, blocked.wait_time, block_cte.[root_spid]      FROM @proc AS blocked      INNER JOIN block_cte ON blocked.blocking_session_id = block_cte.session_id AND blocked.blocking_session_id <> blocked.session_id  ),    block_aggregate_cte AS (      SELECT [root_spid], SUM(wait_time) [total_blocking_time]      FROM block_cte      GROUP BY [root_spid]  ),    sysprocess_cte AS  (      SELECT          sp.session_id [SessionId],          sp.cpu_time [Cpu],          sp.reads [Reads],          sp.writes [Writes],          sp.logical_reads [LogicalReads],          sp.memory_usage [MemoryUsage],          sp.login_time [LoginTime],          sp.login_name [LoginName],          sp.last_request_start_time [LastRequestStartTime],          sp.last_request_end_time [LastRequestEndTime],          sp.status [Status],          sp.host_name [HostName],          sp.program_name [ProgramName],          er.open_transaction_count [OpenTransactions],          er.blocking_session_id [BlockingSessionId],          erb.login_time [BlockingSessionLoginTime],          ba.total_blocking_time [TotalBlockingTime],          er.wait_time [WaitTime],          er.wait_type [WaitType],          er.last_wait_type [LastWaitType],          er.wait_resource [WaitResource],          DATEDIFF(s, er.start_time, GETDATE()) [TotalElapsedTime],          DB_NAME(er.database_id) [DatabaseName],          er.command [Command],          er.sql_handle [SqlHandle],          er.statement_start_offset [StatementStartOffset],          er.statement_end_offset [StatementEndOffset],          er.start_time [QueryStartTime],          st.[text] [SqlText],          ec.most_recent_sql_handle [MostRecentSqlHandle],          stm.[text] [MostRecentSqlText],          NULL [FullCommand],          er.plan_handle [PlanHandle]      FROM sys.dm_exec_sessions sp      LEFT JOIN block_aggregate_cte ba ON ba.root_spid =  sp.session_id      LEFT JOIN sys.dm_exec_requests er ON sp.session_id = er.session_id      LEFT JOIN sys.dm_exec_sessions erb ON er.blocking_session_id = erb.session_id      LEFT JOIN sys.dm_exec_connections ec ON sp.session_id = ec.session_id      OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st      OUTER APPLY sys.dm_exec_sql_text(ec.most_recent_sql_handle) stm      WHERE sp.is_user_process = 1 AND ((er.sql_handle IS NOT NULL AND er.sql_handle > 0x0 AND DB_NAME(er.database_id) IS NOT NULL) OR ba.total_blocking_time > 0) AND CHARINDEX('RedGateIgnore', ISNULL(st.[text], '')) = 0  )    SELECT p.SessionId,         p.Cpu,         p.Reads,         p.Writes,         p.LogicalReads,         p.MemoryUsage,         p.LoginTime,         p.LoginName,         p.LastRequestStartTime,         p.LastRequestEndTime,         p.Status,         p.HostName,         p.ProgramName,         p.OpenTransactions,         p.BlockingSessionId,         p.BlockingSessionLoginTime,         p.TotalBlockingTime,         p.WaitTime,         p.WaitType,         p.LastWaitType,         p.WaitResource,         p.DatabaseName,         p.Command,         p.SqlHandle,         p.StatementStartOffset,         p.StatementEndOffset,         p.QueryStartTime,         p.SqlText,         p.MostRecentSqlHandle,         p.MostRecentSqlText,         p.FullCommand,         p.PlanHandle FROM (  SELECT    ROW_NUMBER() OVER(ORDER BY p.Cpu DESC) CpuRank,    ROW_NUMBER() OVER(ORDER BY p.TotalElapsedTime  DESC) DurationRank,    *  FROM sysprocess_cte p) AS p  WHERE    (p.CpuRank <= 10 AND p.Cpu > 0) OR    (p.DurationRank <= 10 AND p.TotalElapsedTime > 10) OR    p.TotalBlockingTime > 0 OR p.BlockingSessionId > 0

Mac_F

Hi Joona,

I am sorry to see you have this issue with SQL Monitor. As you are a supported customer, we have opened a ticket under your email address so we can troubleshoot this further. 
Would you please respond to that open ticket with the version you are using and the log files from SQL Monitor?

Thanks,

JoonaHook

Hi, just an update on this. The issue hasn't been fixed to this date. Any production level load will cause SQLMonitor to use 100% CPU of the Managed Instance. This is due to fundamental design issue according to RedGate support. No estimated fix date has been promised. It looks like MI is marginal flavor of Azure SQL and such not an priority.

TJamesCaldwell

Just to confirm , I also have this problem with SQL Mon and Azure SQL Managed Instances.
we have had to suspend monitoring on our SQL MIs

really hope a fix / new version is released soon!