FinOps Index Analysis times out — sp_IndexCleanup hangs when dashboard login lacks user mapping in target database

[AWH-Shane-DBA]

Component

Full Dashboard

Performance Monitor Version

2.9.0

SQL Server Version

Microsoft SQL Server 2019 (RTM-CU32-GDR) (KB5077469) - 15.0.4460.4 (X64) Feb 13 2026 17:00:40 Copyright (C) 2019 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2022 Datacenter 10.0 (Build 20348: ) (Hypervisor)

Windows Version

Windows Server 2022 Datacenter

Describe the Bug

FinOps
Index Analysis for one Database
Click Run Analysis

After waiting for some time get "Error: Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding."

In PerformanceMonitor log:

[ERROR] Error running index analysis: Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Exception: SqlException
Message: Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
StackTrace: at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, SqlCommand command, Boolean callerHasConnectionLock, Boolean asyncClose)
at Microsoft.Data.SqlClient.SqlCommand.InternalEndExecuteReader(IAsyncResult asyncResult, Boolean isInternal, String endMethod)
at Microsoft.Data.SqlClient.SqlCommand.EndExecuteReaderInternal(IAsyncResult asyncResult)
at Microsoft.Data.SqlClient.SqlCommand.EndExecuteReaderAsync(IAsyncResult asyncResult)
at System.Threading.Tasks.TaskFactory1.FromAsyncCoreLogic(IAsyncResult iar, Func2 endFunction, Action1 endAction, Task1 promise, Boolean requiresSynchronization)
--- End of stack trace from previous location ---
at PerformanceMonitorDashboard.Services.DatabaseService.RunIndexAnalysisAsync(String databaseName, Boolean getAllDatabases)
at PerformanceMonitorDashboard.Services.DatabaseService.RunIndexAnalysisAsync(String databaseName, Boolean getAllDatabases)
at PerformanceMonitorDashboard.Controls.FinOpsContent.RunIndexAnalysis_Click(Object sender, RoutedEventArgs e)

Steps to Reproduce

1. Open Dashboard
2. Select FinOps
3. Select Index Analysis
4. Add Database I know exists and click Run Analysis

Expected Behavior

Return list of indexes.

Actual Behavior

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

Error Messages / Log Output

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

[ERROR] Error running index analysis: Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
Exception: SqlException
Message: Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
StackTrace:    at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, SqlCommand command, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.SqlCommand.InternalEndExecuteReader(IAsyncResult asyncResult, Boolean isInternal, String endMethod)
   at Microsoft.Data.SqlClient.SqlCommand.EndExecuteReaderInternal(IAsyncResult asyncResult)
   at Microsoft.Data.SqlClient.SqlCommand.EndExecuteReaderAsync(IAsyncResult asyncResult)
   at System.Threading.Tasks.TaskFactory`1.FromAsyncCoreLogic(IAsyncResult iar, Func`2 endFunction, Action`1 endAction, Task`1 promise, Boolean requiresSynchronization)
--- End of stack trace from previous location ---
   at PerformanceMonitorDashboard.Services.DatabaseService.RunIndexAnalysisAsync(String databaseName, Boolean getAllDatabases)
   at PerformanceMonitorDashboard.Services.DatabaseService.RunIndexAnalysisAsync(String databaseName, Boolean getAllDatabases)
   at PerformanceMonitorDashboard.Controls.FinOpsContent.RunIndexAnalysis_Click(Object sender, RoutedEventArgs e)

Screenshots

Additional Context

Initial Proof of Concept test of the monitor.

If I put in a database name that does not exist, it correctly reports this as an error:

[ERROR] Error running index analysis: No valid databases found to process.
Exception: SqlException
Message: No valid databases found to process.
StackTrace: at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at Microsoft.Data.SqlClient.Connection.SqlConnectionInternal.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction)
at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, SqlCommand command, Boolean callerHasConnectionLock, Boolean asyncClose)
at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at Microsoft.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
at Microsoft.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
at Microsoft.Data.SqlClient.SqlCommand.CompleteAsyncExecuteReader(Boolean isInternal, Boolean forDescribeParameterEncryption)
at Microsoft.Data.SqlClient.SqlCommand.InternalEndExecuteReader(IAsyncResult asyncResult, Boolean isInternal, String endMethod)
at Microsoft.Data.SqlClient.SqlCommand.EndExecuteReaderInternal(IAsyncResult asyncResult)
at Microsoft.Data.SqlClient.SqlCommand.EndExecuteReaderAsync(IAsyncResult asyncResult)
at System.Threading.Tasks.TaskFactory1.FromAsyncCoreLogic(IAsyncResult iar, Func2 endFunction, Action1 endAction, Task1 promise, Boolean requiresSynchronization)
--- End of stack trace from previous location ---
at PerformanceMonitorDashboard.Services.DatabaseService.RunIndexAnalysisAsync(String databaseName, Boolean getAllDatabases)
at PerformanceMonitorDashboard.Services.DatabaseService.RunIndexAnalysisAsync(String databaseName, Boolean getAllDatabases)
at PerformanceMonitorDashboard.Controls.FinOpsContent.RunIndexAnalysis_Click(Object sender, RoutedEventArgs e)

[erikdarlingdata]

@AWH-Shane-DBA this runs sp_IndexCleanup in the background. Can you confirm which version of that you’ve got installed and are executing?

I’ve made some improvements to that recently that brought down execution time significantly. The newest one should be in the PerformanceMonitor database, pulled down during install. But if that didn’t happen for some reason, it may be pointing to either what’s in the master database, or in a utility database that you’ve specified.

If you’re on the most recent version already (and that’s what’s being called), can you give it a run for the database you’re looking at in SSMS? If it’s slow there too, we’ll have to dig in to why.

Thanks!

[AWH-Shane-DBA]

Thanks, I am on 2.9.0 that I loaded this morning.

I can directly run sp_IndexCleanup on the one server and get a result for that database in 4 seconds. On another server is has been running for 10 minutes and still going.

When running from the dashboard and check on the first server, I cannot see anything running and it times out.

I am using SQL auth for the Dashboard connection and just checked running sp_IndexCleanup as that user and the stored procedure also does not complete. I set the permissions as:

USE [master];
CREATE LOGIN [SQLServerPerfMon] WITH PASSWORD = N'YourStrongPassword';
GRANT VIEW SERVER STATE TO [SQLServerPerfMon];

USE [PerformanceMonitor];
CREATE USER [SQLServerPerfMon] FOR LOGIN [SQLServerPerfMon];
ALTER ROLE [db_owner] ADD MEMBER [SQLServerPerfMon];

USE [msdb];
CREATE USER [SQLServerPerfMon] FOR LOGIN [SQLServerPerfMon];
ALTER ROLE [SQLAgentReaderRole] ADD MEMBER [SQLServerPerfMon];

What additional permissions are required to allow sp_IndexCleanup to run on a database?

[erikdarlingdata]

@AWH-Shane-DBA we reproduced this locally and tracked it down to a permissions issue inside sp_IndexCleanup — specifically how sys.dm_db_partition_stats behaves when accessed via a three-part name ([YourDb].sys.dm_db_partition_stats) by a login that has no user mapping in the target database.

Every other catalog view and DMV the procedure references (sys.indexes, sys.objects, sys.partitions, sys.allocation_units, sys.dm_db_index_usage_stats, etc.) raises Msg 916 instantly when the principal can't access the target DB. sys.dm_db_partition_stats is the odd one out — instead of erroring, it spins in a CPU-bound loop with no waits and never returns. The bigger the database's metadata, the longer the hang. That's why a database that ran fine in 4 seconds for you in SSMS (where you were authenticated as yourself) timed out from the dashboard, where the connection runs as SQLServerPerfMon. We confirmed the same behavior on SQL Server 2016 SP3 through 2025 CU4.

The fix — for each user database the dashboard's login will analyze, map the login as a user and grant VIEW DATABASE STATE (and VIEW DEFINITION):

USE [YourTargetDatabase];

CREATE USER [SQLServerPerfMon] FOR LOGIN [SQLServerPerfMon];
GRANT VIEW DATABASE STATE TO [SQLServerPerfMon];
GRANT VIEW DEFINITION    TO [SQLServerPerfMon];

After that, sp_IndexCleanup completes for us in well under a second on the same server/database that was hanging, and the dashboard's Index Analysis returns results normally. Repeat per database (or use sp_MSforeachdb if you'd rather grant it broadly).

We're going to:

1. Update the install/permission docs to call this out, since the existing grants on master, PerformanceMonitor, and msdb aren't sufficient for the FinOps Index Analysis.
2. Look at adding a HAS_DBACCESS() preflight inside sp_IndexCleanup so it skips (with a clear warning) instead of hanging when the executing principal has no access to a database in scope.
3. Separately follow up with Microsoft about sys.dm_db_partition_stats — the silent-hang behavior isn't documented anywhere we could find, so we want to confirm whether it's a bug or a docs gap.

Thanks for the thorough repro details, that's what made this trackable. Going to leave this issue open until items 1 and 2 above are merged.