SQL Server Performance Forum – Threads Archive

sql server 2005 In-active indexes

I am a developer working on sql server 2005 and I would like to know how to know about unused index.
I have got some topics which ask us to used profile tools. Please tell me a programmatical way to find unused indexes.
I have got this code from a site.But is useless until I store some data using profile.
can any one give me the whole code to know unsused Indexes
Please help me out of this.
CREATE PROCEDURE Indices_Inactivos AS
declare @Base varchar(15),
@Tabla varchar(50),
@Indice varchar(100),
@BaseAnt varchar(15),
@TablaAnt varchar(50) declare IndicesInactivos cursor for
select Base, Tabla, Indice
from satAnalysis
where Indice like ‘[[]IX%’
group by Base, Tabla, Indice
having sum(convert(money,replace(Uso,’,’,’.’))) = 0 open IndicesInactivos
fetch next from IndicesInactivos into @Base, @Tabla, @Indice
while (@@FETCH_STATUS <> -1)
begin
if @Base <> @BaseAnt
begin
Print ‘BASE: ‘ + @Base
select @BaseAnt = @Base
select @TablaAnt = ”
end if @Tabla <> @TablaAnt
begin
Print ‘TABLA: ‘ + @Tabla
Print ‘INDICES: ‘
select @TablaAnt = @Tabla
end Print ‘ ‘ + @Indice
fetch next from IndicesInactivos into @Base, @Tabla, @Indice
continue
end close IndicesInactivos
deallocate IndicesInactivos

May refer tohttp://www.sql-server-performance.com/forum/default.asp link for further information, I’m sure it will be different and easy in sQL2k5 as compared to SQL 2k. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

hi…please go through the following link http://www.sql-server-performance.com/optimizing_indexes.asp the writer has asked us to use profiler and create a log file by tracking various events.
after creating the log file he has asked us to import the trace file into a table and use the above mentioned code to get the unused indexes.
Can I use create trace storeprocedures for all the events he has mentioned and store the data ina file and then import the data from file to a table and then use the query mentioned above to get unused indexes ? can any one tell me how to import a trace file into a table programatically. Thanks in advance.

Dmaddhali: Check:
http://www.sql-server-performance.com/forum/forum.asp?FORUM_ID=27 At end there is one idea to find out no active indexes.
Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Hey luis ,
thanks for your help. This forum has some information which needs us to use index tuning wizard.
Like we have trace related stored procedures to do the tasks which profiler does.
Do we have any stored procedures which can do this for us .

]]>