0

Is there a way I can identify all objects with over 10K rows and no index?

3
  • 1
    @Martin Smith , I need to create a sql agent job that captures tables over 10K records with no index. As you likely say this is for performance and also i'd like to get an idea of the tables sizes and possibly applying indexes if needed be as our analystical team are running some expensive queries(via linked servers) Note this for an analytical / data warehouse environment .
    – Daniel
    May 15, 2020 at 14:49
  • yeah, originally I thought you were asking how you would go about producing tables that had over 10K rows and no index (which is of course only too easy hence my confusion). I replaced "get" with "identify" for clarity May 15, 2020 at 14:56
  • Linked servers have some non-intuitive performance considerations. While applying appropriate indexes will certainly help, it may not be the cause of performance issues, especially if they are joining local tables with remote tables. May 15, 2020 at 15:36

1 Answer 1

1

I would use this query to find that out. The rowcount for Non-Clustered is the sum of ALL non-clustered indexes on the table, but the heap/clustered count is accurate.

;WITH CTE_RowCount AS
    (
    SELECT S.[name] AS SchemaName
        , O.[name] AS ObjectName 
        , I.type_desc AS IndexType
        , SUM(P.[rows]) AS [RowCount]
    FROM sys.partitions AS P
        INNER JOIN sys.objects AS O ON O.object_id = P.object_id 
        INNER JOIN sys.schemas AS S ON S.schema_id = O.schema_id
        INNER JOIN sys.indexes AS I ON I.object_id = P.object_id AND I.index_id = P.index_id
    GROUP BY S.[name], O.[name], I.[type_desc]
    )
SELECT *
FROM CTE_RowCount AS R
    PIVOT (SUM(R.[RowCount]) FOR IndexType IN ([HEAP], [CLUSTERED], [NONCLUSTERED])) AS Pvt
WHERE COALESCE(Pvt.[HEAP], Pvt.[CLUSTERED]) >= 10000
    --AND Pvt.[CLUSTERED] IS NULL -- Will only show if table is a HEAP
    --AND Pvt.[NONCLUSTERED] IS NULL --will only show if there are no non-clustered indexes.
0

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.