首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >如何获取“筛选索引”缺失的索引

问如何获取“筛选索引”缺失的索引
EN

Database Administration用户
提问于 2015-09-09 15:07:42
回答 1查看 354关注 0票数 2

我有一个程序显示丢失的索引。

这对我很好,但它并没有向我展示丢失的过滤指标。

我希望我的过程能够捕获并建议过滤的索引和表索引,这可能吗?

这是我目前的程序:

代码语言:javascript
复制
USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
BEGIN TRY
    PRINT  @@SERVERNAME
    PRINT  DB_NAME()
    PRINT 'PROCEDURE sp_GetMissingIndexes'
    DROP PROCEDURE sp_GetMissingIndexes
    print 'dropped PROCEDURE sp_GetMissingIndexes'
END TRY
BEGIN CATCH
END CATCH
GO



CREATE PROCEDURE sp_GetMissingIndexes
     @dbname    SYSNAME = NULL
    ,@TableName SYSNAME = NULL
    WITH ENCRYPTION
AS
/*
=======================================================================
Script  :   sp_GetMissingIndexes
Author  :   Marcelo Miorelli
Date    :   15-dec-2014
Desc    :   get the list of missing indexes, database and table can be specified

Usage   :   sp_GetMissingIndexes                       -- shows all missing indexes for ALL databases

            sp_GetMissingIndexes 'Bocss2'              -- all missing indexes in BOCSS2'

            sp_GetMissingIndexes 'distribution_BOCSS' ,'MSdistribution_history'   

            sp_GetMissingIndexes 'dbo.tblBCNEOutFile'  -- Error -- the first parameter must be database

            sp_GetMissingIndexes 'Bocss2', 'tblProdDataValue'  -- the missing indexes for table tblProdDataValue in BOCSS2

            sp_GetMissingIndexes 'Bocss2', 'non_existing_table'  -- Error -- the table does not exist

Links   :

            --http://msdn.microsoft.com/en-us/library/ms345421.aspx
            --http://www.geniiius.com/blog/missing-indexes
            --http://stackoverflow.com/questions/3718111/sql-server-dmv-sys-dm-db-missing-index-group-stats-what-do-these-columns-mea


=======================================================================
History
Date          Action        User                Desc
-----------------------------------------------------------------------
15-dec-2014   created       Marcelo Miorelli
19-mar-2015   ammended      Marcelo Miorelli    incorporate usage of procedure sp_GetObjectID

=======================================================================
*/

--======================================
-- describe primary blocks of processing
--======================================

------------------------------------------------
-- describe action of logical groups of commANDs
------------------------------------------------

-- describe individual actions within a commAND set



SET NOCOUNT ON
SET DATEFORMAT DMY
SET DEADLOCK_PRIORITY NORMAL;

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

    DECLARE @sql NVARCHAR(MAX)
    DECLARE @ParamDefinition NVARCHAR(MAX)
    DECLARE @TableID INT  

    DECLARE  @log NVARCHAR(MAX)
            ,@vCrlf CHAR(2);

    DECLARE @ERR_SEV   SMALLINT,
            @ERR_STA   SMALLINT,
            @obj_type  varchar(3)

    SELECT  @log = ''
           ,@TableID = 0
           ,@vCrlf = CHAR(13)+CHAR(10);

 --   declare  @dbname    SYSNAME
    --declare @TableName SYSNAME
    --select @dbname = 'bocss2'
    --select @tablename = 'tblProdDataValue'

    DECLARE @OBJECTS TABLE(
            [db_id] [smallint] NOT NULL,
            [object_id] [int] NOT NULL,
            [s_name] [sysname] NOT NULL,
            [name] [sysname] NOT NULL,
            [row_count] [bigint] NOT NULL,
            [Statistics_Updated] [datetime] NULL,
            PRIMARY KEY CLUSTERED (DB_ID,OBJECT_ID))



    IF (@dbname IS NOT NULL) AND (DB_ID(@dbname) IS NULL)  /*Validate the database name exists*/
       BEGIN


           SELECT @sql = 'The DATABASE called %s does not exist in the current server.' + @vCrlf + @vCrlf +
                         'sp_GetMissingIndexes usage:' + @vCrlf +
                         'sp_GetMissingIndexes @dbname SYSNAME, @TableName SYSNAME' + @vCrlf +
                         'Example:' + @vCrlf +
                         'sp_GetMissingIndexes ''Bocss2'', ''tblProdDataValue''  -- the missing indexes for table tblProdDataValue in BOCSS2' + @vCrlf

           RAISERROR(@sql ,16,1,@dbname)
           RETURN (-1)

       END

    IF @TableName IS NOT NULL /*validate the index exists in the @dbname database*/
       BEGIN

        BEGIN TRY 

            EXEC sp_GetObjectID @TableName,@TABLEID OUT,@obj_type OUT,1,@dbname


        END TRY
        BEGIN CATCH

            SELECT  @ERR_SEV = ERROR_SEVERITY(),
                    @ERR_STA = ERROR_STATE(),
                    @sql = 'The table called %s does not exist on database %s' + @vCrlf + @vCrlf +
                                 'sp_GetMissingIndexes usage:' + @vCrlf +
                                 'sp_GetMissingIndexes @dbname SYSNAME, @TableName SYSNAME' + @vCrlf +
                                 'Example:' + @vCrlf +
                                 'sp_GetMissingIndexes ''Bocss2'', ''tblProdDataValue''  -- the missing indexes for table tblProdDataValue in BOCSS2' + @vCrlf +
                                 @vCrlf + @vCrlf +
                    ERROR_MESSAGE()


            RAISERROR (@SQL,@ERR_SEV, @ERR_STA,@TableName,@dbName) WITH NOWAIT
            RETURN (-1)

        END CATCH

       END


    --============================================================================================
    -- passed all the reuirements, so we run the script
    --============================================================================================


  BEGIN TRY

SELECT @SQL = '


;with radhe_obj_and_partitions as (

SELECT db_id=db_id(),
       o.object_id,
       s_name=OBJECT_SCHEMA_NAME(o.object_id),
       o.name,
       ddps.row_count
       ,[Statistics_Updated]=stats_date(i.object_id,i.index_id)
FROM sys.indexes AS i
  INNER JOIN sys.objects AS o ON i.object_id = o.object_id
  INNER JOIN sys.dm_db_partition_stats AS ddps ON i.object_id = ddps.object_id
  AND i.index_id = ddps.index_id 
WHERE i.index_id < 2  
 AND ((@table = 0) OR (o.OBJECT_ID = @table))

)
SELECT 
db_id,
object_id,
s_name,
name,
row_count=SUM(row_count),
Statistics_Updated=MAX(Statistics_Updated)
FROM radhe_obj_and_partitions 
GROUP BY 
db_id,
object_id,
s_name,
name
 '

SET @ParamDefinition = N'@table int OUTPUT';
set @SQL = (
            SELECT STUFF(
  (SELECT N'  ' + ' USE ' + QUOTENAME(name) +';' + @vCrlf + @SQL + @vCrlf    
                                FROM SYS.DATABASES SD
                                WHERE SD.STATE_DESC = 'ONLINE' -->Skips the database if it is not online
                                  AND NAME NOT IN ('master','tempdb','model')
                                  AND SD.COMPATIBILITY_LEVEL > 80  
                                  AND (name = @DBName  OR @DBName IS NULL) 

      FOR XML PATH(''),TYPE)
  .value('text()[1]','nvarchar(max)'),1,2,N'')
)

INSERT INTO @OBJECTS
(   [db_id],
    [object_id],
    [s_name],
    [name],
    [row_count],
    [Statistics_Updated]
)
EXECUTE MASTER.DBO.sp_executesql @SQL, @ParamDefinition, @table= @TableID

--SELECT * FROM @OBJECTS


;WITH Radhe  
AS  
(  

            SELECT  
            DB_NAME(d.database_id) AS DatabaseName,  
            O.s_name AS The_Schema,
            o.name,
            o.row_count,
            unique_compiles,      --Number of compilations and recompilations that would benefit from this missing index group. Compilations and recompilations of many different queries can contribute to this column value.
            user_seeks,           --Number of seeks caused by user queries that the recommended index in the group could have been used for.
            user_scans,           --Number of scans caused by user queries that the recommended index in the group could have been used for.
            avg_total_user_cost = CAST (avg_total_user_cost AS NUMERIC(18,2)),  --Average cost of the user queries that could be reduced by the index in the group.
            avg_user_impact,      --Average percentage benefit that user queries could experience if this missing index group was implemented. The value means that the query cost would on average drop by this percentage if this missing index group was implemented.
            last_user_seek,
            last_user_scan,

            d.equality_columns,  
            d.inequality_columns,  
            d.included_columns

            ,o.Statistics_Updated

            ,'USE ' + DB_NAME(d.database_id) + '; CREATE NONCLUSTERED INDEX IDX_' +  
            REPLACE(REPLACE(REPLACE(REPLACE(COALESCE(equality_columns, '') +  
            COALESCE(inequality_columns, ''), ',', '_'), '[', ''),']', ''), ' ', '') +  
            CASE WHEN included_columns IS NOT NULL  
            THEN '_INC_' + REPLACE(REPLACE(REPLACE(REPLACE(included_columns, ',', '_'), '[', ''),']', ''), ' ', '')  
            ELSE '' END + ' ON ' + statement + ' (' +  
            CASE  
            WHEN equality_columns IS NOT NULL AND inequality_columns IS NOT NULL  
            THEN equality_columns + ', ' + inequality_columns  
            WHEN equality_columns IS NOT NULL AND inequality_columns IS NULL  
            THEN equality_columns  
            WHEN equality_columns IS NULL AND inequality_columns IS NOT NULL  
            THEN inequality_columns  
            END + ')' +  
            CASE WHEN included_columns IS NOT NULL THEN ' INCLUDE (' +  
            REPLACE(REPLACE(REPLACE(included_columns, '[', ''),']', ''), ' ', '') + ')'  
            ELSE '' END +  
            CASE WHEN @@Version LIKE '%Enterprise%' THEN ' WITH (ONLINE = ON)'  
            ELSE '' END AS CreateIndex  

                  FROM  sys.dm_db_missing_index_groups g  
            INNER JOIN sys.dm_db_missing_index_group_stats gs ON gs.group_handle = g.index_group_handle  
            INNER JOIN sys.dm_db_missing_index_details d      ON g.index_handle  = d.index_handle  

            INNER JOIN @OBJECTS O                             ON d.database_id   = o.db_id
                                                             AND D.object_id     = o.object_id


            WHERE  (DB_NAME(d.database_id) = @DBName OR @DBName IS NULL)  
)  


        SELECT * 
          FROM Radhe  
        ORDER BY user_seeks DESC  

        RETURN (0)

END TRY
BEGIN CATCH

        PRINT '--EXCEPTION WAS CAUGHT--' + CHAR(13) +
              'THE ERROR NUMBER:' + COALESCE(CAST ( ERROR_NUMBER()  AS VARCHAR), 'NO INFO') + CHAR(13) 

        PRINT 'SEVERITY: '        + COALESCE(CAST ( ERROR_SEVERITY()  AS VARCHAR), 'NO INFO') + CHAR(13) +
              'STATE: '           + COALESCE(CAST ( ERROR_STATE() AS VARCHAR), 'NO INFO')  + CHAR(13) 

        PRINT 'PROCEDURE: '       + COALESCE(CAST ( COALESCE(ERROR_PROCEDURE(),'NO INFO')  AS VARCHAR), 'NO INFO') + CHAR(13) +
              'LINE NUMBER: '     + COALESCE(CAST ( ERROR_LINE() AS VARCHAR), 'NO INFO')  + CHAR(13) 

        PRINT 'ERROR MESSAGE: '
        PRINT  CAST ( COALESCE(ERROR_MESSAGE(),'NO INFO')   AS NTEXT)
        RETURN (-1)

END CATCH;




GO
print 'created PROCEDURE sp_GetMissingIndexes'
exec sys.sp_MS_marksystemobject 'sp_GetMissingIndexes'
GO
EN

回答 1

Database Administration用户

回答已采纳

发布于 2015-09-09 15:15:41

不,这目前是不可能的,因为缺少的索引DMVs没有任何工具来识别筛选索引的机会(因此您将不会看到它们在显示计划输出、数据库引擎优化顾问等方面的推荐)。这些都是你必须识别更多手动的东西。我不认为新的Azure指数顾问也能做到这一点。

注:这是建议的七年前,但关闭不能修复,尽管它被认为是“一个好的建议”。

票数 5
EN
页面原文内容由Database Administration提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://dba.stackexchange.com/questions/114589

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档