Reading Indexes taking very long in Registering data source
sov
Posts: 7
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:
This query produces following statistics (look at impressive 11163790 logical reads for 2nd Worktable):
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:
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.
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
auto_update_statistics is true, why are statistics on sys tables outdated?
Updating statistics on all system tables did not solve this.