October 24, 2018 at 7:14 am
Hi all,
I was running the below query on our server (there are not linked/remote servers; this is the only one) for one of our DBs, and it was horrendously slow. I took a look at the execution plan and noticed a Remote Scan. A quick look at BoL states that it's a scan on another server, but that can't be the case here, right? Any idea why this is happening?
SELECT TOP 50
T.name as 'Table',
I.name as 'Index',
PS.avg_fragmentation_in_percent,
PS.page_count
FROM sys.dm_db_index_physical_stats (94, NULL, NULL, NULL, NULL) AS PS
INNER JOIN sys.tables T
on T.[object_id] = PS.[object_id]
INNER JOIN sys.indexes AS I
ON I.[object_id] = Ps.[object_id]
AND PS.index_id = I.index_id
WHERE PS.database_id = 94
AND PS.avg_fragmentation_in_percent >= 30
AND PS.page_count >= 4
October 24, 2018 at 11:41 am
Look at the Remote Object name in the Remote Scan. It's an object called 'IndexAnalysis'. It's not an actual table, but a chunk of memory.
You're using a DMF to access an internal object that isn't a table. Many DMVs and DMFs will use OLEDB to separately connect to the server to gather data before shredding it and formatting it into a rowset output. (This is why servers that aren't running remote/linked/openquery queries still record OLEDB waits) The Remote Scan in your query is that work.
It's not a remote object, but the mechanics of accessing it appear that way.
Eddie Wuerch
MCM: SQL
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply