October 30, 2020 at 6:33 pm
I'm running an index maintenance script on the MSDB database that loops through a table of indexes with fragmentation over 10% and right after applying Security Update for SQL Server 2016 Service Pack 1 GDR (KB4505219)(0000)(x64)(en), the script started generating an error at this point:
ALTER INDEX [pk_MSdbms_map] ON [dbo].[ExternalMailQueue] REBUILD
Msg 1914, Level 16, State 3, Line 1
Index cannot be created on object 'dbo.ExternalMailQueue' because the object is not a user table or view.
Get the object_id:
SELECT name, object_id, type_desc, create_date, modify_date from sys.objects WHERE name = 'ExternalMailQueue';
ExternalMailQueue 1338487847 SERVICE_QUEUE 2019-09-06 17:43:01.200 2019-09-06 17:43:05.027
The creation date corresponds to the date/time when the patch was applied.
Here is the section of code that generates the object IDs with fragmentation. I'm honing in on the ExternalMailQueue object:
SELECT object_id, index_id, partition_number, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (4, 1338487847, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;
1338487847 1 1 31.5789473684211
1338487847 2 1 80
But if I query sys.indexes against the ExternalMailQueue:
select object_id, name, index_id, type, type_desc from sys.indexes where object_id = 1338487847
it returns NULL
It was obvious then that the "pk_MSdbms_map" index value in the ALTER INDEX [pk_MSdbms_map] ON [dbo].[ExternalMailQueue] REBUILD statement was left over from the previous loop iteration.
Querying sys.db_db_index_operational_stats gives a potential clue:
SELECT index_id, range_scan_count, singleton_lookup_count
FROM sys.dm_db_index_operational_stats (4, 1338487847, NULL, NULL)
Msg 2524, Level 16, State 1, Line 1
Cannot process object ID 1338487847 (object "ExternalMailQueue") because it is a Service Broker queue. Try the operation again with the object ID of the corresponding internal table for the queue, found in sys.internal_tables.
SELECT @@VERSION
Microsoft SQL Server 2016 (SP1-GDR) (KB4505219) - 13.0.4259.0 (X64) Jun 15 2019 19:20:12 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) (Hypervisor)
I know I need to apply SP2 and some cumulative updates but I would love to know if anyone has an explanation for this before I go down that path.
October 31, 2020 at 7:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
November 2, 2020 at 5:26 pm
I could be mistaken, but I am 99% sure that the problem is that a QUEUE is not the same thing as a TABLE.
I just checked that queue on my system and I have 2 indexes on it - one clustered, one nonclustered (obviously). Since these are objects created by SQL Server ("is_ms_shipped" is 1) and they are not tables (type_desc is SERVICE_QUEUE), I would exclude them in your index maintenance task.
I would also update your query so you are looking at tables that have a decent number of rows. If you have 10 pages and a fragmentation of 10%, that is not a lot of fragmentation and any benefits you MAY get from defragmenting indexes will be lost.
I checked that table on one of my test SQL instances that is running Microsoft SQL Server 2016 (SP2-GDR) (KB4505220) - 13.0.5101.9 (X64) Jun 15 2019 23:15:58 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) . Looking at that I am in need of some updates too. But updating to SP2 isn't going to fix that query for you - you are still going to have issues trying to defragment an index on a queue.
I would start by looking at your index maintenance script to see where it is getting the tables from and go from there. You want to make sure that script is just grabbing tables (and likely only user tables, not system tables) when it is getting the list of indexes to rebuild/reogranize.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
November 12, 2020 at 6:42 pm
Issue was resolved by after updating to SP2 and applying CU15. sys.dm_db_index_physical_stats behaving correctly now.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply