SQL Monitor 14 Indexes usages info missing

• 
  i Also found this wich look like what is failing
  
  
  2025-01-29 02:20:03,718 [  122] WARN  RedGate.Monitor.Common.Utilities.Status.StatusLogger - removed: SQL / IndexesSampler : UnreachableError, GroupName: General ActionName: Indexes, ElapsedTime: 1203724

  Microsoft.Data.SqlClient.SqlException: Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

  [CommandText]: '--RedGateIgnore

  SET NOCOUNT ON;

  
  

  CREATE TABLE [##redgate_sqlmonitor_Indexes_gF6x1yNBOEuKgrEU5o9_QNhdrvc](

      index_id integer NOT NULL,

      name nvarchar(450) NOT NULL,

      parent_object_id integer NOT NULL,

      parent_object_name nvarchar(450) NOT NULL,

      database_name nvarchar(450) NOT NULL,

      database_id integer NOT NULL,

      size_used_kb bigint,

      index_type integer NOT NULL,

      fill_factor integer NOT NULL,

      is_unique bit NOT NULL,

      is_filtered bit NOT NULL,

      column_name nvarchar(450),

      column_key_ordinal integer,

      is_column_descending bit,

      is_column_computed bit,

      is_column_included bit

  )

  
  

  DECLARE @errors TABLE ([Message] NVARCHAR(MAX) NOT NULL);

  
  

  DECLARE @database_names TABLE ([Name] NVARCHAR(MAX) NOT NULL);

  
  

  INSERT INTO @database_names

  SELECT [name] from sys.databases d WITH (NOLOCK)

  WHERE d.name NOT IN ('master', 'msdb', 'model', 'tempdb') -- SQL Server system databases

      AND d.name NOT LIKE 'rdsadmin%' -- Amazon RDS system databases

      AND d.state = 0; -- only online databases

  
  

  DECLARE

      @current_database NVARCHAR(MAX),

      @customized_query NVARCHAR(MAX),

      @select_indexes_per_database NVARCHAR(MAX),

      @error_message NVARCHAR(MAX)

  
  

  SET @select_indexes_per_database = N'

      USE [database_name]

      INSERT INTO [##redgate_sqlmonitor_Indexes_gF6x1yNBOEuKgrEU5o9_QNhdrvc]

      SELECT ind.index_id,

         ind.name,

         ind.object_id AS parent_object_id,

         obj.name AS parent_object_name,

         DB_NAME() AS database_name,

         DB_ID() AS database_id,

         istats.size_used_kb,

         ind.type as index_type,

         ind.fill_factor,

         ind.is_unique,

         ind.has_filter AS is_filtered,

         col.name AS column_name,

         ic.key_ordinal AS column_key_ordinal,

         ic.is_descending_key AS is_column_descending,

         col.is_computed AS is_column_computed,

         ic.is_included_column AS is_column_included

  FROM sys.indexes ind WITH (NOLOCK)

      INNER JOIN sys.objects obj WITH (NOLOCK)

          ON ind.object_id = obj.object_id

  LEFT JOIN sys.index_columns ic WITH (NOLOCK)

  ON ind.index_id = ic.index_id

     AND ind.object_id = ic.object_id

  LEFT JOIN sys.columns col WITH (NOLOCK)

  ON ic.column_id = col.column_id

     AND ind.object_id = col.object_id

  OUTER APPLY (SELECT SUM(used_page_count) * 8 AS size_used_kb

               FROM sys.dm_db_partition_stats part WITH (NOLOCK)

               WHERE ind.index_id = part.index_id

                  AND ind.object_id = part.object_id) AS istats

  WHERE ind.type <> 0 AND obj.type NOT IN (''IT'', ''S'')'

  
  

  SET @current_database = (SELECT MIN([Name]) FROM @database_names);

  
  

  WHILE @current_database IS NOT NULL

  BEGIN

      SET @customized_query = REPLACE(@select_indexes_per_database, '[database_name]', QUOTENAME(@current_database));

  
  

      BEGIN TRY

          EXEC(@customized_query);

      END TRY

      BEGIN CATCH

          SET @error_message = N'Failed to run against database "' + @current_database + '". Code: ' + CAST(ERROR_NUMBER() AS nvarchar(10)) + ', Reason: ' + ERROR_MESSAGE() + '.';

          INSERT INTO @errors VALUES(@error_message);

      END CATCH

  
  

      SET @current_database = (SELECT MIN([Name]) FROM @database_names WHERE [Name] > @current_database);

  END

  
  

  SELECT * FROM @errors;

  
  

  SELECT ind.name,

      ind.parent_object_id,

      ind.parent_object_name,

      ind.database_name,

      ind.size_used_kb,

      COALESCE(stats.user_updates, 0) as updates,

      COALESCE(stats.user_seeks, 0) as seeks,

      COALESCE(stats.user_scans, 0) as scans,

      COALESCE(stats.user_lookups, 0) as lookups,

      ind.index_type,

      (SELECT MAX(last_used_options)

           FROM (VALUES (stats.last_user_seek), (stats.last_user_scan), (stats.last_user_lookup)) AS user_actions(last_used_options)) AS last_used,

      stats.last_user_update as last_updated,

      ind.fill_factor,

      ind.is_unique,

      ind.is_filtered,

      ind.column_name,

      ind.column_key_ordinal,

      ind.is_column_descending,

      ind.is_column_computed,

      ind.is_column_included

  FROM [##redgate_sqlmonitor_Indexes_gF6x1yNBOEuKgrEU5o9_QNhdrvc] ind

  LEFT JOIN sys.dm_db_index_usage_stats stats WITH (NOLOCK)

      ON ind.index_id = stats.index_id

          AND ind.parent_object_id = stats.object_id

          AND ind.database_id = stats.database_id'

  [ConnectionString]: 'Data Source=removed;Initial Catalog=tempdb;Integrated Security=True;Connect Timeout=15;Encrypt=False;Trust Server Certificate=False;Packet Size=4096;Application Name="Redgate Monitor - Monitoring - 2cfd8a47-7d8a-4d6e-850e-0a77f14d0f25";Application Intent=ReadOnly'

  [Database]: 'tempdb'

  [ExecutionDuration]: '00:05:00.0480100'

  [HelpLink.BaseHelpUrl]: 'https://go.microsoft.com/fwlink'

  [HelpLink.EvtID]: '-2'

  [HelpLink.EvtSrc]: 'MSSQLServer'

  [HelpLink.LinkId]: '20476'

  [HelpLink.ProdName]: 'Microsoft SQL Server'

  [HelpLink.ProdVer]: '14.00.3485'

  [Error]: {

    [Class]: '11'

    [LineNumber]: '0'

    [Message]: 'Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.'

    [Number]: '-2'

    [Procedure]: ''

    [Server]: 'removed'

    [Source]: 'Core Microsoft SqlClient Data Provider'

    [State]: '0'

  }

     at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()

     at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)

  --- End of stack trace from previous location ---

     at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)

     at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)

  --- End of stack trace from previous location ---

     at StackExchange.Profiling.Data.ProfiledDbCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken) in C:\projects\dotnet\src\MiniProfiler.Shared\Data\ProfiledDbCommand.cs:line 235

     at RedGate.Monitor.Common.Utilities.SqlServer.Profiling.ProfilingSqlCommand.LogCommandTextOnExceptionAsync[T](Func`1 func, Func`2 suppressLogException)

     at RedGate.Monitor.Engine.Monitoring.Core.Communicators.SqlSampleCommandReader.ReadErrorsAndSamplesFromCommandAsync(DbCommand cmd, IEnumerable`1 properties, IEnumerable`1 propertyReaders, ILog log)

     at RedGate.Monitor.Engine.Monitoring.Core.Communicators.SqlServers.SqlResultSetSampler`1.ExecuteDatabasesQueryAsync(IStatusLogger statusLogger, DbCommand cmd, TState state, IReadOnlyCollection`1 properties)

     at RedGate.Monitor.Engine.Monitoring.Core.Communicators.SqlServers.SqlServerCommandExecutor.<>c__DisplayClass13_0`1.<<ExecuteSqlAsync>b__0>d.MoveNext()

  --- End of stack trace from previous location ---

     at RedGate.Monitor.Engine.Monitoring.Core.Communicators.SqlServers.SqlServerCommandExecutor.<>c__DisplayClass13_0`1.<<ExecuteSqlAsync>b__0>d.MoveNext()

  --- End of stack trace from previous location ---

     at RedGate.Monitor.Engine.Monitoring.Core.Communicators.SqlServers.SqlServerCommandExecutor.<>c__DisplayClass13_0`1.<<ExecuteSqlAsync>b__0>d.MoveNext()

  --- End of stack trace from previous location ---

     at Polly.ResiliencePipeline.<>c__10`1.<<ExecuteAsync>b__10_0>d.MoveNext()

  --- End of stack trace from previous location ---

     at Polly.Outcome`1.GetResultOrRethrow()

     at Polly.ResiliencePipeline.ExecuteAsync[TResult](Func`2 callback, CancellationToken cancellationToken)

     at RedGate.Monitor.Common.Utilities.Status.StatusLogger.TryLogAsync[T](String groupName, String actionName, Func`1 action, Boolean logSuccess)

  Inner exception:

     System.ComponentModel.Win32Exception: The wait operation timed out.

  End of inner exception:

  
  

  Viddi January 31, 2025 09:01

  - 0 +
• 

  What version of Redgate Monitor 14 are you using? 

  Josh Head February 18, 2025 18:08

  - 0 +

Add comment

Please sign in to leave a comment.