Red Gate forums :: View topic - SQLCompare query taking over 4 hours....
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

SQLCompare query taking over 4 hours....

Search in SQL Compare 10 forum
Post new topic   Reply to topic
Jump to:  
Author Message
gokhanvarol@gmail.com



Joined: 03 Mar 2013
Posts: 7

PostPosted: Sun Mar 03, 2013 11:17 pm    Post subject: SQLCompare query taking over 4 hours.... Reply with quote

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('['+os.name+'].['+i.name+']') 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('['+os.name+'].['+i.name+']') 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
Back to top
View user's profile Send private message
gokhanvarol@gmail.com



Joined: 03 Mar 2013
Posts: 7

PostPosted: Sun Mar 03, 2013 11:27 pm    Post subject: Forgot to mention Reply with quote

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('['+os.name+'].['+i.name+']') 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
Back to top
View user's profile Send private message
andy.campbell.smith



Joined: 20 Oct 2011
Posts: 159
Location: Red Gate Software

PostPosted: Wed Mar 06, 2013 3:40 pm    Post subject: Reply with quote

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.
_________________
Andy Campbell Smith

Red Gate Technical Support Engineer
Back to top
View user's profile Send private message
Niall



Joined: 21 Jul 2010
Posts: 19

PostPosted: Thu Apr 25, 2013 3:18 pm    Post subject: Reply with quote

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.
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