SQL Monitor 14 Indexes usages info missing

Viddi · January 29, 2025 1:32PM

I have sql Monitor 14

I´m Monitoring 2 sql servers

I have enabeld the index option for both servers

I get the index info for 1 server the other shows no data.

I hve this warning in the logs

2025-01-10 20:49:27,609 [ 44] WARN RedGate.SqlMonitor.Engine.Monitoring.Core.Services.Auth.ActiveDirectory.SqlServerActiveDirectoryConfigRepository - Failed to load credentials for domain "domain name removed" and no old-format entry was found. No Credential Manager entries were found for SQL_Monitor_AD_ServiceAccount_mp.is or SQL_Monitor_AD_ServiceAccount.

2025-01-10 20:49:36,390 [ 39] WARN RedGate.SqlMonitor.SearchEngine.SqlQueries.SqlQueryIndexApi - Unknown exception returned while processing Lucene index operation

Viddi · January 31, 2025 9:01AM

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)