Impact of FULL Scans in your SQL Server DB

Full scans of a HEAP or Clustered index in SQL Server aren’t necessarily a bad thing.  But sometimes they are scanned because the Query Optimizer (“Optimus” here at Dallas DBAs HQ) cannot find a suitable index to use.  Sometimes its just because the table is tiny.  Either way, its worth investigating WHY all the pages are being read…especially if you are having performance issues or memory pressure.

Just for fun, I took the query found in this DBA StackExchange Answer and modified it to fit a client’s need.   Decided to share:

-- base query provided by Jonathan Fite:
-- https://dba.stackexchange.com/questions/210341/number-of-full-table-scans
-- Added sys.Partitions for rowcount, Imapct computed column, Where conditions and Sorting
-- Kevin3NF, https://dallasdbas.com

SELECT S.name AS SchemaName
  , O.name AS ObjectName
  , I.name AS IndexName
  , I.type_desc AS IndexType_Desc
  , IUS.last_user_scan
  , IUS.user_scans
  , P.rows
  , IUS.user_scans * P.rows as [Impact]
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.index_id = I.index_id AND IUS.object_id = I.object_id
  INNER JOIN sys.Partitions P on p.object_id = I.object_id and p.index_id = i.index_id
WHERE 1=1 
  and I.type IN (0,1) --0=HEAP, 1=CLUSTERED (table)
  and IUS.user_scans > 5
  and p.rows > 1000

Order by 
--IUS.user_scans desc
--p.Rows Desc
IUS.user_scans * P.rows DESC

The top rows are very likely good candidates for an index review.

Edit as you see fit for your environment, especially the WHERE clause. Please leave the headers.

Thanks for reading.

Kevin3NF