Rss

  • linkedin

Find Recent Full Table Scans in MS SQL Server

Created a helpful query which will display any Full Table scans in Microsoft SQL Server in the last 24 hours.

Thought I would share: 

SELECT
    ISNULL(i.name, ‘Table’) AS IndexName,
    ISNULL(SO.name, ”) AS TableName,
    s.used_page_count * 8 AS IndexSizeKB,
    s.row_count,
    sis.user_seeks,
    sis.user_scans,
    sis.user_lookups,
    sis.user_updates,
    sis.last_user_scan

FROM
    sys.dm_db_index_usage_stats AS sis

JOIN
    sys.objects AS SO
    ON SO.object_id = SIS.object_id

LEFT JOIN
    sys.indexes AS i
    ON i.object_id = sis.object_id
    AND i.index_id = sis.index_id

JOIN
    sys.dm_db_partition_stats AS S
    ON S.object_id = sis.object_id
    AND S.index_id = sis.index_id
   
WHERE
    sis.database_id = DB_ID() AND
    sis.last_user_scan > DATEADD(DAY,-1,GETDATE()) AND
    i.name is null AND
    SO.type = ‘U’ AND
    SO.is_ms_shipped = 0 AND
    sis.user_scans > 0

ORDER BY
    sis.last_user_scan DESC

Leave a Reply