Reading Indexes taking very long in Registering data source

sovsov Posts: 7
edited December 6, 2013 5:11AM in SQL Compare Previous Versions
I am having issues with the performance of Reading indexes in step Registering data sources.
This step takes about 4-5 minutes to finish which is very annoying.

I have captured the fired query:
SELECT 
CONVERT(bit, CASE i.type WHEN 1 THEN 1 ELSE 0 END) AS [Clustered],
CONVERT(bit, CASE i.type WHEN 3 THEN 1 ELSE 0 END) AS Xml,
i.is_unique AS [Unique],
ic.is_included_column AS [Included],
i.is_unique_constraint AS UniqueConstraint,
i.is_primary_key AS [Primary],
i.ignore_dup_key AS IgnoreDupKey,
i.is_padded AS IsPadIndex,
CONVERT(bit, CASE WHEN o.type='U' THEN 1 ELSE 0 END) AS IsTable,
CONVERT(bit, CASE WHEN o.type='V' THEN 1 ELSE 0 END) AS IsView,
CONVERT(bit, CASE WHEN fi.object_id IS NOT NULL THEN 1 ELSE 0 END) AS FullTextKey,
fg.name AS FileGroup,
o.name AS ParentName,
os.name AS SchemaName,
i.name AS IndexName,
i.fill_factor AS [FillFactor],
c.name AS ColumnName,
ic.is_descending_key AS Descending,
CONVERT (bit, 0) AS [Statistics],
CASE 
WHEN i.type IN (3, 4)
THEN (SELECT nrp.no_recompute from sys.objects nro
    JOIN sys.stats nrp ON nro.object_id = nrp.object_id
    WHERE nro.parent_object_id = i.object_id AND nrp.name = i.name)
ELSE s.no_recompute
END AS NoRecompute,
i.data_space_id,
fg.type AS dataspacetype,
i.index_id AS indexid,
x.using_xml_index_id AS [UsingIndex],
xi.name AS [UsingIndexName],
x.secondary_type AS [SecondaryXmlType],
ic.key_ordinal,
ic.partition_ordinal,
i.allow_row_locks,
i.allow_page_locks,
i.is_disabled,
i.filter_definition,
si.spatial_index_type,
si.tessellation_scheme,
sit.bounding_box_xmin,
sit.bounding_box_ymin,
sit.bounding_box_xmax,
sit.bounding_box_ymax,
sit.level_1_grid,
sit.level_2_grid,
sit.level_3_grid,
sit.level_4_grid,
sit.cells_per_object,
ps.data_compression,
ps.partition_number AS data_compression_partition_number

FROM sys.indexes i WITH (NOLOCK)
LEFT JOIN sys.data_spaces fg WITH (NOLOCK) ON fg.data_space_id=i.data_space_id
LEFT JOIN sys.objects o WITH (NOLOCK) ON o.object_id=i.object_id
LEFT JOIN sys.schemas os WITH (NOLOCK) ON os.schema_id=o.schema_id
LEFT JOIN sys.index_columns ic WITH (NOLOCK) ON ic.object_id=i.object_id AND ic.index_id=i.index_id
LEFT JOIN sys.columns c WITH (NOLOCK) on c.object_id=ic.object_id AND c.column_id=ic.column_id
LEFT JOIN sys.stats s WITH (NOLOCK) on s.object_id=i.object_id AND s.name=i.name
LEFT JOIN sys.xml_indexes x WITH (NOLOCK) on i.object_id=x.object_id AND i.index_id=x.index_id
LEFT JOIN sys.xml_indexes xi WITH (NOLOCK) on i.object_id=xi.object_id AND x.using_xml_index_id = xi.index_id
LEFT JOIN sys.spatial_indexes AS si WITH (NOLOCK) ON i.object_id=si.object_id AND i.index_id=si.index_id
LEFT JOIN sys.spatial_index_tessellations AS sit WITH (NOLOCK) ON i.object_id = sit.object_id AND i.index_id = sit.index_id
LEFT JOIN sys.partitions ps WITH (NOLOCK) ON ps.object_id=o.object_id AND ps.index_id = i.index_id
LEFT JOIN sys.fulltext_indexes fi WITH (NOLOCK) on fi.object_id = i.object_id AND fi.unique_index_id = i.index_id

WHERE 
o.type IN ('U', 'V', 'TF', 'TT')
AND i.is_hypothetical = 0
ORDER BY i.object_id, x.secondary_type, i.index_id, ic.key_ordinal, ps.partition_number

This query produces following statistics (look at impressive 11163790 logical reads for 2nd Worktable):
(587358 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 1, logical reads 11163790, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysobjvalues'. Scan count 2, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysidxstats'. Scan count 5, logical reads 224, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysrowsets'. Scan count 1, logical reads 2419, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'syscolpars'. Scan count 1, logical reads 77, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysiscols'. Scan count 1, logical reads 29, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysclsobjs'. Scan count 1, logical reads 1154, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysschobjs'. Scan count 1, logical reads 115, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'syssingleobjrefs'. Scan count 1, logical reads 20, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysobjvalues'. Scan count 4, logical reads 5104, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysftinds'. Scan count 1, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 262753 ms,  elapsed time = 263499 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
The actual execution plans shows nested loops and tablespool with 1151809038 actual records versus 247 estimated records.
The tablespool had something to do with sys.sysidxstats and TVF TEMPSTATS which I related to sys.stats in the query.
After changing LEFT JOIN sys.stats to LEFT HASH JOIN sys.stats the query finishes in about 15 seconds which is an enormous peformance boost:
(587358 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysftinds'. Scan count 1, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysrowsets'. Scan count 1, logical reads 2419, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysobjvalues'. Scan count 4, logical reads 5104, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysidxstats'. Scan count 5, logical reads 224, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysobjvalues'. Scan count 2, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'syscolpars'. Scan count 1244, logical reads 2708, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysiscols'. Scan count 1, logical reads 29, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysclsobjs'. Scan count 1, logical reads 1202, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysschobjs'. Scan count 1, logical reads 115, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'syssingleobjrefs'. Scan count 1, logical reads 37, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 4961 ms,  elapsed time = 14536 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

Could you somehow implement a hash join as mentioned, or give any advice how I can speed up this process without having to use Plan Guides or so?

By the way: we have many tables with around 1000 partitions in the database. That is the explanation for 587358 records...

Thanks in advance!
Stefan.

Comments

Sign In or Register to comment.