Red Gate forums :: View topic - Reading Indexes taking very long in Registering data source
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Compare 10
SQL Compare 10 forum

Reading Indexes taking very long in Registering data source

Search in SQL Compare 10 forum
Post new topic   Reply to topic
Jump to:  
Author Message
sov



Joined: 12 Dec 2012
Posts: 5
Location: Netherlands

PostPosted: Wed Dec 12, 2012 2:33 pm    Post subject: Reading Indexes taking very long in Registering data source Reply with quote

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:
Code:
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):
Code:
(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:
Code:
(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.
Back to top
View user's profile Send private message
sov



Joined: 12 Dec 2012
Posts: 5
Location: Netherlands

PostPosted: Thu Dec 13, 2012 11:06 am    Post subject: Reply with quote

Updating statistics on sys.* tables fixed the problem.
auto_update_statistics is true, why are statistics on sys tables outdated?
Back to top
View user's profile Send private message
sov



Joined: 12 Dec 2012
Posts: 5
Location: Netherlands

PostPosted: Thu Dec 20, 2012 9:25 am    Post subject: Reply with quote

Unfortunately this is still an issue with our production server.
Updating statistics on all system tables did not solve this.
Back to top
View user's profile Send private message
sov



Joined: 12 Dec 2012
Posts: 5
Location: Netherlands

PostPosted: Fri Dec 06, 2013 11:11 am    Post subject: Reply with quote

Anyone?
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group