Identifying Table Structures

Understanding the distinction between heap tables and those with clustered indexes is fundamental for performance tuning. This can be achieved by inspecting the index_id property within system partition views.

-- Retrieve table structure type based on index identifier
SELECT 
    t.name AS TableName,
    i.type_desc AS IndexTypeDesc,
    CASE 
        WHEN i.index_id = 0 THEN 'Heap Table'
        WHEN i.index_id = 1 THEN 'Clustered Index Table'
    END AS StructureClassification
FROM sys.partitions p
JOIN sys.tables t ON p.object_id = t.object_id
JOIN sys.indexes i ON p.object_id = i.object_id AND p.partition_id = i.partition_id
WHERE p.index_id < 2;

Alternatively, query the index catalog directly where heap names are typically null:

SELECT 
    OBJECT_NAME(i.object_id) AS TableName,
    i.name AS IndexName,
    i.type_desc AS Classification
FROM sys.indexes i
WHERE i.index_id <= 1;

Comprehensive Index Inventory

Generating a report of all database indexes, including column composition and uniqueness attributes, assists in schema review and missing index analysis.

SELECT 
    T.schema_name + '.' + T.object_name AS FullyQualifiedObjectName,
    I.index_name AS IndexDisplayName,
    STUFF((
        SELECT ',' + C.column_name
        FROM sys.index_columns IC
        JOIN sys.columns C ON IC.object_id = C.object_id AND IC.column_id = C.column_id
        WHERE IC.index_id = I.index_id AND IC.object_id = I.object_id
        ORDER BY IC.key_ordinal
        FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '') AS IncludedColumns,
    CASE I.type 
        WHEN 1 THEN 'Clustered' 
        WHEN 2 THEN 'Non-Clustered' 
        WHEN 3 THEN 'XML' 
        WHEN 4 THEN 'Spatial' 
        ELSE 'Other' 
    END AS IndexCategory,
    CASE I.is_unique 
        WHEN 1 THEN 'Unique' 
        ELSE 'Non-Unique' 
    END AS UniquenessStatus
FROM sys.objects O
JOIN sys.schemas S ON O.schema_id = S.schema_id
JOIN sys.indexes I ON O.object_id = I.object_id
CROSS APPLY (
    SELECT SCHEMA_NAME(O.schema_id) AS schema_name,
           O.name AS object_name
) T
WHERE O.type = 'U'
  AND O.is_ms_shipped = 0
ORDER BY ObjectFullName DESC;

Statistics and Maintenance Monitoring

Tracking when statistics were last updated helps identify potential query plan issues caused by stale cardinality estimates.

SELECT 
    OBJECT_NAME(SI.object_id) AS TargetTable,
    SI.name AS StatisticDefinition,
    STATS_DATE(SI.object_id, SI.index_id) AS LastRefreshTimestamp,
    SSI.rowmodctr AS RowModificationCount,
    SP.rows AS EstimatedTotalRows,
    'EXEC sp_updatestatistics [' + SCHEMA_NAME(SO.schema_id) + '].[' + OBJECT_NAME(SI.object_id) + '] ;' AS RemediationQuery
FROM sys.stats SI
JOIN sys.objects SO ON SI.object_id = SO.object_id
JOIN sys.sysindexes SSI ON SI.object_id = SSI.id AND SI.index_id = SSI.indid
JOIN sys.partitions SP ON SI.object_id = SP.object_id
WHERE SSI.rowmodctr > 0
  AND STATS_DATE(SI.object_id, SI.index_id) IS NOT NULL
  AND SO.type = 'U'
ORDER BY RowModCTR DESC;

Storage Space Utilization Analysis

Analyzing physical disk consumption provides insight into growth trends and potential cleanup opportunities.

SELECT 
    T.SchemaName + '.' + T.TableName AS ObjectIdentifier,
    SUM(T.TotalSpaceKB) / 1024.0 AS TotalSizeInMB,
    SUM(T.UsedSpaceKB) / 1024.0 AS UsedSizeInMB,
    SUM(T.UnusedSpaceKB) / 1024.0 AS FreeSpaceInMB
FROM (
    SELECT 
        sch.name AS SchemaName,
        tab.name AS TableName,
        SUM(au.total_pages) * 8 AS TotalSpaceKB,
        SUM(au.used_pages) * 8 AS UsedSpaceKB,
        (SUM(au.total_pages) - SUM(au.used_pages)) * 8 AS UnusedSpaceKB
    FROM sys.tables tab
    INNER JOIN sys.schemas sch ON tab.schema_id = sch.schema_id
    INNER JOIN sys.indexes idx ON tab.object_id = idx.object_id
    INNER JOIN sys.partitions par ON idx.object_id = par.object_id AND idx.index_id = par.index_id
    INNER JOIN sys.allocation_units au ON par.partition_id = au.container_id
    GROUP BY sch.name, tab.name
) AS T
GROUP BY T.SchemaName, T.TableName
ORDER BY TotalSizeInMB DESC;

Concurrency Control Configuration

High concurrency scenarios often lead to locking issues or deadlocks. Enabling Snapshot Isolation at the database level mitigates read-write conflicts by allowing readers to access row versions instead of exclusive locks.

To apply these settings, switch to single-user mode temporarily, adjust configuration, and return to multi-user access:

BEGIN TRANSACTION;

ALTER DATABASE [TargetDatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE [TargetDatabase] SET ALLOW_SNAPSHOT_ISOLATION ON;
ALTER DATABASE [TargetDatabase] SET READ_COMMITTED_SNAPSHOT ON;
ALTER DATABASE [TargetDatabase] SET MULTI_USER;

COMMIT TRANSACTION;

Enabling these options reduces blocking during read operations but requires consideration regarding tempdb usage and version store growth.