SQLCompare query taking over 4 hours....

gokhanvarol@gmail.com

This query is used by sql compare. It's taking over 4 hours in one of our system which I am collecting a trace for it and I would like to send the trace and the database snapshot sqlcompare creates on this database (if the snapshot completes). Maybe the query needs to be broken down a little or some other way is needs to be optimized. Please let me know how I can send this data, I believe it would be over 10MB zipped.

Also I noticed a comment in this query, actually the object_id function is returning wrong values in cases (if the non constraint index name in that schema exists as another object in the same schema). kc.object_id in that query seems to return the right results

OBJECT_ID('.') AS ObjectID -- hack! Can't see where else to get this value though

,kc.OBJECT_ID AS o2 -- hack! Can't see where else to get this value though


SELECT
CONVERT(bit, CASE i.type WHEN 1 THEN 1 WHEN 5 THEN 1 ELSE 0 END) AS [Clustered], -- 5 is reserved as 'clustered columnstore' which doesn't exist yet
CONVERT(bit, CASE i.type WHEN 3 THEN 1 ELSE 0 END) AS Xml,
CONVERT(bit, CASE i.type WHEN 5 THEN 1 WHEN 6 THEN 1 ELSE 0 END) AS Columnstore,
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,
CONVERT(bit, CASE kc.is_system_named WHEN 1 THEN 1 ELSE 0 END) As is_system_named,
OBJECT_ID('.') AS ObjectID -- hack! Can't see where else to get this value though
, fi.property_list_id
,kc.OBJECT_ID AS o2 -- hack! Can't see where else to get this value though
FROM sys.indexes i WITH (NOLOCK)
LEFT JOIN sys.key_constraints kc WITH (NOLOCK) ON kc.parent_object_id = i.object_id AND kc.unique_index_id = i.index_id
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

gokhanvarol@gmail.com

With very minor changes (see below, removing object_id function , putting subquery down) this query returned in 1:44 and returned rows as below.
I can still provide the trace data I am collecting, but if you could put the changes into sql compare and give us a prerelease version or so I'll really appreciated. We are not able to use the tool currently since this query is hanging.
Thank you


--(3900848 row(s) affected)

SELECT
CONVERT(bit, CASE i.type WHEN 1 THEN 1 WHEN 5 THEN 1 ELSE 0 END) AS [Clustered], -- 5 is reserved as 'clustered columnstore' which doesn't exist yet
CONVERT(bit, CASE i.type WHEN 3 THEN 1 ELSE 0 END) AS Xml,
CONVERT(bit, CASE i.type WHEN 5 THEN 1 WHld EN 6 THEN 1 ELSE 0 END) AS Columnstore,
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 i.no_recompute/*(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,
CONVERT(bit, CASE kc.is_system_named WHEN 1 THEN 1 ELSE 0 END) As is_system_named,
kc.[object_id] AS o2, -- hack! Can't see where else to get this value though -- added
-- removed OBJECT_ID('.') AS ObjectID -- hack! Can't see where else to get this value though
fi.property_list_id
INTO #temp
FROM (SELECT * ,(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
AND i.type IN (3, 4)) AS no_recompute
FROM sys.indexes i WITH (NOLOCK))i
LEFT JOIN sys.key_constraints kc WITH (NOLOCK) ON kc.parent_object_id = i.object_id AND kc.unique_index_id = i.index_id
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

Andy C S

Thanks for your feedback - I've logged this as SC-6257 in our internal bug-tracking database. I'll let you know when there's an update on that.

Niall

This issue is also hitting us and SQL Compare is now taking a couple of hours to do the compare on a moderate sized DataWarehouse. Running SQL Server 2012 SP1 CU3. For reasons that I cannot detemine it only hits certain instances of SQL, especially one in which there is no data in the database. A fix would be appreciated.