SQLTeam.com | Weblogs | Forums

Enterprise - Create an index ONLINE is causing locking


#1

We just migrated to Enterprise and I was excited about being able to create indexes without locking tables. I just tried to create one and the application became unresponsive. There were huge amounts of this locking reported...

HADR_SYNC_COMMIT

Which is related to alway-on.

Here is the syntax of the index create...

CREATE INDEX [IDX_ActivityEnrollments_UserId_LessonId_Discriminator] ON [MyAbsorb-NA_Shared_02].[dbo].[ActivityEnrollments] ([UserId], [LessonId], [Discriminator]) INCLUDE ([Status], [DateAdded], [IsDeleted])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON, DROP_EXISTING = OFF, ONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]


#2

SQL must take locks at the start and end of the index build process, even when ONLINE = ON, but usually those locks are of very short duration and so don't cause any locking (they may cause brief blocking, but some blocking is normal anyway in any app).

It may be that the log file must grow to accommodate the logging when creating the index. The entire db will pause when adding log space. Thus, be sure to pre-allocate enough log space to handle the index creation to reduce delays during creation.


#3

So what is the motivation for adding this index that you previously did not have before the migration?


#4

We dropped down from an i3.16xlarge on AWS to an i3.8xlarge due to the much higher cost of Enterprise and also because we need two servers for the Always-On. That is exposing some query performance problems. Some of these can be mitigated with indexing. Others will require code changes.


#5

is ActivityEnrollments an active OLTP table that is also being queried for some reporting purpose?


#6

It is a very big table that is reported on heavily. We do not have an OLAP database unfortunately so I need to balance indexing with heavy inserts/updates. It is a challenge to say the least.


#7

I would go with an option of having a separate table for reporting purposes that is populated on a schedule every 15 minutes? Maybe an appendage database that is a seed for your future OLAP but is not part of AO. This adding of index business on heavily reported on table which also needs high insert/update is going to come back to bite you.


#8

The key is to get the best clustering key(s) on the table, based on how the table is most often queried. Forgot super-simplistic "rules" about an identity "always" being the best clustering key (self-evidently ridiculous once you really think about it).

One of the huge advantages of best clustering is to avoid a gazillion nonclus "covering" indexes, one for (almost) every large query. That is huge overhead and drastically increases the chances of deadlocks.

You can look at current index usage stats and missing index usage stats to help determine the best clustering index. For some tables -- less than half, though -- it could confirm that an identity is indeed the best clus key.


#9

Hi Scott,

What would I look for? Is it simply the most used indices that have the same first column? That column would make the best clustered index. Or is it more nuanced than that?

Thanks.


#10

Bit more nuanced. If you'll run this script and post the results, I'll help you analyze it. Sometimes it's an easy choice, sometimes it's not.

DECLARE @table_name_pattern sysname
SET @table_name_pattern = 'ActivityEnrollments'

SET NOCOUNT ON;
SET DEADLOCK_PRIORITY -8;

IF OBJECT_ID('tempdb.dbo.#index_specs') IS NOT NULL
    DROP TABLE dbo.#index_specs
IF OBJECT_ID('tempdb.dbo.#index_missing') IS NOT NULL
    DROP TABLE dbo.#index_missing
IF OBJECT_ID('tempdb.dbo.#index_usage')  IS NOT NULL
    DROP TABLE dbo.#index_usage

CREATE TABLE dbo.#index_specs (
    object_id int NOT NULL,
    index_id int NOT NULL,
    min_compression int NULL,
    max_compression int NULL,
    drive char(1) NULL,
    alloc_mb decimal(9, 1) NOT NULL,
    alloc_gb AS CAST(alloc_mb / 1024.0 AS decimal(9, 2)),
    used_mb decimal(9, 1) NOT NULL,
    used_gb AS CAST(used_mb / 1024.0 AS decimal(9, 2)),
    rows bigint NULL,
    table_mb decimal(9, 1) NULL,
    table_gb AS CAST(table_mb / 1024.0 AS decimal(9, 2)),
    size_rank int NULL,
    approx_max_data_width bigint NULL,
    UNIQUE CLUSTERED ( object_id, index_id )
    )

DECLARE @list_missing_indexes bit
DECLARE @list_missing_indexes_summary bit
DECLARE @include_schema_in_table_names bit
DECLARE @order_by smallint --1=table_name; 2=size; -2=size DESC;.
DECLARE @format_counts smallint --1=with commas, no decimals; 2/3=with K=1000s,M=1000000s, with 2=0 dec. or 3=1 dec. places;.
DECLARE @debug smallint

--NOTE: showing missing indexes can take some time; set to 0 if you don't want to wait.
SET @list_missing_indexes = 1
SET @list_missing_indexes_summary = 0
SET @include_schema_in_table_names = 1
SET @order_by = 1
SET @format_counts = 3
SET @debug = 0

PRINT 'Started @ ' + CONVERT(varchar(30), GETDATE(), 120)

DECLARE @is_compression_available bit
DECLARE @sql varchar(max)

IF CAST(SERVERPROPERTY('ProductVersion') AS varchar(30)) LIKE '9%'
OR (CAST(SERVERPROPERTY('Edition') AS varchar(40)) NOT LIKE '%Developer%' AND 
    CAST(SERVERPROPERTY('Edition') AS varchar(40)) NOT LIKE '%Enterprise%')
    SET @is_compression_available = 0
ELSE
    SET @is_compression_available = 1

SET @sql = '
INSERT INTO #index_specs ( object_id, index_id,' +
    CASE WHEN @is_compression_available = 0 THEN '' ELSE '
    min_compression, max_compression,' END + '
    alloc_mb, used_mb, rows )
SELECT 
    base_size.object_id, 
    base_size.index_id, ' +
    CASE WHEN @is_compression_available = 0 THEN '' ELSE '
    base_size.min_compression,
    base_size.max_compression,' END + '
    (base_size.total_pages + ISNULL(internal_size.total_pages, 0)) / 128.0 AS alloc_mb,
    (base_size.used_pages + ISNULL(internal_size.used_pages, 0)) / 128.0 AS used_mb,
    base_size.row_count AS rows
FROM (
    SELECT
        dps.object_id,
        dps.index_id, ' +   
        CASE WHEN @is_compression_available = 0 THEN '' ELSE '
        ISNULL(MIN(p.data_compression), 0) AS min_compression,
        ISNULL(MAX(p.data_compression), 0) AS max_compression,' END + '    
        SUM(dps.reserved_page_count) AS total_pages,
	    SUM(dps.used_page_count) AS used_pages,
	    SUM(CASE WHEN dps.index_id IN (0, 1) THEN dps.row_count ELSE 0 END) AS row_count
    FROM sys.dm_db_partition_stats dps ' +
    CASE WHEN @is_compression_available = 0 THEN '' ELSE '
    INNER JOIN sys.partitions p /* WITH (NOLOCK)*/ ON
        p.partition_id = dps.partition_id ' END + '
    WHERE dps.object_id > 100
    GROUP BY 
        dps.object_id,
        dps.index_id
) AS base_size
LEFT OUTER JOIN (
    SELECT 
        it.parent_id,
        SUM(dps.reserved_page_count) AS total_pages,
	    SUM(dps.used_page_count) AS used_pages
    FROM sys.internal_tables it /* WITH (NOLOCK)*/
    INNER JOIN sys.dm_db_partition_stats dps /* WITH (NOLOCK)*/ ON 
        dps.object_id = it.parent_id
    WHERE it.internal_type IN ( ''202'', ''204'', ''211'', ''212'', ''213'', ''214'', ''215'', ''216'' )
    GROUP BY
        it.parent_id
) AS internal_size ON base_size.index_id IN (0, 1) AND internal_size.parent_id = base_size.object_id
'
IF @debug >= 1
    PRINT @sql
EXEC(@sql)

UPDATE [is]
SET approx_max_data_width = index_cols.approx_max_data_width
FROM #index_specs [is]
INNER JOIN (
    SELECT index_col_ids.object_id, index_col_ids.index_id, 
        SUM(CASE WHEN c.max_length = -1 THEN 16 ELSE c.max_length END) AS approx_max_data_width
    FROM (
        SELECT ic.object_id, ic.index_id, ic.column_id
            --,object_name(ic.object_id)
        FROM sys.index_columns ic
        WHERE
            ic.object_id > 100
        UNION
        SELECT i_nonclus.object_id, i_nonclus.index_id, ic_clus.column_id
            --,object_name(i_nonclus.object_id)
        FROM sys.indexes i_nonclus
        CROSS APPLY (
            SELECT ic_clus2.column_id
                --,object_name(ic_clus2.object_id),ic_clus2.key_ordinal
            FROM sys.index_columns ic_clus2
            WHERE
                ic_clus2.object_id = i_nonclus.object_id AND
                ic_clus2.index_id = 1 AND
                ic_clus2.key_ordinal > 0 --technically superfluous, since clus index can't have include'd cols anyway
        ) AS ic_clus
        WHERE
            i_nonclus.object_id > 100 AND
            i_nonclus.index_id > 1
    ) AS index_col_ids
    INNER JOIN sys.columns c ON c.object_id = index_col_ids.object_id AND c.column_id = index_col_ids.column_id
    GROUP BY index_col_ids.object_id, index_col_ids.index_id    
) AS index_cols ON index_cols.object_id = [is].object_id AND index_cols.index_id = [is].index_id

UPDATE ispec
SET table_mb = ispec_ranking.table_mb,
    size_rank = ispec_ranking.size_rank
FROM #index_specs ispec
INNER JOIN (
    SELECT *, ROW_NUMBER() OVER(ORDER BY table_mb DESC, rows DESC, OBJECT_NAME(object_id)) AS size_rank
    FROM (
        SELECT object_id, SUM(alloc_mb) AS table_mb, MAX(rows) AS rows
        FROM #index_specs
        GROUP BY object_id
    ) AS ispec_allocs        
) AS ispec_ranking ON
    ispec_ranking.object_id = ispec.object_id

IF @list_missing_indexes = 1
BEGIN
    SELECT
        IDENTITY(int, 1, 1) AS ident,
        DB_NAME(mid.database_id) AS Db_Name,
        CONVERT(varchar(10), GETDATE(), 120) AS capture_date,
        ispec.size_rank, ispec.table_mb,
        CASE WHEN @format_counts = 1 THEN REPLACE(CONVERT(varchar(20), CAST(dps.row_count AS money), 1), '.00', '')
             WHEN @format_counts = 2 THEN CAST(CAST(dps.row_count * 1.0 / CASE ca1.row_count_suffix 
                 WHEN 'M' THEN 1000000 WHEN 'K' THEN 1000 ELSE 1 END AS int) AS varchar(20)) + ca1.row_count_suffix
             WHEN @format_counts = 3 THEN CAST(CAST(dps.row_count * 1.0 / CASE ca1.row_count_suffix 
                 WHEN 'M' THEN 1000000 WHEN 'K' THEN 1000 ELSE 1 END AS decimal(14, 1)) AS varchar(20)) + ca1.row_count_suffix
             ELSE CAST(dps.row_count AS varchar(20)) END AS row_count,
        CASE WHEN @include_schema_in_table_names = 1 THEN OBJECT_SCHEMA_NAME(mid.object_id) + '.' 
             ELSE '' END + OBJECT_NAME(mid.object_id /*, mid.database_id*/) AS Table_Name,
        mid.equality_columns, mid.inequality_columns, mid.included_columns,        
        user_seeks, user_scans, cj1.max_days_active, unique_compiles, 
        last_user_seek, last_user_scan, 
        CAST(avg_total_user_cost AS decimal(9, 2)) AS avg_total_user_cost,
        CAST(avg_user_impact AS decimal(9, 2)) AS [avg_user_impact%],
        system_seeks, system_scans, last_system_seek, last_system_scan,
        CAST(avg_total_system_cost AS decimal(9, 2)) AS avg_total_system_cost,
        CAST(avg_system_impact AS decimal(9, 2)) AS [avg_system_impact%],
        mid.statement, mid.object_id, mid.index_handle
    INTO #index_missing
    FROM sys.dm_db_missing_index_details mid /*WITH (NOLOCK)*/
    CROSS JOIN (
        SELECT DATEDIFF(DAY, create_date, GETDATE()) AS max_days_active FROM sys.databases /*WITH (NOLOCK)*/ WHERE name = 'tempdb'
    ) AS cj1
    LEFT OUTER JOIN sys.dm_db_missing_index_groups mig /*WITH (NOLOCK)*/ ON
        mig.index_handle = mid.index_handle
    LEFT OUTER JOIN sys.dm_db_missing_index_group_stats migs /*WITH (NOLOCK)*/ ON
        migs.group_handle = mig.index_group_handle
    LEFT OUTER JOIN sys.dm_db_partition_stats dps /*WITH (NOLOCK)*/ ON
        dps.object_id = mid.object_id AND
        dps.index_id IN (0, 1)
    CROSS APPLY (
        SELECT CASE WHEN dps.row_count >= 1000000 THEN 'M' WHEN dps.row_count >= 1000 THEN 'K' ELSE '' END AS row_count_suffix
    ) AS ca1
    OUTER APPLY (
        SELECT ispec.table_mb, ispec.size_rank
        FROM dbo.#index_specs ispec
        WHERE
            ispec.object_id = mid.object_id AND
            ispec.index_id IN (0, 1)
    ) AS ispec
    WHERE
        1 = 1 
        AND mid.database_id = DB_ID()
        AND OBJECT_NAME(mid.object_id) LIKE @table_name_pattern
        AND OBJECT_NAME(mid.object_id) NOT LIKE 'tmp%'
    ORDER BY
        --avg_total_user_cost * (user_seeks + user_scans) DESC,
        Db_Name,
        CASE WHEN @order_by IN (-2, 2) THEN ispec.size_rank * -SIGN(@order_by) ELSE 0 END,
        Table_Name,
        equality_columns, inequality_columns,
        user_seeks DESC
    SELECT *
    FROM #index_missing
    ORDER BY ident
    IF @list_missing_indexes_summary = 1
    BEGIN
        SELECT 
            derived.Size_Rank, derived.table_mb,
            derived.Table_Name, derived.Equality_Column, derived.Equality#, derived.User_Seeks, 
            ISNULL((SELECT SUM(user_seeks)
             FROM #index_missing im2
             CROSS APPLY DBA.dbo.DelimitedSplit8K (inequality_columns, ',') ds
             WHERE im2.Size_Rank = derived.Size_Rank AND
                 LTRIM(RTRIM(ds.Item)) = derived.Equality_Column
            ), 0) AS Inequality_Seeks,
            derived.User_Scans, derived.Last_User_Seek, derived.Last_User_Scan,
            derived.Max_Days_Active, derived.Avg_Total_User_Cost, derived.Approx_Total_Cost
        FROM (
            SELECT 
                Size_Rank, MAX(table_mb) AS table_mb, Table_Name, LTRIM(RTRIM(ds.Item)) AS Equality_Column, 
                SUM(user_seeks) AS User_Seeks, SUM(user_scans) AS User_Scans,
                MAX(last_user_seek) AS Last_User_Seek, MAX(last_user_scan) AS Last_User_Scan,
                MIN(max_days_active) AS Max_Days_Active,
                MAX(avg_total_user_cost) AS Avg_Total_User_Cost,
                (SUM(user_seeks) + SUM(user_scans)) * MAX(avg_total_user_cost) AS Approx_Total_Cost,
                MAX(ds.ItemNumber) AS Equality#
            FROM #index_missing
            CROSS APPLY DBA.dbo.DelimitedSplit8K (equality_columns, ',') ds
            WHERE equality_columns IS NOT NULL
            GROUP BY size_rank, Table_Name, LTRIM(RTRIM(ds.Item))
        ) AS derived
        ORDER BY Size_Rank, Table_Name, Approx_Total_Cost DESC        
    END --IF
END --IF



PRINT 'Index Usage Stats @ ' + CONVERT(varchar(30), GETDATE(), 120)

-- list index usage stats (seeks, scans, etc.)
SELECT 
    IDENTITY(int, 1, 1) AS ident,
    DB_NAME() AS db_name,
    --ispec.drive AS drv,
    ispec.size_rank, ispec.alloc_mb - ispec.used_mb AS unused_mb, 
    CASE WHEN @include_schema_in_table_names = 1 THEN OBJECT_SCHEMA_NAME(i.object_id /*, DB_ID()*/) + '.' 
         ELSE '' END + OBJECT_NAME(i.object_id /*, i.database_id*/) AS Table_Name,    
    CASE WHEN @format_counts = 1 THEN REPLACE(CONVERT(varchar(20), CAST(dps.row_count AS money), 1), '.00', '')
         WHEN @format_counts = 2 THEN CAST(CAST(dps.row_count * 1.0 / CASE ca1.row_count_suffix 
             WHEN 'M' THEN 1000000 WHEN 'K' THEN 1000 ELSE 1 END AS int) AS varchar(20)) + ca1.row_count_suffix
         WHEN @format_counts = 3 THEN CAST(CAST(dps.row_count * 1.0 / CASE ca1.row_count_suffix 
             WHEN 'M' THEN 1000000 WHEN 'K' THEN 1000 ELSE 1 END AS decimal(14, 1)) AS varchar(20)) + ca1.row_count_suffix
         ELSE CAST(dps.row_count AS varchar(20)) END AS row_count,
    ispec.table_gb, ispec.alloc_gb AS index_gb,
    SUBSTRING('NY', CAST(i.is_primary_key AS int) + CAST(i.is_unique_constraint AS int) + 1, 1) +
    CASE WHEN i.is_unique = CAST(i.is_primary_key AS int) + CAST(i.is_unique_constraint AS int) THEN '' 
         ELSE '.' + SUBSTRING('NY', CAST(i.is_unique AS int) + 1, 1) END AS [Uniq?],
    REPLACE(i.name, oa1.table_name, '~') AS index_name,
    --fc_row_count.formatted_value AS row_count,
    i.index_id,
    ispec.approx_max_data_width AS [data_width], 
    CAST(CAST(ispec.used_mb AS float) * 1024.0 * 1024.0 / NULLIF(dps.row_count, 0) AS int) AS cmptd_row_size,
    key_cols AS key_cols,
    LEN(nonkey_cols) - LEN(REPLACE(nonkey_cols, ';', '')) + 1 AS nonkey_count,
    nonkey_cols AS nonkey_cols,
    ius.user_seeks, ius.user_scans, --ius.user_seeks + ius.user_scans AS total_reads,
    ius.user_lookups, ius.user_updates,
    dios.leaf_delete_count + dios.leaf_insert_count + dios.leaf_update_count as leaf_mod_count,
    dios.range_scan_count, dios.singleton_lookup_count,
    DATEDIFF(DAY, STATS_DATE ( i.object_id , i.index_id ), GETDATE()) AS stats_days_old,
    DATEDIFF(DAY, CASE 
        WHEN o.create_date > cj1.sql_startup_date AND o.create_date > o.modify_date THEN o.create_date 
        WHEN o.modify_date > cj1.sql_startup_date AND o.modify_date > o.create_date THEN o.modify_date 
        ELSE cj1.sql_startup_date END, GETDATE()) AS max_days_active,
    dios.row_lock_count, dios.row_lock_wait_in_ms,
    dios.page_lock_count, dios.page_lock_wait_in_ms,    
    ius.last_user_seek, ius.last_user_scan,
    ius.last_user_lookup, ius.last_user_update,
    fk.Reference_Count AS fk_ref_count,
    i.fill_factor,
    ius2.row_num,
    CASE 
        WHEN ispec.max_compression IS NULL THEN '(Not applicable)'
        WHEN ispec.max_compression = 2 THEN 'Page'
        WHEN ispec.max_compression = 1 THEN 'Row'
        WHEN ispec.max_compression = 0 THEN ''
        ELSE '(Unknown)' END AS max_compression,
    ius.system_seeks, ius.system_scans, ius.system_lookups, ius.system_updates,
    ius.last_system_seek, ius.last_system_scan, ius.last_system_lookup, ius.last_system_update,
    GETDATE() AS capture_date
INTO #index_usage
FROM sys.indexes i /*WITH (NOLOCK)*/
INNER JOIN sys.objects o /*WITH (NOLOCK)*/ ON
    o.object_id = i.object_id
CROSS JOIN (
    SELECT create_date AS sql_startup_date FROM sys.databases /*WITH (NOLOCK)*/ WHERE name = 'tempdb'
) AS cj1
OUTER APPLY (
    SELECT CASE WHEN EXISTS(SELECT 1 FROM #index_specs [is] WHERE [is].object_id = i.object_id AND [is].index_id = 1)
        THEN 1 ELSE 0 END AS has_clustered_index
) AS cj2
LEFT OUTER JOIN dbo.#index_specs ispec ON
    ispec.object_id = i.object_id AND
    ispec.index_id = i.index_id
OUTER APPLY (
    SELECT STUFF((
    SELECT
        '; ' + COL_NAME(ic.object_id, ic.column_id)
    FROM sys.index_columns ic /*WITH (NOLOCK)*/
    WHERE
        ic.key_ordinal > 0 AND
        ic.object_id = i.object_id AND
        ic.index_id = i.index_id
    ORDER BY
        ic.key_ordinal
    FOR XML PATH('')
    ), 1, 2, '')
) AS key_cols (key_cols)
OUTER APPLY (
    SELECT STUFF((
    SELECT
        '; ' + COL_NAME(ic.object_id, ic.column_id)
    FROM sys.index_columns ic /*WITH (NOLOCK)*/
    WHERE
        ic.key_ordinal = 0 AND
        ic.object_id = i.object_id AND
        ic.index_id = i.index_id
    ORDER BY
        COL_NAME(ic.object_id, ic.column_id)
    FOR XML PATH('') 
    ), 1, 2, '')
) AS nonkey_cols (nonkey_cols)
LEFT OUTER JOIN sys.dm_db_partition_stats dps /*WITH (NOLOCK)*/ ON
    dps.object_id = i.object_id AND
    dps.index_id = i.index_id
LEFT OUTER JOIN sys.dm_db_index_usage_stats ius /*WITH (NOLOCK)*/ ON
    ius.database_id = DB_ID() AND
    ius.object_id = i.object_id AND
    ius.index_id = i.index_id
LEFT OUTER JOIN (
    SELECT
        database_id, object_id, MAX(user_scans) AS user_scans,
        ROW_NUMBER() OVER (ORDER BY MAX(user_scans) DESC) AS row_num --user_scans|user_seeks+user_scans
    FROM sys.dm_db_index_usage_stats /*WITH (NOLOCK)*/
    WHERE
        database_id = DB_ID()
        --AND index_id > 0
    GROUP BY
        database_id, object_id
) AS ius2 ON
    ius2.database_id = DB_ID() AND
    ius2.object_id = i.object_id
LEFT OUTER JOIN (
    SELECT
        referenced_object_id, COUNT(*) AS Reference_Count
    FROM sys.foreign_keys /*WITH (NOLOCK)*/
    WHERE
        is_disabled = 0
    GROUP BY
        referenced_object_id
) AS fk ON
    fk.referenced_object_id = i.object_id
LEFT OUTER JOIN (
    SELECT *
    FROM sys.dm_db_index_operational_stats ( DB_ID(), NULL, NULL, NULL )
) AS dios ON
    dios.object_id = i.object_id AND
    dios.index_id = i.index_id
OUTER APPLY (
    SELECT OBJECT_NAME(i.object_id/*, DB_ID()*/) AS table_name
        --, CASE WHEN dps.row_count >= 1000000 THEN 'M' WHEN dps.row_count >= 1000 THEN 'K' ELSE '' END AS row_count_suffix
) AS oa1
CROSS APPLY (
    SELECT CASE WHEN dps.row_count >= 1000000 THEN 'M' WHEN dps.row_count >= 1000 THEN 'K' ELSE '' END AS row_count_suffix
) AS ca1

WHERE
    i.object_id > 100 AND
    i.is_hypothetical = 0 AND
    i.type IN (0, 1, 2) AND
    o.type NOT IN ( 'IF', 'IT', 'TF', 'TT' ) AND
    (
     o.name LIKE @table_name_pattern AND
     o.name NOT LIKE 'dtprop%' AND
     o.name NOT LIKE 'filestream[_]' AND
     o.name NOT LIKE 'MSpeer%' AND
     o.name NOT LIKE 'MSpub%' AND
     --o.name NOT LIKE 'queue[_]%' AND 
     o.name NOT LIKE 'sys%' 
    )
ORDER BY
    db_name,
    CASE WHEN @order_by IN (-2, 2) THEN ispec.size_rank * -SIGN(@order_by) ELSE 0 END,
    --ius.user_scans DESC,
    --ius2.row_num, --user_scans&|user_seeks
    table_name, 
    -- list clustered index first, if any, then other index(es)
    CASE WHEN i.index_id IN (0, 1) THEN 1 ELSE 2 END, 
    key_cols


SELECT *
FROM #index_usage
ORDER BY ident

PRINT 'Ended @ ' + CONVERT(varchar(30), GETDATE(), 120)

SET DEADLOCK_PRIORITY NORMAL

#11
ident Db_Name capture_date size_rank table_mb row_count Table_Name equality_columns inequality_columns included_columns user_seeks user_scans max_days_active unique_compiles last_user_seek last_user_scan avg_total_user_cost avg_user_impact% system_seeks system_scans last_system_seek last_system_scan avg_total_system_cost avg_system_impact% statement object_id index_handle
1 MyAbsorb-Main 2018-03-13 2 150006.9 54.7M dbo.ActivityEnrollments [SessionId], [Discriminator] [IsDeleted] [UserId], [InstructorLedCourseEnrollmentId] 453 0 7 215 2018-03-13 14:41:14.483 NULL 1.32 46.84 0 0 NULL NULL 0.00 0.00 [MyAbsorb-Main].[dbo].[ActivityEnrollments] 245575913 88787
2 MyAbsorb-Main 2018-03-13 2 150006.9 54.7M dbo.ActivityEnrollments [SessionId], [Discriminator] [Status], [IsDeleted] [UserId] 4 0 7 1 2018-03-13 13:07:28.340 NULL 88.13 18.82 0 0 NULL NULL 0.00 0.00 [MyAbsorb-Main].[dbo].[ActivityEnrollments] 245575913 144688
3 MyAbsorb-Main 2018-03-13 2 150006.9 54.7M dbo.ActivityEnrollments [UserId], [LessonId], [Discriminator] NULL [Status], [DateAdded], [IsDeleted] 13698 0 7 326 2018-03-13 14:44:02.110 NULL 0.05 35.28 0 0 NULL NULL 0.00 0.00 [MyAbsorb-Main].[dbo].[ActivityEnrollments] 245575913 88736
ident db_name size_rank unused_mb Table_Name row_count table_gb index_gb Uniq? index_name index_id data_width cmptd_row_size key_cols nonkey_count nonkey_cols user_seeks user_scans user_lookups user_updates leaf_mod_count range_scan_count singleton_lookup_count stats_days_old max_days_active row_lock_count row_lock_wait_in_ms page_lock_count page_lock_wait_in_ms last_user_seek last_user_scan last_user_lookup last_user_update fk_ref_count fill_factor row_num max_compression system_seeks system_scans system_lookups system_updates last_system_seek last_system_scan last_system_lookup last_system_update capture_date
1 MyAbsorb-Main 2 37.7 dbo.ActivityEnrollments 54.7M 146.49 20.26 N IX_RowId 1 4 396 RowId NULL NULL 0 0 4826020 1286104 1286103 0 18247426 2 1 2592613 1010883 2368867 0 NULL NULL 2018-03-13 14:44:17.570 2018-03-13 14:44:17.570 7 0 108 0 1 0 0 NULL 2018-03-11 21:57:29.740 NULL NULL 2018-03-13 14:44:17.467
2 MyAbsorb-Main 2 223.9 dbo.ActivityEnrollments 54.7M 146.49 12.88 N idx_~_CurriculumGroupID_Discriminator 20 937 248 CurriculumGroupId; Discriminator 16 AddedBy; CurriculumEnrollment_Id; DateAdded; DateCompleted; DateDeleted; DateEdited; DateStarted; EditedBy; Id; IsDeleted; Progress; ReferenceNumber; Score; Status; TimeSpentTicks; UserId 123193 0 0 1284161 1284160 387509 0 2 1 1510876 0 1508038 0 2018-03-13 14:44:16.017 NULL NULL 2018-03-13 14:44:17.570 7 0 108 0 1 0 0 NULL 2018-03-11 21:57:23.497 NULL NULL 2018-03-13 14:44:17.467
3 MyAbsorb-Main 2 260.1 dbo.ActivityEnrollments 54.7M 146.49 18.57 N idx_~_Discriminator_ChapterEnrollmentID 27 1515 359 Discriminator; ChapterEnrollment_Id 22 AddedBy; CurriculumEnrollment_Id; CurriculumGroupId; DateAdded; DateCompleted; DateDeleted; DateEdited; DateStarted; EditedBy; Id; IsDeleted; LessonId; MaxAttempts; OnlineCourseEnrollment_Id; OtherTrainingVendor; Progress; ReferenceNumber; Score; StateData_Id; Status; TimeSpentTicks; UserId 1831426 0 0 1286104 1286102 2389450 0 2 1 1624716 0 1428557 0 2018-03-13 14:44:17.393 NULL NULL 2018-03-13 14:44:17.570 7 0 108 0 0 0 0 NULL NULL NULL NULL 2018-03-13 14:44:17.467
4 MyAbsorb-Main 2 572.0 dbo.ActivityEnrollments 54.7M 146.49 13.17 N idx_~_Discriminator_CurriculumEnrollmentID 41 937 247 Discriminator; CurriculumEnrollment_Id 16 AddedBy; CurriculumGroupId; DateAdded; DateCompleted; DateDeleted; DateEdited; DateStarted; EditedBy; Id; IsDeleted; Progress; ReferenceNumber; Score; Status; TimeSpentTicks; UserId 245351 0 0 1284161 1284160 247371 0 2 1 1513574 0 1503467 0 2018-03-13 14:44:17.420 NULL NULL 2018-03-13 14:44:17.570 7 0 108 0 27 0 0 NULL 2018-03-13 13:51:58.307 NULL NULL 2018-03-13 14:44:17.467
5 MyAbsorb-Main 2 321.8 dbo.ActivityEnrollments 54.7M 146.49 14.12 N idx_~_Discriminator_OnlineCourseEnrollmentID 34 937 270 Discriminator; OnlineCourseEnrollment_Id 16 AddedBy; ChapterId; DateAdded; DateCompleted; DateDeleted; DateEdited; DateStarted; EditedBy; Id; IsDeleted; Progress; ReferenceNumber; Score; Status; TimeSpentTicks; UserId 542725 0 0 1284161 1284160 924701 0 2 1 1571278 0 1453747 0 2018-03-13 14:44:17.400 NULL NULL 2018-03-13 14:44:17.570 7 0 108 0 2 0 0 NULL 2018-03-11 22:33:12.977 NULL NULL 2018-03-13 14:44:17.467
6 MyAbsorb-Main 2 28.5 dbo.ActivityEnrollments 54.7M 146.49 4.06 Y PK_dbo.~ 2 20 79 Id NULL NULL 5350251 0 0 213582 213582 0 14191289 2 1 2053714 333328 1823078 0 2018-03-13 14:44:17.570 NULL NULL 2018-03-13 14:44:15.950 7 0 108 0 0 0 0 NULL NULL NULL NULL 2018-03-13 14:44:17.467
7 MyAbsorb-Main 2 131.4 dbo.ActivityEnrollments 54.7M 146.49 13.24 N idx_~_ILCEnrollmentID_Disc 42 957 257 InstructorLedCourseEnrollmentId; Discriminator 18 AddedBy; CertificateId; DateAdded; DateCompleted; DateDeleted; DateEdited; DateStarted; EditedBy; Id; IsDeleted; Progress; ReferenceNumber; Score; SessionId; Status; TimeSpentTicks; UserId; WaitlistPriority 12491 0 0 1284161 1284160 14481 0 2 1 1513184 0 1511194 0 2018-03-13 14:44:09.793 NULL NULL 2018-03-13 14:44:17.570 7 0 108 0 0 0 0 NULL NULL NULL NULL 2018-03-13 14:44:17.467
8 MyAbsorb-Main 2 51.7 dbo.ActivityEnrollments 54.7M 146.49 5.55 N idx_~_InstructorLedCourseEnrollmentId_Discriminator_Incl 24 309 107 InstructorLedCourseEnrollmentId; Discriminator; IsDeleted 2 Id; SessionId 6669 0 0 221726 222188 849307 0 2 1 459293 0 454605 0 2018-03-13 14:44:01.563 NULL NULL 2018-03-13 14:44:15.950 7 0 108 0 2 0 0 NULL 2018-03-13 11:19:01.397 NULL NULL 2018-03-13 14:44:17.467
9 MyAbsorb-Main 2 30.8 dbo.ActivityEnrollments 54.7M 146.49 2.29 N IX_LessonId 6 20 44 LessonId NULL NULL 1432 0 0 217579 213582 20208 0 2 1 431832 0 357159 0 2018-03-13 14:33:28.953 NULL NULL 2018-03-13 14:44:15.950 7 0 108 0 0 0 0 NULL NULL NULL NULL 2018-03-13 14:44:17.467
10 MyAbsorb-Main 2 41.0 dbo.ActivityEnrollments 54.7M 146.49 2.77 N IX_OnlineCourseEnrollment_Id 12 20 53 OnlineCourseEnrollment_Id NULL NULL 228 0 0 213582 213582 31789 0 2 1 443164 0 358368 0 2018-03-13 14:23:59.063 NULL NULL 2018-03-13 14:44:15.950 7 0 108 0 0 0 0 NULL NULL NULL NULL 2018-03-13 14:44:17.467
11 MyAbsorb-Main 2 31.7 dbo.ActivityEnrollments 54.7M 146.49 2.18 N IX_SessionId 9 20 42 SessionId NULL NULL 640 1 0 213848 213582 8941 0 2 1 429452 0 427537 0 2018-03-13 14:41:14.483 2018-03-12 18:20:42.027 NULL 2018-03-13 14:44:15.950 7 0 108 0 1 0 0 NULL 2018-03-11 21:20:21.370 NULL NULL 2018-03-13 14:44:17.467
12 MyAbsorb-Main 2 47.5 dbo.ActivityEnrollments 54.7M 146.49 12.82 N idx_~_SessionId_Discriminator_Incl18 26 957 250 SessionId; Discriminator 18 AddedBy; CertificateId; DateAdded; DateCompleted; DateDeleted; DateEdited; DateStarted; EditedBy; Id; InstructorLedCourseEnrollmentId; IsDeleted; Progress; ReferenceNumber; Score; Status; TimeSpentTicks; UserId; WaitlistPriority 24455 0 0 1284161 1284160 734365 0 2 1 1511996 0 1511318 0 2018-03-13 14:42:28.760 NULL NULL 2018-03-13 14:44:17.570 7 0 108 0 4 0 0 NULL 2018-03-12 09:47:01.983 NULL NULL 2018-03-13 14:44:17.467
13 MyAbsorb-Main 2 125.3 dbo.ActivityEnrollments 54.7M 146.49 5.83 N idx_~_Status_SessionId_Discriminator_IsDeleted_Incl2 19 313 112 Status; SessionId; Discriminator; IsDeleted 2 InstructorLedCourseEnrollmentId; UserId 2043 0 0 350769 352217 68135 0 1 1 853996 0 798220 0 2018-03-13 14:41:14.483 NULL NULL 2018-03-13 14:44:15.950 7 0 108 0 2 0 0 NULL 2018-03-11 21:57:27.697 NULL NULL 2018-03-13 14:44:17.467
14 MyAbsorb-Main 2 23.2 dbo.ActivityEnrollments 54.7M 146.49 2.42 N IX_UserId 3 20 47 UserId NULL NULL 2 0 0 218437 213622 2358 0 2 1 443198 0 295478 0 2018-03-12 10:27:37.700 NULL NULL 2018-03-13 14:44:15.950 7 0 108 0 0 0 0 NULL NULL NULL NULL 2018-03-13 14:44:17.467
15 MyAbsorb-Main 2 20.6 dbo.ActivityEnrollments 54.7M 146.49 16.32 N idx_~_UserID_Discriminator_Incl 23 1515 319 UserId; Discriminator; IsDeleted; CurriculumGroupId 20 AddedBy; ChapterEnrollment_Id; ChapterId; CurriculumEnrollment_Id; DateAdded; DateCompleted; DateDeleted; DateEdited; DateStarted; EditedBy; Id; LessonId; MaxAttempts; OtherTrainingVendor; Progress; ReferenceNumber; Score; StateData_Id; Status; TimeSpentTicks 189946 0 0 1286104 1286649 2290091 0 2 1 1556648 0 1452195 0 2018-03-13 14:44:15.987 NULL NULL 2018-03-13 14:44:17.570 7 0 108 0 6 0 0 NULL 2018-03-12 18:13:18.630 NULL NULL 2018-03-13 14:44:17.467

#12

Interesting: the indexes are 6 times the size of the table itself. That's typical, but it means the indexes can be tuned up, probably considerably. The clus index is almost certainly not the best, as it's never used to do a seek anyway.

But I need the results in a format where the columns come out independently as columns. I can't easily use it as one giant "splat" of data. And the DDL of the table columns.