SQL Server Performance

Active Indexs and non Active Indexs

Discussion in 'Contribute Your SQL Server Scripts' started by Luis Martin, Feb 8, 2004.

  1. Luis Martin Moderator

    To find it I do the following:<br /><br />1) Run Profiler 2 or 3 or more days if necessary.<br />2) Separate big trace in small. One by user (I know is a huge task)<br />3) Run Index Tuning Wizard for each one.<br />4) Save Analisys (when IT end) in txt for each user.<br />5) Each txt has index and % of use.<br />6) Build one txt with all txt.<br />7) Transfer txt to sql table.<br /><img src='/community/emoticons/emotion-11.gif' alt='8)' /> Run one procedure to find active index, whit that information I know what indexs need more attencion from me (defrag, etc).<br />9) Run one procedure to find non active index. This procedure tell me what index are not used at all.<br />10) Delete not used.<br /><br />Non Active Index:<br /><br />CREATE PROCEDURE Indices_Inactivos AS<br />declare<br /><br />@Base varchar(15),<br />@Tabla varchar(50),<br />@Indice varchar(100),<br />@BaseAnt varchar(15),<br />@TablaAnt varchar(50)<br /><br />declare IndicesInactivos cursor for<br />select Base, Tabla, Indice<br />from Analisis<br />where Indice like '[[]IX%'<br />group by Base, Tabla, Indice<br />having sum(convert(money,replace(Uso,',','.'))) = 0<br /><br />open IndicesInactivos<br />fetch next from IndicesInactivos into @Base, @Tabla, @Indice<br />while (@@FETCH_STATUS &lt;&gt; -1)<br />begin<br />if @Base &lt;&gt; @BaseAnt<br />begin <br />Print 'BASE: ' + @Base<br />select @BaseAnt = @Base<br />select @TablaAnt = ''<br />end<br /><br />if @Tabla &lt;&gt; @TablaAnt<br />begin <br />Print 'TABLA: ' + @Tabla<br />Print 'INDICES: '<br />select @TablaAnt = @Tabla<br />end<br /><br />Print ' ' + @Indice<br />fetch next from IndicesInactivos into @Base, @Tabla, @Indice<br />continue<br />end<br /><br />close IndicesInactivos<br />deallocate IndicesInactivos<br />GO<br /><br />Active Index<br /><br />CREATE PROCEDURE Indices_Usados AS<br />declare<br /><br />@Base varchar(15),<br />@Tabla varchar(50),<br />@Indice varchar(100),<br />@BaseAnt varchar(15),<br />@TablaAnt varchar(50),<br />@Uso varchar(50)<br /><br /><br />declare IndicesUsados cursor for<br />select Base, Tabla, Indice, convert(varchar,max(convert(money,replace(Uso,',','.'))))as Uso<br />from Analisis<br />where Indice like '[[]IX%'<br />group by Base, Tabla, Indice<br />having sum(convert(money,replace(Uso,',','.'))) &gt; 0<br />Order by Base, Tabla, convert(varchar,max(convert(money,replace(Uso,',','.')))) desc,Indice<br /><br />open IndicesUsados<br />fetch next from IndicesUsados into @Base, @Tabla, @Indice,@Uso<br />while (@@FETCH_STATUS &lt;&gt; -1)<br />begin<br />if @Base &lt;&gt; @BaseAnt<br />begin <br />Print 'BASE: ' + @Base<br />select @BaseAnt = @Base<br />select @TablaAnt = ''<br />end<br /><br />if @Tabla &lt;&gt; @TablaAnt<br />begin <br />Print 'TABLA: ' + @Tabla<br />Print 'INDICES: '<br />select @TablaAnt = @Tabla<br />end<br />if len(@Uso) = 4<br />begin<br />select @Uso = '0' + @Uso<br />end<br />Print @Uso + ' ' + @Indice<br /><br />fetch next from IndicesUsados into @Base, @Tabla, @Indice,@Uso<br />continue<br />end<br /><br />close IndicesUsados<br />deallocate IndicesUsados<br />GO<br /><br />I'm not developer, so any suggestion would be appreciate.<br /><br /><br /><br /><br />Luis Martin<br />Moderator<br />SQL-Server-Performance.com
  2. dmaddhali New Member

    Hey luis...thanks for your information . You missed out to create a table called Analisis.
  3. dmaddhali New Member

    Hey luis...thanks for your information . You missed out to create a table called Analisis.
  4. Luis Martin Moderator

    This is and old and heavy work.

    There is and easy way I wrote un post:

    http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=9391

    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.



Share This Page