Schema object name is shown as the batch name instead of TSQL in SQL PI (4226624)

In SQL PI, when viewing collected SQL batches, the batch name typically reflects the nature of the SQL:

• Ad-hoc batches are labeled as "ad-hoc batch".
• System procedures are labeled as "system procedure".
• Stored procedures or schema-bound objects display the schema-qualified object name, such as dbo.C_SP_Stock_Level;1.

However, in some cases, the batch name appears as a numeric object reference, such as:

Object -6:1671677003;1

This format indicates that SQL PI was unable to resolve the object name at the time of collection.

This issue usually occurs when the Foglight monitoring user lacks sufficient permissions to access the database or object during data collection. Common scenarios include:

• The monitored database is a restored replica from another SQL Server instance.
• The foglightuser login exists, but the SID does not match the database user.
• The user mapping is broken, preventing SQL PI from resolving object metadata.

As a result, SQL PI falls back to displaying the internal object reference format:
Object <dbid>:<object_id>;1

Step 1: Fix Permissions

Ensure the foglightuser has access to the affected database:

Option A: Re-run the Grant Permissions Script

Use the standard Foglight permissions script to reassign roles and access.

Option B: Drop and Recreate the User in the Restored Database

If the database was restored from another instance:

USE [YourDatabaseName];
DROP USER [foglightuser];
CREATE USER [foglightuser] FOR LOGIN [foglightuser];

This remaps the user to the correct login SID.

Step 2: Identify the Unresolved Batch

Look for batch names in SQL PI like:

Object 6:1671677003;1

Extract:

• dbid = 6
• object_id = 1671677003

Step 3: Retrieve the SQL Handle

Run this query on the monitored SQL Server instance:

SELECT 
    qs.sql_handle, 
    st.text 
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
WHERE st.objectid = 1671677003 AND st.dbid = 6;

Copy the sql_handle from the result.

Step 4: Preview the rows to delete from the SQL PI Repository

Connect to the SQL PI repository using Toad or SSMS.

DECLARE @i INT = 0;
DECLARE @sql NVARCHAR(MAX);
DECLARE @sql_handle NVARCHAR(128) = 'your_sql_handle_here';  -- Replace with actual sql_handle
DECLARE @table_name NVARCHAR(100);
DECLARE @row_count INT;

-- Table to store preview results

IF OBJECT_ID('tempdb..#DeletePreview') IS NOT NULL DROP TABLE #DeletePreview;
CREATE TABLE #DeletePreview (
    table_name NVARCHAR(100),
    rows_to_delete INT
);

WHILE @i < 30
BEGIN
    SET @table_name = 'pass_batch_dim_p' + CAST(@i AS VARCHAR(2));
    SET @sql = '
        INSERT INTO #DeletePreview (table_name, rows_to_delete)
        SELECT ''' + @table_name + ''', COUNT(*) 
        FROM ' + QUOTENAME(@table_name) + '
        WHERE sql_handle = @sql_handle;';

    EXEC sp_executesql @sql, N'@sql_handle NVARCHAR(128)', @sql_handle;
    SET @i = @i + 1;
END

-- Show summary of rows that would be deleted
SELECT * FROM #DeletePreview
ORDER BY rows_to_delete DESC;

Replace <sql_handle> with the actual value retrieved in Step 3.

Step 5: Delete the Unresolved Entry from SQL PI Repository

Connect to the SQL PI repository using Toad or SSMS.

DECLARE @i INT = 0;
DECLARE @sql NVARCHAR(MAX);
DECLARE @sql_handle NVARCHAR(128) = 'your_sql_handle_here';  -- Replace with actual sql_handle
DECLARE @table_name NVARCHAR(100);

-- Table to store log results
IF OBJECT_ID('tempdb..#DeleteLog') IS NOT NULL DROP TABLE #DeleteLog;
CREATE TABLE #DeleteLog (
    table_name NVARCHAR(100),
    rows_deleted INT
);

WHILE @i < 30
BEGIN
    SET @table_name = 'pass_batch_dim_p' + CAST(@i AS VARCHAR(2));

    SET @sql = '
        DECLARE @count INT;
        DELETE FROM ' + QUOTENAME(@table_name) + '
        WHERE sql_handle = @sql_handle;
        SET @count = @@ROWCOUNT;
        INSERT INTO #DeleteLog (table_name, rows_deleted) VALUES (''' + @table_name + ''', @count);';

    EXEC sp_executesql @sql, N'@sql_handle NVARCHAR(128)', @sql_handle;

    SET @i = @i + 1;
END

-- Show summary of deletions
SELECT * FROM #DeleteLog
ORDER BY rows_deleted DESC;

Step 6: Allow Re-Aggregation

After deletion, SQL PI will reprocess the batch from the 1-minute tables (pass_batch_dim_1m_*) during the next collection cycle. If permissions are now correct, the batch name will be resolved properly.

Preventing Future Issues

• Always ensure the monitoring user has access to all monitored databases.
• After restoring a database, remap the user to the login using CREATE USER FOR LOGIN.
  

  USE [RestoredDB];
  DROP USER [foglightuser];
  CREATE USER [foglightuser] FOR LOGIN [foglightuser];

• Consider automating a check for orphaned users using sp_change_users_login or ALTER USER ... WITH LOGIN.