4

I would like to see information on how many times has a table been fully scanned. I was hoping for something like sys.dm_db_index_usage_stats but for table scans. There is a method described at the blog keepitsimleandfast, but I would rather get all scans since server started.

What I am looking for is also similiar to pg_stat_all_tables.seq_scan in PostgreSQL.

Improve this question
7

Well, that table will give you the answer you are looking for. Just join onto sys.indexes to determine the type of index. Tables are either Heaps or Clustered indexes, so a scan on that index type is a scan on the table. See the example below, add any additional columns you are looking for.

SELECT S.name AS SchemaName
    , O.name AS ObjectName
    , I.name AS IndexName
    , I.type AS IndexType
    , I.type_desc AS IndexType_Desc
    , IUS.user_scans
FROM sys.objects AS O 
    INNER JOIN sys.schemas AS S ON S.schema_id = O.schema_id
    INNER JOIN sys.indexes AS I ON I.object_id = O.object_id
    INNER JOIN sys.dm_db_index_usage_stats AS IUS ON IUS.object_id = I.object_id AND IUS.index_id = I.index_id
WHERE I.type IN (0,1) -- 0=HEAP, 1=CLUSTERED (table)
Improve this answer
  • After checking it seems you're right, thank you. Just add AND IUS.object_id = I.object_id in last join – dreptak Jun 22 '18 at 13:22
  • Great query...just for fun I added in sys.partitions for rowcounts and (scans * rows) for measuring impact. – Kevin3NF Jun 22 '18 at 14:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.