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

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 ·
in the same server and same environment.Say i have QA Server with 8 databases and each of them has 12 nc indexes and 2 clustered indexes before the code deployment it should remain intact even after the code deployment,the whole problem is the seed scripts and master data being pushed with code is sometimes deleting the databases and there by indexes and if the index creation scripts are not part of post deployment scripts in the release we are not being notified that a index went missing.
0 Likes 0 ·
Thank you Thomas! i will go through it and let you how useful was it.I am really interested using a script like this take the count and definition and save the results to a table before the deployment and repeat the same after deployment and then do a compare and find the differences,does that make sense?
0 Likes 0 ·
Thomas due to character limit i am unable to post the script in the same comment and hence i have posted it as an answer below.
0 Likes 0 ·

1 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

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.