在sql server 2005里，根据数据库性能动态构建索引。

在sql server 2005里，根据数据库性能动态构建索引。

数据库设计好后，系统上线运动一个周期后，数据库性能瓶颈突现出来，这个时间，需要一种根据性能，来动态构建索引，提高查询效率。

以下为SQL 原码。

-- 添加 IX_AutoIndex_ 系列索引   

/*
    --过程优化SQL
    SELECT   [RowNumber] =  row_number() over (order by (avg_user_impact ) ) , [Total Cost]  = ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans),0)
                    , avg_user_impact
                    , TableName = statement
                    , [EqualityUsage] = equality_columns
                    , [InequalityUsage] = inequality_columns
                    , [Include Cloumns] = included_columns
                    FROM        sys.dm_db_missing_index_groups g
                    INNER JOIN    sys.dm_db_missing_index_group_stats s
                           ON s.group_handle = g.index_group_handle
                    INNER JOIN    sys.dm_db_missing_index_details d
                           ON d.index_handle = g.index_handle
                    ORDER BY [RowNumber] ;       
--查字段类型
        select
        ColumnsName = sc.name ,
        DataType = st.name,
        DataLength = sc.Length
        from lzmisplat..syscolumns sc
        inner join lzmisplat..sysobjects so on sc.id=so.id
        inner join lzmisplat..SysTypes st on sc.XType=st.XType
        where
        so.name='S_FlowInstance' and sc.name='State'


    */
    declare @TableName varchar(1000)
    declare @EqualityUsage varchar(1000)
    declare @InequalityUsage varchar(1000)
    declare @IncludeCloumns varchar(1000)

    declare @IndexTemplete    nvarchar(2000)
    declare @IndexIncludeTemplete    nvarchar(2000)
    declare @Templete    nvarchar(2000)
    declare @ExecuteSql    nvarchar(4000)
   
    declare @NowTime  varchar(500)
    declare @IndexName  varchar(500)
    declare @RowNumber  int

    -----------------

    select @NowTime = replace(  replace( replace( getdate() ,'-','') ,' ','') ,':',''), @ExecuteSql =''
   
    select @IndexTemplete = '
    CREATE NONCLUSTERED
    INDEX [IndexName]

    ON [TableName]

    (
        [EqualityUsage] [InequalityUsage]
    )
    ', @IndexIncludeTemplete = '
    include(
    [IncludeCloumns]
    )
    ',@Templete =
    '
    WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF,
    SORT_IN_TEMPDB = ON, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF,
    ONLINE = ON, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF)
     ON [PRIMARY]

    '
   
    --建立临时表，存放错误信息
    create table #tmp_ErrorInfo
    (
        sqlContent varchar(4000) ,
        ErrorDes varchar(max)
    )

    declare cursor_index cursor for
        SELECT 
                  [RowNumber] =  row_number() over (order by (avg_user_impact ) )
                , [TableName] = statement
                , [EqualityUsage] = equality_columns
                , [InequalityUsage] = inequality_columns
                , [IncludeCloumns] = included_columns
                FROM        sys.dm_db_missing_index_groups g
                INNER JOIN    sys.dm_db_missing_index_group_stats s
                       ON s.group_handle = g.index_group_handle
                INNER JOIN    sys.dm_db_missing_index_details d
                       ON d.index_handle = g.index_handle
                where ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans),0) > 1
                ORDER BY [RowNumber];       

    open cursor_index

    fetch next from cursor_index into @RowNumber,@TableName,@EqualityUsage , @InequalityUsage,@IncludeCloumns
    while @@fetch_status = 0
    begin
           
            if ( @EqualityUsage is null )
                 set @EqualityUsage    = ''
            else
                if ( @InequalityUsage is not null )
                    set  @InequalityUsage = ',' + @InequalityUsage
                else
                    set  @InequalityUsage = ''

            select @IndexName = 'IX_AutoIndex_' + replace( replace(  replace( replace( @TableName , '[','') , ']','') , ']',''), '.dbo.','')  + '_' + @NowTime  + '_' + ltrim( str( @RowNumber ) ) 
                   
            --拼SQL
            select @ExecuteSql =  replace ( replace ( replace ( replace ( @IndexTemplete  , '[IndexName]' , @IndexName ) , '[TableName]' , @TableName )  , '[EqualityUsage]' , @EqualityUsage )   , '[InequalityUsage]' , @InequalityUsage  )
                +  isnull( replace ( @IndexIncludeTemplete  , '[IncludeCloumns]' , @IncludeCloumns ) , '' )      + @Templete

            --print     @ExecuteSql   
           
            begin try
                Execute ( @ExecuteSql )
            end try
            begin catch
                insert into #tmp_ErrorInfo select  @ExecuteSql ,ERROR_MESSAGE()
               
            end catch
       
          fetch next from cursor_index into @RowNumber,@TableName,@EqualityUsage , @InequalityUsage,@IncludeCloumns
    end
    close cursor_index         --关闭游标并且销毁
    deallocate cursor_index

    select * from #tmp_ErrorInfo
    truncate table #tmp_ErrorInfo
    drop table #tmp_ErrorInfo