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.
Brent Ozar says
Scans in that DMV aren’t quite what you think. For example, this will show as a scan even though it only reads a couple of pages:
SELECT TOP 1 * FROM MyBigTable;
Yep…but its a good starting point for investigation. In this case I was running it against an ugly AX database and the numbers returned make sense when compared to execution plans, missing index reports, etc.
Side note, in my head: “Career goal achieved – Brent Ozar commented on my blog!”