question

way2sequel avatar image
way2sequel asked

clustered/non clustered Index count and their definition comparision before and after weekly deployment cadence

Hello All, Good afternoon,I am trying to compare the clustered and nonclustered count and their definitions before and after the deployments.With the performance issues observed across the databases and environments,i have found the reason to be missing indexes and statistics and created them directly on the database but since the scripts are not checked in to TFS properly,indexes are not getting created back correctly,we have fixed the scripts and check ins but still i was assigned the task to compare count and index structure before and after each deployment in every environment in an automated fashion,currently i am scripting out the CREATE INDEX statements before and after and comparing in beyond compare but its being tedious for mutiple databases and multiple environments and weekly deployment cadence. Any scripts will be highly appreciated. Thanks in advance, BT
indexesclustered-indexautomationnonclustered-indexscript-task
8 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

It might be worth having a look at Redgate's DLM dashboard http://www.red-gate.com/products/dlm/dlm-dashboard/
1 Like 1 ·
You might want to look at Red Gate's Compare product. You could automate something using powershell and/or command line to facilitate this type of process.
0 Likes 0 ·
Thank you John! i will the pursue the licensing cost and see if i can do something with powershell,cant we achieve this with t-sql?
0 Likes 0 ·
Do you want to actually compare the objects or just push them up from one environment to another?
0 Likes 0 ·
I wanted to compare the index count before the code deployment and after deployment and show me any differing indexes.
0 Likes 0 ·
Show more comments

1 Answer

· Write an Answer
way2sequel avatar image
way2sequel answered
SELECT Db_name() AS database_name, sc.NAME + N'.' + t.NAME AS table_name, (SELECT Max(user_reads) FROM ( VALUES ( last_user_seek ), ( last_user_scan ), ( last_user_lookup )) AS value(user_reads)) AS last_user_read, last_user_update, CASE si.index_id WHEN 0 THEN N'/* No create statement (Heap) */' ELSE CASE is_primary_key WHEN 1 THEN N'ALTER TABLE ' + Quotename(sc.NAME) + N'.' + Quotename(t.NAME) + N' ADD CONSTRAINT ' + Quotename(si.NAME) + N' PRIMARY KEY ' + CASE WHEN si.index_id > 1 THEN N'NON' ELSE N'' END + N'CLUSTERED ' ELSE N'CREATE ' + CASE WHEN si.is_unique = 1 THEN N'UNIQUE ' ELSE N'' END + CASE WHEN si.index_id > 1 THEN N'NON' ELSE N'' END + N'CLUSTERED ' + N'INDEX ' + Quotename(si.NAME) + N' ON ' + Quotename(sc.NAME) + N'.' + Quotename(t.NAME) + N' ' END + /* key def */ N'(' + key_definition + N')' + /* includes */ CASE WHEN include_definition IS NOT NULL THEN N' INCLUDE (' + include_definition + N')' ELSE N'' END + /* filters */ CASE WHEN filter_definition IS NOT NULL THEN N' WHERE ' + filter_definition ELSE N'' END + /* with clause - compression goes here */ CASE WHEN row_compression_partition_list IS NOT NULL OR page_compression_partition_list IS NOT NULL THEN N' WITH (' + CASE WHEN row_compression_partition_list IS NOT NULL THEN N'DATA_COMPRESSION = ROW ' + CASE WHEN psc.NAME IS NULL THEN N'' ELSE + N' ON PARTITIONS (' + row_compression_partition_list + N')' END ELSE N'' END + CASE WHEN row_compression_partition_list IS NOT NULL AND page_compression_partition_list IS NOT NULL THEN N', ' ELSE N'' END + CASE WHEN page_compression_partition_list IS NOT NULL THEN N'DATA_COMPRESSION = PAGE ' + CASE WHEN psc.NAME IS NULL THEN N'' ELSE + N' ON PARTITIONS (' + page_compression_partition_list + N')' END ELSE N'' END + N')' ELSE N'' END + /* ON where? filegroup? partition scheme? */ ' ON ' + CASE WHEN psc.NAME IS NULL THEN Isnull(Quotename(fg.NAME), N'') ELSE psc.NAME + N' (' + partitioning_column.column_name + N')' END + N';' END AS index_create_statement, si.index_id, si.NAME AS index_name, partition_sums.reserved_in_row_GB, partition_sums.reserved_LOB_GB, partition_sums.row_count, stat.user_seeks, stat.user_scans, stat.user_lookups, user_updates AS queries_that_modified, partition_sums.partition_count, si.allow_page_locks, si.allow_row_locks, si.is_hypothetical, si.has_filter, si.fill_factor, si.is_unique, Isnull(pf.NAME, '/* Not partitioned */') AS partition_function, Isnull(psc.NAME, fg.NAME) AS partition_scheme_or_filegroup, t.create_date AS table_created_date, t.modify_date AS table_modify_date FROM sys.indexes AS si JOIN sys.tables AS t ON si.object_id = t.object_id JOIN sys.schemas AS sc ON t.schema_id = sc.schema_id LEFT JOIN sys.dm_db_index_usage_stats AS stat ON stat.database_id = Db_id() AND si.object_id = stat.object_id AND si.index_id = stat.index_id LEFT JOIN sys.partition_schemes AS psc ON si.data_space_id = psc.data_space_id LEFT JOIN sys.partition_functions AS pf ON psc.function_id = pf.function_id LEFT JOIN sys.filegroups AS fg ON si.data_space_id = fg.data_space_id /* Key list */ OUTER APPLY (SELECT Stuff ((SELECT N', ' + Quotename(c.NAME) + CASE ic.is_descending_key WHEN 1 THEN N' DESC' ELSE N'' END FROM sys.index_columns AS ic JOIN sys.columns AS c ON ic.column_id = c.column_id AND ic.object_id = c.object_id WHERE ic.object_id = si.object_id AND ic.index_id = si.index_id AND ic.key_ordinal > 0 ORDER BY ic. key_ordinal FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')) AS keys ( key_definition ) /* Partitioning Ordinal */ OUTER APPLY (SELECT Max(Quotename(c.NAME)) AS column_name FROM sys.index_columns AS ic JOIN sys.columns AS c ON ic.column_id = c.column_id AND ic.object_id = c.object_id WHERE ic.object_id = si.object_id AND ic.index_id = si.index_id AND ic.partition_ordinal = 1) AS partitioning_column /* Include list */ OUTER APPLY (SELECT Stuff ((SELECT N', ' + Quotename(c.NAME) FROM sys.index_columns AS ic JOIN sys.columns AS c ON ic.column_id = c.column_id AND ic.object_id = c.object_id WHERE ic.object_id = si.object_id AND ic.index_id = si.index_id AND ic.is_included_column = 1 ORDER BY c. NAME FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')) AS includes ( include_definition ) /* Partitions */ OUTER APPLY (SELECT Count(*) AS partition_count, Cast(Sum(ps.in_row_reserved_page_count) * 8. / 1024. / 1024. AS NUMERIC(32, 1)) AS reserved_in_row_GB, Cast(Sum(ps.lob_reserved_page_count) * 8. / 1024. / 1024. AS NUMERIC(32, 1)) AS reserved_LOB_GB, Sum(ps.row_count) AS row_count FROM sys.partitions AS p JOIN sys.dm_db_partition_stats AS ps ON p.partition_id = ps.partition_id WHERE p.object_id = si.object_id AND p.index_id = si.index_id) AS partition_sums /* row compression list by partition */ OUTER APPLY (SELECT Stuff ((SELECT N', ' + Cast(p.partition_number AS VARCHAR(32)) FROM sys.partitions AS p WHERE p.object_id = si.object_id AND p.index_id = si.index_id AND p.data_compression = 1 ORDER BY p. partition_number FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')) AS row_compression_clause ( row_compression_partition_list ) /* data compression list by partition */ OUTER APPLY (SELECT Stuff ((SELECT N', ' + Cast(p.partition_number AS VARCHAR(32)) FROM sys.partitions AS p WHERE p.object_id = si.object_id AND p.index_id = si.index_id AND p.data_compression = 2 ORDER BY p. partition_number FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')) AS page_compression_clause ( page_compression_partition_list ) WHERE si.type IN ( 0, 1, 2 ) /* heap, clustered, nonclustered */ AND si.NAME NOT LIKE '%PK%' AND t.NAME NOT LIKE '%dbo%' ORDER BY table_name, si.index_id OPTION (RECOMPILE); GO
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.