Index Management

  • Ahmad Osama

    SSCertifiable

    Points: 7676

    Comments posted to this topic are about the item Index Management

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

  • jay.peri

    Grasshopper

    Points: 13

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

  • darwin.maramot

    SSC Enthusiast

    Points: 157

    hi am kinda new here

    can this be apllied on SQL 2000?

    thanks!

  • dagfinn

    SSC Enthusiast

    Points: 154

    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...)

  • Ahmad Osama

    SSCertifiable

    Points: 7676

    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]

  • Ahmad Osama

    SSCertifiable

    Points: 7676

    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]

  • darwin.maramot

    SSC Enthusiast

    Points: 157

    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!

  • ChiragNS

    One Orange Chip

    Points: 26137

    nice article

    "Keep Trying"

  • Benn Sugden-373263

    Grasshopper

    Points: 19

    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 ...."

  • norbert.heyermeyer

    Grasshopper

    Points: 13

    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

  • jens.murer

    Grasshopper

    Points: 16

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

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

  • ghughes

    SSCrazy

    Points: 2141

    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.

  • pnewhart

    SSC Eights!

    Points: 953

    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?

  • Adam Seniuk

    SSCrazy

    Points: 2221

    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

  • jens.murer

    Grasshopper

    Points: 16

    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