Calculate changes or view database history performance for big databases

SQLDoubleG

At work we use one of those multi-purpose databases which has thousands of objects, tables, views, procs and so on.

Despite being a 3rd party product, we are allowed to create custom views and stored procedures, and we want to keep track of those with SQL Source control.

So far so good, the problem is when you need to do anything with source control, because calculating differences before committing, or checking history for this database just takes so long that makes it unusable.

I decided to run sp_whoIsActive in a different SSMS while waiting for history to show up and I got a query that took +45s to complete when running it myself in SSMS, depending on the environment (longer in dev and test)

SELECT
o.name,
os.name AS schemaname,
o.object_id AS id,
o.lock_escalation,
p.name AS owner,
ftc.name AS FullTextCatalogName,
CONVERT (bit, CASE WHEN ftc.name IS NULL THEN 0 ELSE 1 END) AS FullTextIndexed,
fti.change_tracking_state AS FullTextChangeTracking,
fti.stoplist_id AS FullTextStoplistId,
fts.name AS FullTextStoplistName,
ds.name AS FileGroup,
dsx.name AS TextFileGroup,
fs.name AS FilestreamFileGroup,
ds.type AS dataspacetype,
CONVERT(bit, CASE WHEN ct.is_track_columns_updated_on IS NULL THEN 0 ELSE 1 END) AS TableChangeTrackingEnabled,
ct.is_track_columns_updated_on AS TableChangeTrackingTracksColumns,
o.is_filetable AS IsFiletable,
ft.directory_name AS DirectoryName,
ft.filename_collation_name AS FilenameCollationName,
o.modify_date AS LastModified
INTO #RG_TMP_tables
FROM
sys.tables o WITH (NOLOCK)
LEFT JOIN sys.schemas os WITH (NOLOCK) ON os.schema_id=o.schema_id
LEFT JOIN sys.database_principals p WITH (NOLOCK) ON p.principal_id=o.principal_id
LEFT JOIN sys.fulltext_indexes fti WITH (NOLOCK) ON fti.object_id=o.object_id
LEFT JOIN sys.fulltext_catalogs ftc WITH (NOLOCK) ON ftc.fulltext_catalog_id=fti.fulltext_catalog_id
LEFT JOIN sys.fulltext_stoplists fts WITH (NOLOCK) ON fts.stoplist_id=fti.stoplist_id
LEFT JOIN sys.indexes ix WITH (NOLOCK) ON ix.object_id=o.object_id
LEFT JOIN sys.data_spaces ds WITH (NOLOCK) ON ds.data_space_id=ix.data_space_id
LEFT JOIN sys.data_spaces dsx WITH (NOLOCK) ON dsx.data_space_id=o.lob_data_space_id
LEFT JOIN sys.data_spaces fs WITH (NOLOCK) ON fs.data_space_id=o.filestream_data_space_id
LEFT JOIN sys.change_tracking_tables ct WITH (NOLOCK) ON ct.object_id = o.object_id

LEFT JOIN sys.filetables ft WITH (NOLOCK) ON ft.object_id = o.object_id

WHERE ix.index_id<2
AND (CAST(CASE WHEN o.is_ms_shipped = 1 THEN 1
WHEN (SELECT major_id FROM sys.extended_properties
WHERE major_id = o.object_id AND minor_id = 0 AND class = 1 AND
name = N'microsoft_database_tools_support') IS NOT NULL THEN 1
ELSE 0
END AS bit)=0)

Fun enough is that when you run the query above without inserting into #RG_TMP_tables it runs immediately.

So I decide to tune the query, but since only uses dmv's, there is no chance to create indexes, so I changed it slightly and made it run almost immediately.

My query just replace the subquery in the CASE statement in the WHERE clause for an OUTER APPLY and seems to run much better.

SELECT
o.name,
os.name AS schemaname,
o.object_id AS id,
o.lock_escalation,
p.name AS owner,
ftc.name AS FullTextCatalogName,
CONVERT (bit, CASE WHEN ftc.name IS NULL THEN 0 ELSE 1 END) AS FullTextIndexed,
fti.change_tracking_state AS FullTextChangeTracking,
fti.stoplist_id AS FullTextStoplistId,
fts.name AS FullTextStoplistName,
ds.name AS FileGroup,
dsx.name AS TextFileGroup,
fs.name AS FilestreamFileGroup,
ds.type AS dataspacetype,
CONVERT(bit, CASE WHEN ct.is_track_columns_updated_on IS NULL THEN 0 ELSE 1 END) AS TableChangeTrackingEnabled,
ct.is_track_columns_updated_on AS TableChangeTrackingTracksColumns,
o.is_filetable AS IsFiletable,
ft.directory_name AS DirectoryName,
ft.filename_collation_name AS FilenameCollationName,
o.modify_date AS LastModified

INTO #RG_TMP_tables

FROM
sys.tables o WITH (NOLOCK)
LEFT JOIN sys.schemas os WITH (NOLOCK) ON os.schema_id=o.schema_id
LEFT JOIN sys.database_principals p WITH (NOLOCK) ON p.principal_id=o.principal_id
LEFT JOIN sys.fulltext_indexes fti WITH (NOLOCK) ON fti.object_id=o.object_id
LEFT JOIN sys.fulltext_catalogs ftc WITH (NOLOCK) ON ftc.fulltext_catalog_id=fti.fulltext_catalog_id
LEFT JOIN sys.fulltext_stoplists fts WITH (NOLOCK) ON fts.stoplist_id=fti.stoplist_id
LEFT JOIN sys.indexes ix WITH (NOLOCK) ON ix.object_id=o.object_id
LEFT JOIN sys.data_spaces ds WITH (NOLOCK) ON ds.data_space_id=ix.data_space_id
LEFT JOIN sys.data_spaces dsx WITH (NOLOCK) ON dsx.data_space_id=o.lob_data_space_id
LEFT JOIN sys.data_spaces fs WITH (NOLOCK) ON fs.data_space_id=o.filestream_data_space_id
LEFT JOIN sys.change_tracking_tables ct WITH (NOLOCK) ON ct.object_id = o.object_id

LEFT JOIN sys.filetables ft WITH (NOLOCK) ON ft.object_id = o.object_id

OUTER APPLY (SELECT major_id
FROM sys.extended_properties
WHERE major_id = o.object_id
AND minor_id = 0
AND class = 1
AND name = N'microsoft_database_tools_support') AS mstools

WHERE ix.index_id<2
AND o.is_ms_shipped = 0
AND mstools.major_id IS NULL

I obviously double checked the results are the same, in my case 3.7k rows, but sure enough you need to test it against big databases (in number of objects).

I know there are more queries involved but this one seems to be the most painful.

Hope you guys can have a look at it and do something, it'd help a lot.

Thanks.