Remote scan on local server

• scarr030

  SSCommitted

  Points: 1699

  More actions

  October 24, 2018 at 7:14 am

  #372286

  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


• Eddie Wuerch

  SSChampion

  Points: 12541

  More actions

  October 24, 2018 at 11:41 am

  #2010980

  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