Perf Audit: Duplicate Index Query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Perf Audit: Duplicate Index Query

Author

Topic

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2007-08-14 : 11:55:43

Where in the sys tables do you find the columns that an index spans?

-I want to write a query that gives me the table, column & index name for any column that is indexed more than once.
-I would also be able to write a query that shows me the widest (covering most amount of columns) indexes in descending order.
-And because all my JOIN columns end with the letters "ID", I would be able to list all columns that get JOIN'd on that don't have an index on them.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-14 : 12:21:44

>>>-I want to write a query that gives me the table, column & index name for any column that is indexed more than once.

Check where index not in Sys.dm_db_index_usage_stats DMV.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2007-08-15 : 04:14:05

Surely there must be a way to access the metadata that tells me which columns an index covers?

pootle_flump

1064 Posts

Posted - 2007-08-15 : 04:48:36

You can use a mixture of sys.objects, sys.indexes, sys.index_columns and sys.columns.

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2007-08-15 : 06:45:23

quote:
Originally posted by pootle_flump

You can use a mixture of sys.objects, sys.indexes, sys.index_columns and sys.columns.

fantastic!

--Indexes and their columns
select distinct o.name TableName, i.name IndexName, c.name ColumnName from sys.objects o
join sys.columns c on o.object_id=c.object_id
join sys.indexes i on i.object_id=o.object_id
join sys.index_columns icol on o.object_id=icol.object_id and c.column_id=icol.column_id and i.index_id=icol.index_id
order by 1,2,3

--Indexes and their columns for any column that is indexed more than once
select distinct o.name TableName, i.name IndexName, c.name ColumnName
from sys.objects o
join sys.columns c on o.object_id=c.object_id
join sys.indexes i on i.object_id=o.object_id
join sys.index_columns icol on o.object_id=icol.object_id and c.column_id=icol.column_id and i.index_id=icol.index_id
where exists
(select * from
(
select object_id, column_id, count(*) cnt
from sys.index_columns
group by object_id, column_id
) as A where cnt>1 and A.object_id=icol.object_id and A.column_id=icol.column_id
)
order by 1,2,3

--the widest (covering most amount of columns) indexes in descending order (this only seems to work properly in 2005 (v90) databases.)
select distinct o.name TableName, i.name IndexName, cnt
from sys.objects o
join sys.columns c on o.object_id=c.object_id
join sys.indexes i on i.object_id=o.object_id
join sys.index_columns icol on o.object_id=icol.object_id and c.column_id=icol.column_id and i.index_id=icol.index_id
join (
select object_id, index_id, count(*) cnt
from sys.index_columns
group by object_id, index_id
) as A on A.object_id=icol.object_id and A.index_id=icol.index_id
order by cnt desc

--Because all my JOIN columns end with the letters "ID", I can list all columns that get JOIN'd on that don't have an index on them.
select distinct o.name TableName, c.name ColumnName
from sys.objects o
join sys.columns c on o.object_id=c.object_id
where substring(c.name,len(c.name)-1,2)='ID'
and not exists (select * from sys.index_columns icol where o.object_id=icol.object_id and c.column_id=icol.column_id)
order by 1,2

--Get Slowest commands that get called most often
select distinct left(statement_text,150) from (
select top 10000 * from (
SELECT TOP 10000 total_worker_time/execution_count 'Avg CPU Time',
SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2) + 1)statement_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY total_worker_time/execution_count DESC
) as B
where left(statement_text,150) in (select [text] from
(select TOP 10000
objtype,
p.size_in_bytes,
LEFT([sql].[text], 150) as [text]
from sys.dm_exec_cached_plans p
outer apply sys.dm_exec_sql_text (p.plan_handle) sql
ORDER BY usecounts DESC) as A
)
ORDER BY 'Avg CPU Time' DESC
) as C
where len(statement_text)>40
order by left(statement_text,150)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources