March 17, 2016 at 9:21 am
I can build the query but don't want to spend time on it if it's already out there.
Does anyone have the query to compare indexes on 2 databases? The databases are on 2 separate servers but the tables are identical with different indexes. Any help with the query is much appreciated.
March 17, 2016 at 9:44 am
sp_helpindex tablename
March 17, 2016 at 10:28 am
I am going to say that it depends on what your are trying to accomplish. Your question is actually a little vague on what you are trying to accomplish.
March 17, 2016 at 10:50 am
Lynn Pettis (3/17/2016)
I am going to say that it depends on what your are trying to accomplish. Your question is actually a little vague on what you are trying to accomplish.
Sorry, I should've been clear.
We have 2 VLDB databases(on separate servers--QA and PROD) with identical tables but different indexes(not on every table). I didn't want to drop all the indexes and re-create them on QA to match them to PROD.Instead I was thinking to compare the indexes, leave out the ones which are same on both but list out the ones which are different, so that I could just drop just those and re-create them.
March 17, 2016 at 11:31 am
Redgate has a tool that will do that for you, SQL Compare.
Not sure what the cost is off the top of my head.
Disclaimer: I am not a Redgate employee but I am a user of some of their tools.
March 17, 2016 at 11:56 am
If you are looking for a query to query the indexes to maybe do this manually, you could use this script below:
SELECT OBJECT_NAME(ips.object_id) AS table_name,
ips.index_type_desc,
ISNULL(i.name, ips.index_type_desc) AS index_name,
ISNULL(REPLACE(RTRIM(( SELECT c.name + CASE WHEN c.is_identity = 1 THEN ' (IDENTITY)' ELSE '' END + CASE WHEN ic.is_descending_key = 0 THEN ' ' ELSE ' DESC ' END
FROM sys.index_columns ic
INNER JOIN sys.columns c
ON ic.object_id = c.object_id
AND ic.column_id = c.column_id
WHERE ic.object_id = ips.object_id
AND ic.index_id = ips.index_id
AND ic.is_included_column = 0
ORDER BY ic.key_ordinal
FOR XML PATH(''))), ' ', ', '), ips.index_type_desc) AS index_keys,
ips.record_count,
(ips.page_count / 128.0) AS space_used_in_MB,
ips.avg_page_space_used_in_percent,
CASE WHEN i.fill_factor = 0 THEN 100 ELSE i.fill_factor END AS fill_factor,
8096 / (ips.max_record_size_in_bytes + 2.00) AS min_rows_per_page,
8096 / (ips.avg_record_size_in_bytes + 2.00) AS avg_rows_per_page,
8096 / (ips.min_record_size_in_bytes + 2.00) AS max_rows_per_page,
8096 * ((100 - (CASE WHEN i.fill_factor = 0 THEN 100.00 ELSE i.fill_factor END)) / 100.00) / (ips.avg_record_size_in_bytes + 2.0000) AS defined_free_rows_per_page,
8096 * ((100 - ips.avg_page_space_used_in_percent) / 100.00) / (ips.avg_record_size_in_bytes + 2) AS actual_free_rows_per_page,
reads = ISNULL(ius.user_seeks, 0) + ISNULL(ius.user_scans, 0) + ISNULL(ius.user_lookups, 0),
writes = ISNULL(ius.user_updates, 0),
1.00 * (ISNULL(ius.user_seeks, 0) + ISNULL(ius.user_scans, 0) + ISNULL(ius.user_lookups, 0)) / ISNULL(CASE WHEN ius.user_updates > 0 THEN ius.user_updates END, 1) AS reads_per_write
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') ips
INNER JOIN sys.indexes i
ON ips.object_id = i.object_id
AND ips.index_id = i.index_id
LEFT OUTER JOIN sys.dm_db_index_usage_stats ius
ON ius.database_id = DB_ID()
AND ips.object_id = ius.object_id
AND ips.index_id = ius.index_id
WHERE ips.alloc_unit_type_desc != 'LOB_DATA'
ORDER BY ips.index_type_desc,
OBJECT_NAME(ips.object_id),
(ips.page_count / 128.0)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply