Index Management

  • Comments posted to this topic are about the item Index Management

    Regards,
    [font="Verdana"]Sqlfrenzy[/font]

  • technically very good document. please continue do more research on locks also.

  • hi am kinda new here

    can this be apllied on SQL 2000?

    thanks!

  • The code posted contains a lot of errors. Misspelled procedure names, missing columns, wrong aliases.

    I don't have time to do the error checking right now, but will post my findings/corrections later today (if it is not done by then...)

  • darwin.maramot (10/20/2009)


    hi am kinda new here

    can this be apllied on SQL 2000?

    thanks!

    No..It doesn't applies to sql 2000

    Regards,
    [font="Verdana"]Sqlfrenzy[/font]

  • dagfinn.molde (10/20/2009)


    The code posted contains a lot of errors. Misspelled procedure names, missing columns, wrong aliases.

    I don't have time to do the error checking right now, but will post my findings/corrections later today (if it is not done by then...)

    thanks..I will review and update asap...

    Regards,
    [font="Verdana"]Sqlfrenzy[/font]

  • Sqlfrenzy (10/20/2009)


    darwin.maramot (10/20/2009)


    hi am kinda new here

    can this be apllied on SQL 2000?

    thanks!

    No..It doesn't applies to sql 2000

    ok thanks!

  • nice article

    "Keep Trying"

  • Code posted is very inconsistent.

    Uppercase, lowercase, schema names missing.

    Prefixes are inconsistent - sometimes "Uf_", othertimes "fn_"

    select * is naughty unless you need the full result set...

    When using the "EXISTS" keyword you can simply "SELECT 1 WHERE ...."

  • When trying to bring your functions to 'life', I changed the proc_FilltblIndexUsageInfo

    ALTER Proc [dbo].[proc_FilltblIndexUsageInfo]

    AS

    Begin

    Truncate table tblIndexUsageInfo

    insert into tblIndexUsageInfo

    select db_name(db_id()) DbName,

    so.name as 'TableName',

    ISNULL(si.name,'No Index') as IndexName,

    si.index_id,

    Case When is_primary_key=1 then 'Primary Key Constraint'

    Else 'Index' End ConstraintType,

    si.type_desc,

    dbo.Uf_GetIndexCol(si.index_id,so.object_id,0) As IndexKeyColumn,

    dbo.Uf_GetIndexCol(si.index_id,so.object_id,1) As IncludedCols,

    spi.user_seeks,spi.user_scans,spi.user_lookups,spi.user_updates,

    (user_seeks+user_scans+user_lookups+user_updates) as 'IndexUsage ',

    dbo.Uf_GetIndexSize(si.index_id,so.object_id) 'IndexSizeKB',

    Cast(

    (user_seeks+user_scans+user_lookups+user_updates)/

    dbo.Uf_GetIndexSize(si.index_id,so.object_id) As decimal(10,2))

    As IndexUsagetoSizeRatio

    from sys.objects so inner join sys.indexes si

    on so.object_id=si.Object_id

    inner join sys.dm_db_index_usage_stats spi

    on spi.Object_id=so.Object_id

    and si.index_id=spi.index_id

    and spi.database_id=db_id()

    where so.type='u'

    END

  • The second-to-last call doesn't work due to casting problems.

    Is there a way to have something like this for SQL2000?

  • Where will the corrected code be placed? Will it be reposted? The articles topic and goals interested me, but the problems trying to get it to work are very frustrating.

  • Agreed. Also, index reorgs can somtimes wreak havoc with TL shipping. I haven't had the chance to completely finish these scripts due to time constraints. Is anybody else having an issue with Uf_GetIndexSize?

  • The fixed code... I am not sure if it runs but I can create all the objects.

    create table tblIndexUsageInfo

    (

    Sno int identity(1, 1)

    ,Dbname varchar(100)

    ,TableName varchar(100)

    ,IndexName varchar(300)

    ,Index_id int

    ,ConstraintType varchar(25)

    ,Type_desc varchar(100)

    ,IndexKeyColumn varchar(1000)

    ,IncludedColumn varchar(1000)

    ,user_seeks int

    ,user_scans int

    ,user_lookups int

    ,user_update int

    ,IndexUsage int

    ,IndexSizeKB int

    ,IndexUSageToSizeRatio decimal(10, 2)

    )

    go

    create function Uf_GetIndexCol

    (

    @index_id int

    ,@tableid int

    ,@isincluded bit

    )

    returns varchar(3000)

    as

    begin

    return

    (

    stuff(

    (select ',' + sc.name from sys.columns sc,

    sys.index_columns sic,sys.indexes si

    where sc.column_id=sic.column_id

    and si.index_id=sic.index_id

    and sc.object_id=sic.object_id

    and si.object_id=sic.object_id

    and sic.is_included_column=@isincluded

    and si.object_id=@tableid

    and si.index_id=@index_id

    for xml path('')),1,1,'')

    )

    end

    go

    create function Uf_GetIndexSize

    (

    @index_id int

    ,@tableid int

    )

    returns float

    as

    begin

    return (select (cast(reserved as float)*8192)/(1024) from sysindexes

    where indid=@index_id and id=@tableid)

    end

    go

    create proc proc_FilltblIndexUsageInfo

    as

    begin

    truncate table tblIndexUsageInfo

    insert into tblIndexUsageInfo

    select distinct

    db_name(db_id()) DbName

    ,so.name as 'TableName'

    ,isnull(si.name, 'No Index') as IndexName

    ,si.index_id

    ,case when is_primary_key = 1 then 'Primary Key Constraint'

    else 'Index'

    end ConstraintType

    ,si.type_desc

    ,dbo.Uf_GetIndexCol(si.index_id, so.object_id, 0) as IndexKeyColumn

    ,dbo.Uf_GetIndexCol(si.index_id, so.object_id, 1) as IncludedCols

    ,spi.user_seeks

    ,spi.user_scans

    ,spi.user_lookups

    ,spi.user_updates

    ,(user_seeks + user_scans + user_lookups + user_updates) as 'IndexUsage '

    ,dbo.Uf_GetindexSize(si.index_id, so.object_id) 'IndexSizeKB'

    ,cast((user_seeks + user_scans + user_lookups + user_updates) / dbo.Uf_GetindexSize(si.index_id, so.object_id) as decimal(10, 2)) as IndexUsagetoSizeRatio

    from

    sys.objects so

    inner join sys.indexes si

    on so.object_id = si.Object_id

    inner join sys.dm_db_index_usage_stats spi

    on spi.Object_id = so.Object_id

    inner join sys.index_columns sic

    on sic.object_id = si.object_id and sic.index_id = si.index_id

    inner join sys.columns sc

    on sc.Column_id = sic.column_id and sc.object_id = sic.object_id

    inner join INFORMATION_SCHEMA.TABLE_CONSTRAINTS c

    on so.name = c.TABLE_NAME

    where

    so.type = 'u'

    end

    go

    create table tblMostUsedIndexes

    (

    Sno int identity(1, 1)

    ,TableName varchar(100)

    ,IndexName varchar(1000)

    ,Index_id int

    ,SchemaName varchar(100)

    ,TableId int

    ,IndexUsage int

    ,IndexUSageToSizeRatio decimal(10, 2)

    )

    go

    create proc proc_InsertMostUsedIndexes

    (

    @IndexUSageToSizeRatio decimal(10, 2)

    ,@indexusage int

    )

    as

    begin

    insert into tblMostUsedIndexes

    select

    b.TableName

    ,b.IndexName

    ,(

    select

    index_id

    from

    sys.indexes

    where

    name = b.IndexName

    ) as Index_id

    ,ss.name as Schemaname

    ,object_id(tablename)

    ,IndexUsage

    ,IndexUSageToSizeRatio

    from

    tblIndexUsageInfo b

    ,sys.tables st

    ,sys.schemas ss

    where

    (

    b.indexusage >= @indexUsage or IndexUSageToSizeRatio >= @IndexUSageToSizeRatio

    ) and st.name = tablename and st.schema_id = ss.schema_id and b.indexname not in (select

    indexname

    from

    tblMostUsedIndexes)

    group by

    b.indexname

    ,b.tablename

    ,ss.name

    ,b.IndexUSageToSizeRatio

    ,b.indexusage

    end

    go

    create proc proc_RebuildSelectedIndexes

    as

    begin

    set NOCOUNT on

    /* Code to Rebuild or Reorganise index */

    declare

    @Schema varchar(200)

    ,@Tablename varchar(200)

    declare

    @indexName varchar(400)

    ,@Qry varchar(1000)

    ,@RecCount int

    declare

    @avg_frag decimal

    ,@dbid int

    ,@ObjectId int

    declare

    @IndexCount int

    ,@TotalRec int

    ,@Index_type varchar(50)

    declare

    @IndexRebuildCount int

    ,@IndexReorgCount int

    ,@IxOpr varchar(10)

    declare @index_id int

    set @IndexRebuildCount = 0

    set @IndexReorgCount = 0

    set @IxOpr = ''

    set @dbid = db_id()

    select

    @RecCount = sno

    from

    tblMostUsedIndexes

    set @TotalRec = @RecCount

    while (@RecCount > 0)

    begin

    select

    @Schema = schemaname

    ,@TableName = TableName

    ,@ObjectId = tableid

    ,@index_id = index_id

    from

    tblMostUsedIndexes

    where

    sno = @RecCount

    select

    identity( int,1,1 ) as Sno

    ,a.[name] IndexName

    ,avg_fragmentation_in_percent as avg_frag

    ,type_desc

    ,a.index_id

    into

    #temp_2

    from

    sys.dm_db_index_physical_stats(@dbid, @objectid, @index_id, null, 'Limited') as b

    join

    sys.indexes as a

    on a.object_id = b.object_id and a.index_id = b.index_id and a.index_id > 0

    select

    @IndexCount = sno

    from

    #temp_2

    while (@IndexCount > 0)

    begin

    select

    @avg_frag = avg_frag

    ,@IndexName = indexname

    ,@Index_Type = type_desc

    from

    #temp_2

    where

    sno = @IndexCount

    if (@avg_frag <= 20)

    begin

    set @Qry = 'Alter index ' + @IndexName + ' ON ' + @Schema + '.' + @TableName + ' REORGANIZE;'

    set @IndexReorgCount = @IndexReorgCount + 1

    set @IxOpr = 'REORGANIZE'

    end

    if (@avg_frag > 20)

    begin

    set @Qry = 'Alter index ' + @IndexName + ' ON ' + @Schema + '.' + @TableName + ' REBUILD;'

    set @IndexRebuildCount = @IndexRebuildCount + 1

    set @IxOpr = 'REBUILD'

    end

    print @qry

    execute(@qry)

    set @IndexCount = @IndexCount - 1

    end

    drop table #temp_2

    set @RecCount = @RecCount - 1

    end

    set NOCOUNT off

    end

    go

    create table tblUnusedIndexes

    (

    UnusedIndid int identity(1, 1)

    ,Schemaname varchar(100)

    ,tablename varchar(100)

    ,IndexName varchar(500)

    ,IndexUsage int

    ,IndexUsageToSizeRatio decimal(10, 2)

    ,IndexKey varchar(1000)

    ,IncludedCol varchar(1000)

    ,ConstraintType varchar(1000)

    ,IndexSizeKB int

    ,DropQry varchar(4000)

    ,IndexStatus varchar(20) default 'Active'

    )

    go

    create procedure proc_FilltblUnusedIndexes --1,0

    (

    @IndexUsageToSizeRatio decimal(10, 2)

    ,@indexusage int

    )

    as

    begin

    insert into tblUnusedIndexes

    (

    Schemaname

    ,tablename

    ,IndexName

    ,IndexUsage

    ,IndexUsageToSizeRatio

    ,IndexKey

    ,IncludedCol

    ,ConstraintType

    ,IndexSizeKB

    ,DropQry

    )

    -- Indexes that does not exist in sys.dm_db_index_usage_stats

    select

    ss.name SchemaName

    ,so.name as TableName

    ,isnull(si.name, 'NoIndex') as IndexName

    ,0 IndexUsage

    ,0 IndexUsageToSizeRatio

    ,dbo.Uf_GetIndexCol(si.index_id, so.object_id, 0) as IndexKey

    ,dbo.Uf_GetIndexCol(si.index_id, so.object_id, 1) as IncludedCol

    ,case when is_primary_key = 1 then 'Primary Key Constraint'

    else 'Index'

    end ConstraintType

    ,dbo.Uf_GetIndexSize(si.index_id, so.object_id) as IndexSizeInKB

    ,case when (is_primary_key = 1) then ('alter table ' + so.name + ' drop constraint ' + si.name)

    else ('Drop Index ' + ss.name + '.' + so.name + '.' + si.name)

    end as DropQry

    from

    sys.objects so

    inner join sys.indexes si

    on so.object_id = si.Object_id

    inner join sys.schemas ss

    on ss.schema_id = so.schema_id

    where

    not exists ( select

    *

    from

    sys.dm_db_index_usage_stats spi

    where

    si.object_id = spi.object_id and si.index_id = spi.index_id ) and so.type = 'U' and ss.schema_id <> 4 and si.index_id > 0 and si.name not in (

    select

    indexname

    from

    tblUnusedIndexes)

    union

    -- Indexes that doesn't satisfy the Indexusage criteria.

    select

    ss.name

    ,b.TableName

    ,b.IndexName

    ,b.IndexUsage

    ,b.IndexUSageToSizeRatio

    ,dbo.Uf_GetIndexCol(b.index_id, object_id(b.tablename), 0) as IndexKey

    ,dbo.Uf_GetIndexCol(b.index_id, object_id(b.tablename), 1) as IncludedCol

    ,b.ConstraintType

    ,dbo.Uf_GetIndexSize(b.index_id, object_id(b.tablename)) as IndexSizeInKB

    ,case b.ConstraintType

    when 'Index' then ('Drop Index ' + ss.name + '.' + b.TableName + '.' + b.IndexName)

    else ('alter table ' + b.TableName + ' drop constraint ' + b.IndexName)

    end DropQry

    from

    tblIndexUsageInfo b

    ,sys.tables st

    ,sys.schemas ss

    where

    (

    b.indexusage <= @indexUsage or IndexUsageToSizeRatio <= @IndexUsageToSizeRatio

    ) and st.name = tablename and st.schema_id = ss.schema_id and b.indexname not in (select

    indexname

    from

    tblUnusedIndexes)

    group by

    b.indexname

    ,b.tablename

    ,ss.name

    ,ss.schema_id

    ,b.ConstraintType

    ,b.index_id

    ,b.indexusage

    ,b.IndexUsageToSizeRatio

    end

    go

    create proc proc_DropUnusedIndex @UnusedIndID int

    as

    begin

    declare @SqlStr varchar(4000)

    select

    @SqlStr = DropQry

    from

    tblunusedindexes

    where

    UnusedIndid = @UnusedIndID

    begin tran

    begin try

    execute(@SqlStr)

    update

    tblunusedindexes

    set

    IndexStatus = 'Dropped'

    where

    UnusedIndID = @UnusedIndID

    end try

    begin catch

    select

    error_message() as ErrorMessage

    if @@TRANCOUNT > 0

    rollback transaction ;

    end catch

    if @@TRANCOUNT > 0

    commit transaction

    print 'Index dropped Successfully'

    end

    go

    create table tblMissingIndexes

    (

    Sno int identity(1, 1)

    ,DatabaseName varchar(100)

    ,tablename varchar(200)

    ,Significance decimal(10, 0)

    ,CreateIndexStatement varchar(8000)

    ,Status varchar(20) default ('NotCreated')

    )

    go

    create procedure proc_FindMissingIndexes

    as

    begin

    insert into tblMissingIndexes

    (

    DatabaseName

    ,tablename

    ,Significance

    ,CreateIndexStatement

    )

    select

    db_name(sid.database_id)

    ,sid.statement

    ,(avg_total_user_cost * avg_user_impact) * (user_scans + user_seeks) as Significance

    ,dbo.fn_CreateIndexStmt(sid.statement, sid.equality_columns, sid.inequality_columns, sid.included_columns)

    from

    sys.dm_db_missing_index_details sid

    ,sys.dm_db_missing_index_group_stats sigs

    ,sys.dm_db_missing_index_groups sig

    where

    sig.index_group_handle = sigs.group_handle and sid.index_handle = sig.index_handle

    order by

    significance desc

    end

    go

    create function fn_CreateIndexStmt

    (

    @statement varchar(1000)

    ,@Equalitycols varchar(1000)

    ,@InEqualitycols varchar(1000)

    ,@Includedcols varchar(1000)

    )

    returns varchar(5000)

    as

    begin

    declare

    @STR varchar(5000)

    ,@tablename varchar(100)

    set @tablename = substring(substring(@statement, charindex('.', @statement) + 1, len(@statement)),

    charindex('.', substring(@statement, charindex('.', @statement) + 1, len(@statement))) + 1,

    len(substring(@statement, charindex('.', @statement) + 1, len(@statement))))

    set @Includedcols = replace(replace(@Includedcols, ']', ''), '[', '')

    set @Equalitycols = replace(replace(replace(@Equalitycols, ']', ''), ', ', '_'), '[', '')

    set @InEqualitycols = replace(replace(replace(@InEqualitycols, ']', ''), ', ', '_'), '[', '')

    set @STR = 'Create Index Ix_' + replace(replace(@tablename, ']', ''), '[', '')

    set @STR = case when @Equalitycols is null then @STR

    else (@str + '_' + isnull(@Equalitycols, ''))

    end

    set @STR = case when @InEqualitycols is null then @STR

    else (@str + '_' + isnull(@InEqualitycols, ''))

    end

    set @STR = @STR + ' ON ' + @statement + '(' + case when @Equalitycols is null then ''

    else replace(isnull(@Equalitycols, ''), '_', ',')

    end + case when @InEqualitycols is null then ''

    else ',' + replace(isnull(@InEqualitycols, ''), '_', ',')

    end + ')'

    set @STR = case when @Includedcols is null then @STR

    else @STR + 'Include (' + isnull(@Includedcols, '') + ')'

    end

    return @STR

    end

    go

    create procedure proc_CreateMissingIndexes

    @significance decimal(10, 0)

    as

    begin

    declare

    @Count int

    ,@SqlStr varchar(8000)

    set @SqlStr = ''

    select

    identity( int,1,1 ) as Sno

    ,CreateIndexStatement

    into

    #temp

    from

    tblmissingindexes

    where

    significance > @significance

    select

    @count = count(*)

    from

    #temp

    while (@count >= 0)

    begin

    select

    @SqlStr = CreateIndexStatement

    from

    #temp

    where

    sno = @count

    update

    tblmissingindexes

    set

    Status = 'Created'

    where

    sno = @count

    exec(@sqlStr)

    set @count = @Count - 1

    end

    end


    Over 12yrs in IT and 10yrs happily stuck with SQL.
    - SQL 2008/R2/2012/2014/2016/2017
    - Oracle 8/9/10/11
    - MySQL 4/5 and MariaDB

  • I'd like to mention that the article is really great and the errors in the script are solveable. The one with the wrong column-name was tricky, the rest just a little doing. I already created a lot of new indexes in my databases today 🙂

Viewing 15 posts - 1 through 15 (of 85 total)

You must be logged in to reply to this topic. Login to reply