I have a procedure that shows me the missing indexes.
It has been very good to me, but it does not show me the missing FILTERED INDEXES.
I would like my procedure to be able to catch and suggest filtered indexes as well as table indexes, is this possible?
this is my current procedure:
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
Best Answer
No, this is currently not possible, since the missing index DMVs do not have any facility for identifying opportunities for filtered indexes (and hence you won't see them recommended in showplan output, database engine tuning advisor, etc). These are things you have to identify a little more manually. I don't think the new Azure Index Advisor does this, either.
Note: This was suggested seven years ago, but closed as won't fix, even though it was noted as "a good suggestion."