SQLTeam.com | Weblogs | Forums

Problem with Table Performance

Hi, there I have a SQL Server 2008 R2 database and recently I have had major performance issues (I am not a DB admin).

The database is connected to a CRM and the CRM users call many queries through the CRM interface on the various tables in their day to day work. I was made aware of the issue when user of the CRM reported slow running reports.

I’ve spent several weeks troubleshooting this looking at server issues, CRM code issues, Microsoft application issues… literally everything I could think of. Then I checked the size of the primary table that was being reported on, this table I found to have 540 columns (I didn’t create this took it over). I thought that was quite a lot, so I setup a test system so that I could run a few tests, I randomly thought cut the table columns down by approx. 25%, I randomly selected the columns and deleted them, then tested the reports and they started running better! I thought great, I have a potential cause of the problem at least. I naively thought too many columns. More research has lead me to understand this is not the cause but part of it to a degree. I went back to the DB owner and said we need to scale back the columns, many are redundant anyway so a good time to do a clean-up. So we found 150 random unused columns and deleted them. I ran some test reports, the reports were still very slow, my potential solution didn’t work! I then deleted more columns, ran the reports, they worked again. I then started digging about max sizes of data in SQL 2008 tables and came across this on ibm.com.

For the columns in a table, there is a maximum limit of 1024 columns in a table. SQL Server does have a wide-table feature that allows a table to have up to 30,000 columns instead of 1024. SQL Server 2008 and up have a Sparse Columns feature that can optimize storage when you have many columns with NULL values in the rows of the table. You are still limited to 8060 bytes of data per row. However, Sparse Columns and the wide-table feature in SQL Server are not support with P8 CPE at this time. One possible way to avoid the 1024 maximum number of columns is to create multiple object stores for different classes of objects, for different business needs.”

Reading this I realised, or assumed that when I randomly deleted the fields initially I must of stripped the rows back so that they were under the 8060 bytes limit. I as I say, I’m certainly not a DB admin and just stumbling across things here.

So I’m here for some assistance with this so that I can confirm my suspicions, see if there is anything I can do to be more accurate in diagnosing the issue with some scripts maybe?! And workout a better solution than randomly deleting fields, maybe I can identify what fields are good and bad?!

Any advice would be greatly appreciated.

these issues come OVER and OVER again .. specially in software ...

the guy who develops .. does not do any documentation ..and leaves the company
.. he could have designed it very badly ..
there are so many things ... when it comes to people ..

the guy who takes over has to BREAK his head to figure out ..

The guy who developed would have done with some design in mind ..
If we start changing things without knowing WHY they were designed that way .. then issues will come

Best to take a back up ..
Play around with the backup ... do all your modifications .. testing .. Rollback changes if not happy
once fully satisfied .. move those changes to the Original

To be more accurate at diagnosing the issue ..
.. there are lot of self help guides
Please google search ..

Start woth thr basics first. What are the specs of the server?
Ram, disk size?
Physical server or is it a virtual server?
Are you doing daily backups and regular log backups?

Then look at using DMVs to help guide you find issues

1 Like

It all ultimately comes down to indexes, most importantly the clustered index. For good performance, it's critical to get the best clus index on every significant table.

IF you have Enterprise Edition, you can compress the data which is nearly always a huge help too.

If you're willing to run this script and post the results, I'll provide some guidance on index adjustments to make.

SET DEADLOCK_PRIORITY -8;

DECLARE @filegroup_name nvarchar(128)
DECLARE @list_missing_indexes bit
DECLARE @list_missing_indexes_summary bit
DECLARE @max_compression tinyint
DECLARE @order_by smallint --1=table_name; 2=size; -2=size DESC;.
DECLARE @table_name_pattern sysname

SET @list_missing_indexes = 1 --NOTE: can take some time, set to 0 if you don't want to wait.
SET @list_missing_indexes_summary = 0
SET @order_by = -2
SET @filegroup_name = NULL /* null=all; 'PRIMARY'/'<other_group_name'>=that filegroup only*/
SET @table_name_pattern = '%'
--SET @max_compression = 0

SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; 

DECLARE @debug smallint
DECLARE @include_schema_in_table_names bit
DECLARE @include_system_tables bit
DECLARE @list_filegroup_and_drive_summary bit
DECLARE @total decimal(19, 3)

SET @list_filegroup_and_drive_summary = 0
SET @include_schema_in_table_names = 0 

SET @include_system_tables = 0
SET @debug = 0


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
    
--SELECT * INTO tempdb.dbo.gap_index_specs FROM #index_specs;
--SELECT * FROM tempdb.dbo.gap_index_specs;
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 )
    ) --SELECT * FROM #index_specs

--SELECT create_date FROM sys.databases WITH (NOLOCK) WHERE name = N'tempdb'

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%'))
AND (CAST(SERVERPROPERTY('ProductVersion') AS varchar(30)) NOT LIKE '1[45]%.%')
    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 '
        MIN(p.data_compression) AS min_compression,
        MAX(p.data_compression) 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
    WHERE OBJECT_NAME(dps.object_id) LIKE ''' + @table_name_pattern + '''
    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,
        dps.row_count,
        CASE WHEN @include_schema_in_table_names = 1 THEN OBJECT_SCHEMA_NAME(mid.object_id /*, mid.database_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)
    OUTER 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
    --order by
        --DB_NAME, Table_Name, equality_columns
    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)

--IF @debug > 0
--    SELECT OBJECT_NAME(object_id), * FROM #index_specs ORDER BY 1 /*used_mb DESC*/

-- 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, 
    FILEGROUP_NAME(i.data_space_id) AS main_fg_name,
    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,    
    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,
    dps.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,
    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,
    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,
    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
OUTER 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 dps.row_count AS formatted_value
) AS fc_row_count
OUTER APPLY (
    SELECT alloc_mb AS formatted_value
) AS fc_alloc_mb
*/
--OUTER APPLY DBA.dbo.FormatCounts(dps.row_count, 3) AS fc_row_count

WHERE
    --EXISTS(SELECT 1 FROM sys.indexes i2 WITH (NOLOCK) WHERE i2.object_id = i.object_id AND i2.index_id = 0) AND --$T
    --i.object_id > 100 AND
    (i.is_disabled = 0 OR @order_by IN (-1, 1)) 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 
     --(DB_NAME() IN ('master', 'msdb') OR o.name NOT LIKE 'sys%')
     (@include_system_tables = 1 OR o.name NOT LIKE 'sys%')
    ) AND
    (@filegroup_name IS NULL OR FILEGROUP_NAME(i.data_space_id) LIKE @filegroup_name) AND
    (@max_compression IS NULL OR ispec.max_compression <= @max_compression)
ORDER BY
    --cj2.has_clustered_index, ispec.size_rank, --heaps first, by size
    db_name,
    --i.index_id,
    --ius.user_seeks - ius.user_scans,
    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

SELECT SUM(index_gb) AS Total_Size_GB
FROM #index_usage
--PRINT 'Total Size_GB = ' + CAST(@total AS varchar(30))

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

SET DEADLOCK_PRIORITY NORMAL
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
3 Likes

Hi Scott, Thank you for your reply and assistance. I have ran the script, here are the results.

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 Alma_Site 11/11/2020 2 1801.3 2365073 wce_linkto [LUTableName], [LEntityID], [LETableName] NULL [LUniqueID] 73 0 1 32 14:14.7 NULL 82.79 99.34 0 0 NULL NULL 0 0 [Alma_Site].[dbo].[wce_linkto] 2105058535 31
2 Alma_Site 11/11/2020 5 88 56801 wce_deletelog NULL [DELETETIME] [UNIQUEID] 1 0 1 1 54:46.9 NULL 3.38 36.32 0 0 NULL NULL 0 0 [Alma_Site].[dbo].[wce_deletelog] 309576141 780
3 Alma_Site 11/11/2020 8 66.3 42666 wce_quote_items NULL [Component] [Quote_No], [Total_Net_IVA] 1 0 1 1 02:23.0 NULL 4.98 96.38 0 0 NULL NULL 0 0 [Alma_Site].[dbo].[wce_quote_items] 939866415 16
4 Alma_Site 11/11/2020 8 66.3 42666 wce_quote_items [EntityID] NULL NULL 896 0 1 218 01:31.5 NULL 0.57 95.71 0 0 NULL NULL 0 0 [Alma_Site].[dbo].[wce_quote_items] 939866415 431
5 Alma_Site 11/11/2020 8 66.3 42666 wce_quote_items [Quote_No] NULL NULL 5467 0 1 422 01:30.1 NULL 0.55 95.51 0 0 NULL NULL 0 0 [Alma_Site].[dbo].[wce_quote_items] 939866415 530
6 Alma_Site 11/11/2020 8 66.3 42666 wce_quote_items [Quote_No] [Component] NULL 4 0 1 10 32:22.1 NULL 0.53 99.27 0 0 NULL NULL 0 0 [Alma_Site].[dbo].[wce_quote_items] 939866415 704
7 Alma_Site 11/11/2020 8 66.3 42666 wce_quote_items [Quote_No] [Quote_Print] NULL 2 0 1 2 32:21.0 NULL 0.55 95.42 0 0 NULL NULL 0 0 [Alma_Site].[dbo].[wce_quote_items] 939866415 710
8 Alma_Site 11/11/2020 8 66.3 42666 wce_quote_items [Quote_No], [Component] NULL NULL 2 0 1 1 27:20.6 NULL 0.57 97.56 0 0 NULL NULL 0 0 [Alma_Site].[dbo].[wce_quote_items] 939866415 1187
9 Alma_Site 11/11/2020 8 66.3 42666 wce_quote_items [Quote_No], [Quote_Type] NULL NULL 77 0 1 156 01:27.7 NULL 0.53 99.01 0 0 NULL NULL 0 0 [Alma_Site].[dbo].[wce_quote_items] 939866415 538
10 Alma_Site 11/11/2020 8 66.3 42666 wce_quote_items [Sort_Order], [Sub_Quote_Code] NULL [UniqueID] NULL NULL 1 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL [Alma_Site].[dbo].[wce_quote_items] 939866415 545
11 Alma_Site 11/11/2020 10 53.7 26716 wce_contact [emailIsBanned] [idemail] NULL 2 0 1 1 34:47.6 NULL 4.96 95.93 0 0 NULL NULL 0 0 [Alma_Site].[dbo].[wce_contact] 141959582 915
12 Alma_Site 11/11/2020 10 53.7 26716 wce_contact [idemail], [emailIsBanned] NULL NULL 2 0 1 1 34:47.6 NULL 4.96 99.63 0 0 NULL NULL 0 0 [Alma_Site].[dbo].[wce_contact] 141959582 913
13 Alma_Site 11/11/2020 10 53.7 26716 wce_contact [UNIQUEID] NULL NULL 122 0 1 43 49:40.9 NULL 4.97 97.38 0 0 NULL NULL 0 0 [Alma_Site].[dbo].[wce_contact] 141959582 686
14 Alma_Site 11/11/2020 11 37.7 576136 openRate [mailLogCode] NULL [idEmail] 15 0 1 15 35:01.6 NULL 4.64 84.99 0 0 NULL NULL 0 0 [Alma_Site].[dbo].[openRate] 1502628396 719
15 Alma_Site 11/11/2020 11 37.7 576136 openRate [mailLogCode] NULL [idEmail], [ipOpened], [dateOpened] 1 0 1 1 35:01.7 NULL 8.9 39.44 0 0 NULL NULL 0 0 [Alma_Site].[dbo].[openRate] 1502628396 924
16 Alma_Site 11/11/2020 14 20.6 15817 wce_activity [ALARMSTATUS] [STARTTIME] [UNIQUEID] 1025 0 1 2 50:40.0 NULL 1.05 96.04 0 0 NULL NULL 0 0 [Alma_Site].[dbo].[wce_activity] 1746821285 5
17 Alma_Site 11/11/2020 14 20.6 15817 wce_activity [SCHEDULEFOR] [STARTTIME] [UNIQUEID] 30 0 1 15 02:01.9 NULL 0.31 95.52 0 0 NULL NULL 0 0 [Alma_Site].[dbo].[wce_activity] 1746821285 40
18 Alma_Site 11/11/2020 15 19.5 63937 stats [mailLogCode] NULL [idEmail] 14 0 1 14 34:54.7 NULL 3.39 64.65 0 0 NULL NULL 0 0 [Alma_Site].[dbo].[stats] 1726629194 717
19 Alma_Site 11/11/2020 15 19.5 63937 stats [mailLogCode] NULL [linkUrl] 7 0 1 7 34:54.7 NULL 1.65 99.03 0 0 NULL NULL 0 0 [Alma_Site].[dbo].[stats] 1726629194 723
20 Alma_Site 11/11/2020 17 11.4 7468 wce_quote_stock [Entry_Code] NULL NULL 4 0 1 3 51:39.1 NULL 0.72 98.08 0 0 NULL NULL 0 0 [Alma_Site].[dbo].[wce_quote_stock] 907866301 1504
21 Alma_Site 11/11/2020 17 11.4 7468 wce_quote_stock [Pricing_Year] NULL [UniqueID], [Package_Code], [Sort_Order] 8 0 1 6 45:50.9 NULL 0.74 95.69 0 0 NULL NULL 0 0 [Alma_Site].[dbo].[wce_quote_stock] 907866301 584
22 Alma_Site 11/11/2020 17 11.4 7468 wce_quote_stock [Season], [Quote_Type], [Holiday_Type], [Pricing_Year] NULL [UniqueID], [Component], [Entry_Code] 1 0 1 1 43:53.5 NULL 0.75 96.22 0 0 NULL NULL 0 0 [Alma_Site].[dbo].[wce_quote_stock] 907866301 1500
23 Alma_Site 11/11/2020 17 11.4 7468 wce_quote_stock [Season], [Quote_Type], [Holiday_Type], [Pricing_Year] [Valid_From], [Valid_to] [UniqueID], [Component], [Entry_Code] 7 0 1 5 45:34.0 NULL 0.75 96.15 0 0 NULL NULL 0 0 [Alma_Site].[dbo].[wce_quote_stock] 907866301 581
24 Alma_Site 11/11/2020 19 7.8 6329 wce_quotes NULL [Booking_Ref] [CreateTime], [Quote_No], [Status], [Client_Sage_Code], [Holiday_Type], [Arrival_Date], [Departure_Date], [No_Nights] 1 0 1 1 02:22.6 NULL 0.42 94.11 0 0 NULL NULL 0 0 [Alma_Site].[dbo].[wce_quotes] 2097442546 14
25 Alma_Site 11/11/2020 19 7.8 6329 wce_quotes [EntityID] NULL NULL 21 0 1 6 00:12.0 NULL 0.17 79.04 0 0 NULL NULL 0 0 [Alma_Site].[dbo].[wce_quotes] 2097442546 429
26 Alma_Site 11/11/2020 19 7.8 6329 wce_quotes [Quote_No] NULL NULL 307 0 1 72 01:28.3 NULL 0.08 81.98 0 0 NULL NULL 0 0 [Alma_Site].[dbo].[wce_quotes] 2097442546 542
27 Alma_Site 11/11/2020 32 0.5 2773 schedulerTasks [pCompleted] NULL [idSchedulerEntry], [pmailLogcode], [pActivationDateTime], [pNumberOfMessagesToSend], [pRepeatEveryXseconds], [pLastExecutionFromScheduler], [pTimesExecuted], [pTaskCounter] 276 0 1 1 47:55.9 NULL 0.05 53.58 0 0 NULL NULL 0 0 [Alma_Site].[dbo].[schedulerTasks] 1790629422 1
ident db_name size_rank unused_mb main_fg_name Table_Name key_cols nonkey_count nonkey_cols user_seeks user_scans user_lookups user_updates row_count table_gb index_gb Uniq? index_name index_id data_width cmptd_row_size max_compression 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 system_seeks system_scans system_lookups system_updates last_system_seek last_system_scan last_system_lookup last_system_update capture_date
1 Alma_Site 1 330.8 PRIMARY wce_history UNIQUEID NULL NULL 11772 0 0 507 2250162 4.75 4.75 Y pk_~_uid 1 65 2114 507 0 25524 256 1 153443 0 136727 0 44:19.4 NULL NULL 44:19.3 NULL 0 28 0 0 0 0 NULL NULL NULL NULL 51:04.7
2 Alma_Site 2 7.3 PRIMARY wce_linkto LUniqueID, LUTableName, LEntityID, LETableName NULL NULL 10372 0 117 212 2365073 1.76 0.91 Y pk_~_uid 1 121 409 198 26 26250 256 1 13002 0 14597 0 44:19.3 NULL 43:33.4 44:19.3 NULL 0 13 0 0 0 0 NULL NULL NULL NULL 51:04.7
3 Alma_Site 2 8.3 PRIMARY wce_linkto LUniqueID NULL NULL 51 73 0 212 2365073 1.76 0.85 N LUniqueID_INDEX 2 121 382 198 120024 545325 256 1 1164 0 3793113 0 43:33.4 14:14.7 NULL 44:19.3 NULL 0 13 0 0 0 0 NULL NULL NULL NULL 51:04.7
4 Alma_Site 3 9.6 PRIMARY newsletter idNewsletter NULL NULL 232 2 0 0 2136 0.48 0.48 Y PK_~ 1 4 238384 0 2 232 282 1 4324 0 490 0 49:42.6 19:12.2 NULL NULL NULL 0 23 0 0 0 0 NULL NULL NULL NULL 51:04.7
5 Alma_Site 4 59.1 PRIMARY wce_contact_lastgood UNIQUEID NULL NULL NULL NULL NULL NULL 26715 0.22 0.2 Y PK_wce_contact 1 16 5903 NULL NULL NULL 65 1 NULL NULL NULL NULL NULL NULL NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL NULL NULL 51:04.7
6 Alma_Site 4 0.9 PRIMARY wce_contact_lastgood ClientName NULL NULL NULL NULL NULL NULL 26715 0.22 0.01 N IX_clientname 383 66 180 NULL NULL NULL 55 1 NULL NULL NULL NULL NULL NULL NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL NULL NULL 51:04.7
7 Alma_Site 4 0.8 PRIMARY wce_contact_lastgood UNIQUEID NULL NULL NULL NULL NULL NULL 26715 0.22 0 N IX_uniqueid 384 16 86 NULL NULL NULL 65 1 NULL NULL NULL NULL NULL NULL NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL NULL NULL 51:04.7
8 Alma_Site 4 2 PRIMARY wce_contact_lastgood UNIQUEID, CREATETIME, Company, ClientName, Postcode, WorkPhone, FirstName, Surname NULL NULL NULL NULL NULL NULL 26715 0.22 0.01 N all fields 388 276 321 NULL NULL NULL 65 1 NULL NULL NULL NULL NULL NULL NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL NULL NULL 51:04.7
9 Alma_Site 5 7.4 PRIMARY wce_deletelog UNIQUEID NULL NULL 42 1 0 15 56801 0.09 0.08 Y pk_~_uid 1 16 1434 14 1 49 22 1 57271 0 2007 0 51:25.0 54:46.9 NULL 51:25.0 NULL 0 24 0 0 0 0 NULL NULL NULL NULL 51:04.7
10 Alma_Site 5 0.8 PRIMARY wce_deletelog UNIQUEID NULL NULL 0 0 0 8 56801 0.09 0 N.Y UNIQUEID_INDEX 2 16 38 7 0 0 22 1 40 0 36 0 NULL NULL NULL 51:25.0 NULL 0 24 0 0 0 0 NULL NULL NULL NULL 51:04.7
11 Alma_Site 6 3 PRIMARY wce_contact_pre_field_delete_10112020 NULL NULL NULL NULL NULL NULL NULL 26715 0.08 0.08 N NULL 0 NULL 3100 NULL NULL NULL NULL 1 NULL NULL NULL NULL NULL NULL NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL NULL NULL 51:04.7
12 Alma_Site 7 3.1 PRIMARY wce_contact_31102020 NULL NULL NULL NULL NULL NULL NULL 26703 0.08 0.08 N NULL 0 NULL 3098 NULL NULL NULL NULL 1 NULL NULL NULL NULL NULL NULL NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL NULL NULL 51:04.7
13 Alma_Site 8 2.8 PRIMARY wce_quote_items UniqueID NULL NULL 830607 14 6448 193 42666 0.06 0.06 Y pk_~_uid 1 16 1413 186 235 1013773 61 1 215179509 0 40688183 0 01:31.6 10:14.9 01:31.5 51:49.9 NULL 0 3 0 0 0 0 NULL NULL NULL NULL 51:04.7
14 Alma_Site 8 1.3 PRIMARY wce_quote_items Property_UID, Quote_No, EntityID NULL NULL 0 6510 0 138 42666 0.06 0.01 N quote_no 2 111 115 131 6510 0 177 1 371 0 3802158 0 NULL 01:31.5 NULL 51:49.9 NULL 0 3 0 0 0 0 NULL NULL NULL NULL 51:04.7
15 Alma_Site 9 2.5 PRIMARY listRecipients NULL NULL NULL 0 0 0 2 428629 0.06 0.04 N NULL 0 NULL 98 5 0 5 NULL 1 10 0 10 0 NULL NULL NULL 55:55.1 NULL 0 12 0 0 0 0 NULL NULL NULL NULL 51:04.7
16 Alma_Site 9 1.5 PRIMARY listRecipients idList, idEmail NULL NULL 4 236 0 2 428629 0.06 0.01 N Index_le 4 8 31 0 1612 0 54 1 10 0 384654 0 34:47.6 35:08.0 NULL 55:55.1 NULL 0 12 0 0 0 0 NULL NULL NULL NULL 51:04.7
17 Alma_Site 10 0.1 PRIMARY wce_contact NULL NULL NULL 16785 2786 0 242 26716 0.05 0.05 N NULL 0 NULL 2103 242 4118 16885 NULL 1 401072 0 118363652 0 47:51.1 47:51.0 NULL 44:44.7 NULL 0 4 0 348 0 0 NULL 55:15.9 NULL NULL 51:04.7
18 Alma_Site 11 3 PRIMARY openRate NULL NULL NULL 0 23 0 191 576136 0.04 0.04 N NULL 0 NULL 63 191 87 0 NULL 1 191 0 102150 0 NULL 35:01.7 NULL 39:00.3 NULL 0 18 0 0 0 0 NULL NULL NULL NULL 51:04.7
19 Alma_Site 12 3 PRIMARY wce_groupcontacts LUniqueID NULL NULL NULL NULL NULL NULL 16525 0.03 0.02 Y pk_~_uid 1 16 831 NULL NULL NULL 250 1 NULL NULL NULL NULL NULL NULL NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL NULL NULL 51:04.7
20 Alma_Site 12 1.1 PRIMARY wce_groupcontacts LParentID NULL NULL NULL NULL NULL NULL 16525 0.03 0.01 N LParentID_INDEX 3 32 393 NULL NULL NULL 255 1 NULL NULL NULL NULL NULL NULL NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL NULL NULL 51:04.7
21 Alma_Site 12 0.7 PRIMARY wce_groupcontacts LUniqueID NULL NULL NULL NULL NULL NULL 16525 0.03 0 N LUniqueID_INDEX 2 16 241 NULL NULL NULL 250 1 NULL NULL NULL NULL NULL NULL NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL NULL NULL 51:04.7
22 Alma_Site 13 4.2 PRIMARY wce_attachment UNIQUEID NULL NULL 2299 0 2262 7 34173 0.03 0.02 Y pk_~_uid 1 16 490 0 0 60 288 1 60 0 60 0 14:16.2 NULL 14:16.2 00:42.4 NULL 0 29 0 0 0 0 NULL NULL NULL NULL 51:04.7
23 Alma_Site 13 1.3 PRIMARY wce_attachment HISTORYID NULL NULL 2262 0 0 7 34173 0.03 0 N HISTORYID_INDEX 3 32 110 0 2262 0 288 1 16 0 2277 0 14:16.2 NULL NULL 00:42.4 NULL 0 29 0 0 0 0 NULL NULL NULL NULL 51:04.7
24 Alma_Site 13 0.7 PRIMARY wce_attachment UNIQUEID NULL NULL NULL NULL NULL NULL 34173 0.03 0 N UNIQUEID_INDEX 2 16 58 0 0 0 288 1 0 0 0 0 NULL NULL NULL NULL NULL 0 29 NULL NULL NULL NULL NULL NULL NULL NULL 51:04.7
25 Alma_Site 14 3.5 PRIMARY wce_activity UNIQUEID NULL NULL 137285 1072 30 40 15817 0.02 0.02 Y pk_~_uid 1 16 822 33 3008 136909 64 1 974652 0 2645947 0 50:40.0 50:40.0 02:01.9 04:29.8 NULL 0 5 0 0 0 0 NULL NULL NULL NULL 51:04.7
26 Alma_Site 14 0.3 PRIMARY wce_activity UNIQUEID NULL NULL 0 0 0 15 15817 0.02 0 N.Y UNIQUEID_INDEX 2 16 53 8 0 0 64 1 23 0 22 0 NULL NULL NULL 02:06.0 NULL 0 5 0 0 0 0 NULL NULL NULL NULL 51:04.7
27 Alma_Site 14 0.9 PRIMARY wce_activity UNIQUEID, STARTTIME, SCHEDULEFOR, ATYPE NULL NULL 0 30 0 35 15817 0.02 0 N ~ 17 215 178 27 30 0 22 1 80 0 10031 0 NULL 02:01.9 NULL 04:29.8 NULL 0 5 0 0 0 0 NULL NULL NULL NULL 51:04.7
28 Alma_Site 15 3.3 PRIMARY stats NULL NULL NULL 0 28 0 7 63937 0.02 0.02 N NULL 0 NULL 265 7 84 0 NULL 1 7 0 57855 0 NULL 34:54.7 NULL 07:23.8 NULL 0 15 0 0 0 0 NULL NULL NULL NULL 51:04.7
29 Alma_Site 17 1.1 PRIMARY wce_quote_stock UniqueID NULL NULL 1134 24 0 0 7468 0.01 0.01 Y pk_~_uid 1 16 1179 0 24 1107 96 1 192513 0 54413 0 51:39.1 51:39.1 NULL NULL NULL 0 16 0 0 0 0 NULL NULL NULL NULL 51:04.7
30 Alma_Site 17 0.4 PRIMARY wce_quote_stock UniqueID, Component, Type, Old_Entry_Code NULL NULL NULL NULL NULL NULL 7468 0.01 0 N stock_idx 2 306 210 0 0 0 96 1 0 0 0 0 NULL NULL NULL NULL NULL 0 16 NULL NULL NULL NULL NULL NULL NULL NULL 51:04.7
31 Alma_Site 18 0.5 PRIMARY crm_history UNIQUEID NULL NULL NULL NULL NULL NULL 60777 0.01 0.01 Y PK__~__02925FBF 1 16 148 NULL NULL NULL 1980 1 NULL NULL NULL NULL NULL NULL NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL NULL NULL 51:04.7
32 Alma_Site 19 1.2 PRIMARY wce_quotes UniqueID NULL NULL 44538 14 450 347 6329 0.01 0.01 Y pk_~_uid 1 16 960 293 14 61118 180 1 628549 16 530719 0 01:31.5 10:15.1 01:28.3 01:31.5 NULL 0 8 0 0 0 0 NULL NULL NULL NULL 51:04.7
33 Alma_Site 19 0.2 PRIMARY wce_quotes UniqueID, EntityID, Quote_No NULL NULL 0 527 0 63 6329 0.01 0 N quote_no_1 2 36 99 6 8823 0 180 1 3334821 0 44671 0 NULL 01:28.3 NULL 43:30.1 NULL 0 8 0 0 0 0 NULL NULL NULL NULL 51:04.7
34 Alma_Site 20 2.1 PRIMARY wce_products UNIQUEID NULL NULL NULL NULL NULL NULL 2046 0 0 Y pk_~_uid 1 16 1127 NULL NULL NULL 257 1 NULL NULL NULL NULL NULL NULL NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL NULL NULL 51:04.7
35 Alma_Site 20 0.3 PRIMARY wce_products UNIQUEID NULL NULL NULL NULL NULL NULL 2046 0 0 N.Y UNIQUEID_INDEX 2 16 102 NULL NULL NULL 257 1 NULL NULL NULL NULL NULL NULL NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL NULL NULL 51:04.7
36 Alma_Site 21 1.8 PRIMARY wce_sales UNIQUEID NULL NULL 1 1 0 0 1833 0 0 Y pk_~_uid 1 16 1258 0 1 0 293 1 5305 0 3674 0 30:53.2 30:50.0 NULL NULL NULL 0 25 0 0 0 0 NULL NULL NULL NULL 51:04.7
37 Alma_Site 21 0.3 PRIMARY wce_sales UNIQUEID NULL NULL NULL NULL NULL NULL 1833 0 0 N.Y UNIQUEID_INDEX 2 16 114 0 0 0 293 1 0 0 0 0 NULL NULL NULL NULL NULL 0 25 NULL NULL NULL NULL NULL NULL NULL NULL 51:04.7
38 Alma_Site 22 1.1 PRIMARY wce_tempdata UNIQUEID NULL NULL 108 24 0 12 155 0 0 Y pk_~_uid 1 16 20295 12 24 120 4803 1 4332 0 564 0 44:20.2 44:20.2 NULL 44:20.2 NULL 0 17 0 0 0 0 NULL NULL NULL NULL 51:04.7
39 Alma_Site 23 0.9 PRIMARY wces_users NULL NULL NULL 0 3 0 0 106 0 0 N NULL 0 NULL 7913 0 3 0 NULL 1 8207303 0 373521 0 NULL 03:12.6 NULL NULL NULL 0 22 0 0 0 0 NULL NULL NULL NULL 51:04.7
40 Alma_Site 24 0.3 PRIMARY mailLog idMailLog NULL NULL 2 594 0 2 1289 0 0 Y PK_~ 1 4 732 2 595 2 0 1 818786 0 93614 0 34:47.6 47:56.0 NULL 55:55.1 NULL 0 7 0 1 0 0 NULL 34:06.3 NULL NULL 51:04.7
41 Alma_Site 27 0 PRIMARY wce_bookings NULL NULL NULL NULL NULL NULL NULL 1034 0 0 N NULL 0 NULL 709 NULL NULL NULL NULL 1 NULL NULL NULL NULL NULL NULL NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL NULL NULL 51:04.7
42 Alma_Site 28 0.2 PRIMARY wce_dropdowns UNIQUEID NULL NULL 222552 63181 0 6 448 0 0 Y pk_~_uid 1 16 1170 6 63181 210884 4895 1 29472967 0 1526953 0 50:39.8 50:39.8 NULL 04:31.8 NULL 0 1 0 0 0 0 NULL NULL NULL NULL 51:04.7
43 Alma_Site 28 0 PRIMARY wce_dropdowns UNIQUEID NULL NULL NULL NULL NULL NULL 448 0 0 N.Y UNIQUEID_INDEX 3 16 0 0 0 0 4803 1 0 0 0 0 NULL NULL NULL NULL NULL 0 1 NULL NULL NULL NULL NULL NULL NULL NULL 51:04.7
44 Alma_Site 29 0.2 PRIMARY wce_sysusers UNIQUEID NULL NULL NULL NULL NULL NULL 169 0 0 Y pk_~_uid 1 16 3102 NULL NULL NULL 1815 1 NULL NULL NULL NULL NULL NULL NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL NULL NULL 51:04.7
45 Alma_Site 31 0.1 PRIMARY links idLink NULL NULL 7 0 0 0 1878 0 0 Y PK_~ 1 4 279 0 0 7 48 1 0 0 7 0 07:23.8 NULL NULL NULL NULL 0 31 0 0 0 0 NULL NULL NULL NULL 51:04.7
46 Alma_Site 32 0.2 PRIMARY schedulerTasks idSchedulerEntry NULL NULL 280 384 0 1 2773 0 0 Y PK_~ 1 4 113 1 384 280 575 1 1065109 0 15256 0 47:56.0 47:55.9 NULL 34:31.6 NULL 0 9 0 0 0 0 NULL NULL NULL NULL 51:04.7
47 Alma_Site 33 0.2 PRIMARY wce_sysnotify UniqueID NULL NULL 1904 272 0 0 79 0 0 Y pk_~_uid 1 16 3981 0 272 1632 2296 1 31280 0 8976 0 50:01.3 50:01.2 NULL NULL NULL 0 11 0 0 0 0 NULL NULL NULL NULL 51:04.7
48 Alma_Site 35 0.1 PRIMARY lists idList NULL NULL 109 4 0 2 687 0 0 Y PK_~ 1 4 457 5 4 114 117 1 1386 0 171 0 34:54.7 35:01.7 NULL 55:55.1 NULL 0 21 0 0 0 0 NULL NULL NULL NULL 51:04.7
49 Alma_Site 36 0.1 PRIMARY optOutReasons NULL NULL NULL 0 0 0 2 1926 0 0 N NULL 0 NULL 108 2 0 0 NULL 1 2 0 2 0 NULL NULL NULL 55:55.2 NULL 0 30 0 0 0 0 NULL NULL NULL NULL 51:04.7
50 Alma_Site 37 0.1 PRIMARY wce_property UniqueID NULL NULL NULL NULL NULL NULL 392 0 0 Y pk_~_uid 1 16 534 NULL NULL NULL 420 1 NULL NULL NULL NULL NULL NULL NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL NULL NULL 51:04.7
51 Alma_Site 39 0 PRIMARY Booking_Ref_Accounts_End_Reset_Jan_2018 NULL NULL NULL NULL NULL NULL NULL 642 0 0 N NULL 0 NULL 163 NULL NULL NULL NULL 1 NULL NULL NULL NULL NULL NULL NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL NULL NULL 51:04.7
52 Alma_Site 40 0 PRIMARY Booking_Ref_live_2016_Invoices NULL NULL NULL NULL NULL NULL NULL 616 0 0 N NULL 0 NULL 170 NULL NULL NULL NULL 1 NULL NULL NULL NULL NULL NULL NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL NULL NULL 51:04.7
53 Alma_Site 41 0 PRIMARY Booking_Ref_old nov 2016 NULL NULL NULL NULL NULL NULL NULL 576 0 0 N NULL 0 NULL 182 NULL NULL NULL NULL 1 NULL NULL NULL NULL NULL NULL NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL NULL NULL 51:04.7
54 Alma_Site 42 0 PRIMARY Booking_Ref_Accounts_End_Reset_Jan_2019 NULL NULL NULL NULL NULL NULL NULL 521 0 0 N NULL 0 NULL 201 NULL NULL NULL NULL 1 NULL NULL NULL NULL NULL NULL NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL NULL NULL 51:04.7
55 Alma_Site 43 0 PRIMARY Booking_Ref_Accounts_End_Reset_Jan_2020 NULL NULL NULL NULL NULL NULL NULL 512 0 0 N NULL 0 NULL 204 NULL NULL NULL NULL 1 NULL NULL NULL NULL NULL NULL NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL NULL NULL 51:04.7
56 Alma_Site 45 0 PRIMARY wce_groups UNIQUEID NULL NULL NULL NULL NULL NULL 219 0 0 Y pk_~_uid 1 16 478 NULL NULL NULL 4895 1 NULL NULL NULL NULL NULL NULL NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL NULL NULL 51:04.7
57 Alma_Site 45 0 PRIMARY wce_groups UNIQUEID NULL NULL NULL NULL NULL NULL 219 0 0 N.Y UNIQUEID_INDEX 2 16 0 NULL NULL NULL 4895 1 NULL NULL NULL NULL NULL NULL NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL NULL NULL 51:04.7
58 Alma_Site 46 0 PRIMARY Booking_Ref NULL NULL NULL 0 15 0 0 124 0 0 N NULL 0 NULL 845 0 15 0 NULL 1 0 0 105 0 NULL 48:06.9 NULL NULL NULL 0 19 0 0 0 0 NULL NULL NULL NULL 51:04.7
59 Alma_Site 47 0.1 PRIMARY wce_prop NULL NULL NULL 0 746 0 0 57 0 0 N NULL 0 NULL 0 0 746 0 NULL 1 0 0 2232 0 NULL 26:11.3 NULL NULL NULL 0 6 0 0 0 0 NULL NULL NULL NULL 51:04.7
60 Alma_Site 48 0.1 PRIMARY Booking_Ref_feb_2017 NULL NULL NULL NULL NULL NULL NULL 10 0 0 N NULL 0 NULL 0 NULL NULL NULL NULL 1 NULL NULL NULL NULL NULL NULL NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL NULL NULL 51:04.7
61 Alma_Site 50 0 PRIMARY countries NULL NULL NULL NULL NULL NULL NULL 238 0 0 N NULL 0 NULL 0 NULL NULL NULL NULL 1 NULL NULL NULL NULL NULL NULL NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL NULL NULL 51:04.7
62 Alma_Site 52 0 PRIMARY wce_imsgnotify UniqueID NULL NULL NULL NULL NULL NULL 196 0 0 Y pk_~_uid 1 16 0 NULL NULL NULL 3069 1 NULL NULL NULL NULL NULL NULL NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL NULL NULL 51:04.7
63 Alma_Site 59 0 PRIMARY states NULL NULL NULL NULL NULL NULL NULL 57 0 0 N NULL 0 NULL 0 NULL NULL NULL NULL 1 NULL NULL NULL NULL NULL NULL NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL NULL NULL 51:04.7
64 Alma_Site 60 0 PRIMARY settings idSetting NULL NULL 0 12085 0 1 48 0 0 Y PK_~ 1 4 0 1 12085 0 2849 1 580081 0 12086 0 NULL 47:56.0 NULL 35:01.3 NULL 0 2 0 0 0 0 NULL NULL NULL NULL 51:04.7
65 Alma_Site 67 0 PRIMARY messages idMessage NULL NULL 0 32 0 0 13 0 0 Y PK_~ 1 4 0 0 32 0 3437 1 520 0 136 0 NULL 55:55.1 NULL NULL NULL 0 14 0 0 0 0 NULL NULL NULL NULL 51:04.7
66 Alma_Site 68 0 PRIMARY sendFilters idSendFilter NULL NULL 108 1 0 0 9 0 0 Y PK_~ 1 4 0 0 1 108 2848 1 0 0 109 0 35:01.6 19:12.2 NULL NULL NULL 0 26 0 0 0 0 NULL NULL NULL NULL 51:04.7
67 Alma_Site 69 0 PRIMARY wce_dir_sizes NULL NULL NULL NULL NULL NULL NULL 8 0 0 N NULL 0 NULL 0 NULL NULL NULL NULL 1 NULL NULL NULL NULL NULL NULL NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL NULL NULL 51:04.7
68 Alma_Site 70 0 PRIMARY wce_activity_temp NULL NULL NULL NULL NULL NULL NULL 5 0 0 N NULL 0 NULL 0 NULL NULL NULL NULL 1 NULL NULL NULL NULL NULL NULL NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL NULL NULL 51:04.7
69 Alma_Site 75 0 PRIMARY admins idAdmin NULL NULL 133 281 0 3 1 0 0 Y PK_~ 1 4 0 3 281 133 2849 1 3 0 414 0 34:54.7 47:55.9 NULL 33:36.6 NULL 0 10 0 0 0 0 NULL NULL NULL NULL 51:04.7
70 Alma_Site 76 0 PRIMARY adminStatistics NULL NULL NULL 0 8 0 5 1 0 0 N NULL 0 NULL 0 5 8 0 NULL 1 10 0 13 0 NULL 33:36.7 NULL 33:36.7 NULL 0 20 0 0 0 0 NULL NULL NULL NULL 51:04.7
71 Alma_Site 77 0 PRIMARY privacy NULL NULL NULL NULL NULL NULL NULL 1 0 0 N NULL 0 NULL 0 NULL NULL NULL NULL 1 NULL NULL NULL NULL NULL NULL NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL NULL NULL 51:04.7
72 Alma_Site 81 0 PRIMARY wce_imessage UniqueID NULL NULL NULL NULL NULL NULL 1 0 0 Y pk_~_uid 1 16 0 NULL NULL NULL 1115 1 NULL NULL NULL NULL NULL NULL NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL NULL NULL 51:04.7
73 Alma_Site 82 0 PRIMARY crm_activity UNIQUEID NULL NULL NULL NULL NULL NULL 0 0 0 Y PK__crm_acti__04FF5B322B947552 1 16 NULL NULL NULL NULL NULL 1 NULL NULL NULL NULL NULL NULL NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL NULL NULL 51:04.7
74 Alma_Site 83 0 PRIMARY crm_tmpactivity NULL NULL NULL NULL NULL NULL NULL 0 0 0 N NULL 0 NULL NULL NULL NULL NULL NULL 1 NULL NULL NULL NULL NULL NULL NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL NULL NULL 51:04.7
75 Alma_Site 84 0 PRIMARY dataSources NULL NULL NULL NULL NULL NULL NULL 0 0 0 N NULL 0 NULL NULL NULL NULL NULL NULL 1 NULL NULL NULL NULL NULL NULL NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL NULL NULL 51:04.7
76 Alma_Site 86 0 PRIMARY listRecipientsTemp NULL NULL NULL NULL NULL NULL NULL 0 0 0 N NULL 0 NULL NULL NULL NULL NULL NULL 1 NULL NULL NULL NULL NULL NULL NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL NULL NULL 51:04.7
77 Alma_Site 109 0 PRIMARY wce_emailtracking UniqueID NULL NULL NULL NULL NULL NULL 0 0 0 Y pk_~_uid 1 16 NULL NULL NULL NULL NULL 1 NULL NULL NULL NULL NULL NULL NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL NULL NULL 51:04.7

Total_Size_GB
7.78

Thanks yosiasz, I will look through the DMV article, looks very helpful, thanks again.

Thanks for the data. A lot of people bail out when I ask them to run a script :-).

But I need to be able to download the data into a spreadsheet / SSMS so I can review it.

Can you post text files or a spreadsheet? I hope this site allows that.

The seek / scan numbers look really good overall though, esp. for a first pass at tuning indexes.

Hi Scott,

I can only attach images here :frowning: not sure if you can copy what i pasted into excel or not.

Thanks for looking.

you can post the excel to "Online Drives" and send a link

My experience is 99% of performance issues come from the indexes (as ScottPletcher already said).
Proper usage of indexes can take an hours long query to seconds.

If you are using composite indexes be aware that you must consume them in the same order they are defined to benefit from their optimisation. (cardinality is also very important here).

Daniel.