Expanding Indexes List Query

Posted by Barac in SQL Server, SQL Tips and Tricks on Mar 11th, 2019 | 0 comments

Same as in previous posts examples Expanding Tables List Query and Expanding Columns List Query I usually do not notice this type of queries working in the background. But recently I end up working with a customer having DB with over 500K indexes, 150K tables and expanding indexes list operation can end up being slower than usual.

Here is the code:

SELECT
i.name AS [Name],
'Server[@Name=' + quotename(CAST(
        serverproperty(N'Servername')
       AS sysname),'''') + ']' + '/Database[@Name=' + quotename(db_name(),'''') + ']' + '/Table[@Name=' + quotename(tbl.name,'''') + ' and @Schema=' + quotename(SCHEMA_NAME(tbl.schema_id),'''') + ']' + '/Index[@Name=' + quotename(i.name,'''') + ']' AS [Urn],
CAST(
          CASE i.type WHEN 1 THEN 0 WHEN 4 THEN 4
                      WHEN 3 THEN CASE xi.xml_index_type WHEN 0 THEN 2 WHEN 1 THEN 3 WHEN 2 THEN 7 WHEN 3 THEN 8 END
                      WHEN 4 THEN 4 WHEN 6 THEN 5 WHEN 7 THEN 6 WHEN 5 THEN 9 ELSE 1 END
        AS tinyint) AS [IndexType],
i.is_primary_key + 2*i.is_unique_constraint AS [IndexKeyType],
i.is_unique AS [IsUnique],
case UPPER(ISNULL(xi.secondary_type,'')) when 'P' then 1 when 'V' then 2 when 'R' then 3 else 0 end AS [SecondaryXmlIndexType],
i.has_filter AS [HasFilter],
CAST(
          CASE WHEN ((SELECT o.type FROM sys.objects o WHERE o.object_id=i.object_id)='U')
          THEN
          CASE WHEN ((SELECT tbl.is_memory_optimized FROM sys.tables tbl WHERE tbl.object_id = i.object_id)=1) THEN 1 ELSE 0 END
          ELSE
          CASE WHEN ((SELECT tt.is_memory_optimized FROM sys.table_types tt WHERE tt.type_table_object_id = i.object_id)=1) THEN 1 ELSE 0 END
          END
       AS bit) AS [IsMemoryOptimized]
FROM
sys.tables AS tbl
INNER JOIN sys.indexes AS i ON (i.index_id > @_msparam_0 and i.is_hypothetical = @_msparam_1) AND (i.object_id=tbl.object_id)
LEFT OUTER JOIN sys.xml_indexes AS xi ON xi.object_id = i.object_id AND xi.index_id = i.index_id
WHERE
(CAST(
          CASE WHEN ((SELECT o.type FROM sys.objects o WHERE o.object_id=i.object_id)='U')
          THEN
          CASE WHEN ((SELECT tbl.is_memory_optimized FROM sys.tables tbl WHERE tbl.object_id = i.object_id)=1) THEN 1 ELSE 0 END
          ELSE
          CASE WHEN ((SELECT tt.is_memory_optimized FROM sys.table_types tt WHERE tt.type_table_object_id = i.object_id)=1) THEN 1 ELSE 0 END
          END
       AS bit)=@_msparam_2)and((tbl.name=@_msparam_3 and SCHEMA_NAME(tbl.schema_id)=@_msparam_4))
ORDER BY
[Name] ASC
OPTION (FORCE ORDER)

Query plan: