This will be my last blog about the limitations of the missing index feature. This time I will demonstrate that the missing index feature will not suggest filtered indexes. Filtered indexes reduce the index size and thus the storage allocation in the data file. Rebuilding or reorganizing these indexes also requires fewer resources. So it’s worth creating filtered indexes when meaningful.
The following example will prove that the missing index feature will not consider creating filtered indexes.
/**************************************************************************

missing index feature - limitations
It does not suggest filtered indexes

**************************************************************************/

DROP TABLE IF EXISTS [dbo].[missingIndex];
GO

-- Create a table
CREATE TABLE [missingIndex]
(
	[col1] int NOT NULL
  , [col2] int NULL
);
GO
;

-- Insert 100.000 test records into the table
WITH
	[cte] AS
	(
		SELECT [numbers].[col1]
		FROM
			(
				VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)
			) AS [numbers] ([col1])
	)
INSERT [dbo].[missingIndex]
	(
		[col1]
	  , [col2]
	)
SELECT
	[c1].[col1]
  , CASE WHEN [c1].[col1] < 9 THEN NULL ELSE [c1].[col1] END AS [col2]
FROM
	[cte] AS [c1]
CROSS JOIN [cte] AS [c2]
CROSS JOIN [cte] AS [c3]
CROSS JOIN [cte] AS [c4]
CROSS JOIN [cte] AS [c5];
GO
-- Uneven distribution 
-- 90,000 NULL 
-- 10,000 9 
SELECT
	COUNT(*), [col2]
FROM
	[dbo].[missingIndex]
GROUP BY
	[col2];
GO
-- Index recommendation from missing index dmv 
-- Costs : 0,268838 
-- Table 'missingIndex'. Scan count 1, logical reads 211 
-- The Query Processor estimates that implementing the following index could 
-- improve the query cost by 73.2966%. 
SELECT *
FROM
	[dbo].[missingIndex]
WHERE
	[col2] = 9
OPTION (QUERYTRACEON 8757);
GO

-- Index recommendation from missing index dmv 
-- Create a non clustered index including col1 
CREATE NONCLUSTERED INDEX [missing_index_feature]
ON [dbo].[missingIndex] ([col2])
INCLUDE ([col1]);
GO

-- 272 pages are allocated for the non clustered index 
SELECT *
FROM
	[sys].[dm_db_index_physical_stats](
										  DB_ID()
										, OBJECT_ID('missingIndex', 'U'), NULL
										, NULL, 'detailed'
									  )
WHERE
	[index_id] > 1;
GO

-- Flush pages from buffer
CHECKPOINT;
DBCC DROPCLEANBUFFERS;
GO

-- Costs : 0,0336894
-- Index Seek
-- Table 'missingIndex'. Scan count 1, logical reads 30
SELECT *
FROM
	[dbo].[missingIndex]
WHERE
	[col2] = 9;
GO

-- Get the pages in buffer pool for the table missingIndex
-- 29 pages are loaded into cache
SELECT
	DB_NAME(DB_ID()) AS [db_name]
  , OBJECT_SCHEMA_NAME([object_id], DB_ID()) AS [schema_name]
  , OBJECT_NAME([object_id]) AS [object_name]
  , [file_id]
  , [page_id]
  , [page_level]
  , [page_type]
  , [row_count]
  , ([free_space_in_bytes])
  , ([read_microsec]) AS [read_microsec]
FROM
	[sys].[dm_os_buffer_descriptors] AS [bd]
INNER JOIN
	(
		SELECT
			[object_id], [index_id], [allocation_unit_id]
		FROM
			[sys].[allocation_units] AS [au]
		INNER JOIN
			[sys].[partitions] AS [p]
		ON
			[au].[container_id] = [p].[hobt_id]
			AND
				(
					[au].[type] = 1
					OR [au].[type] = 3
				)
		UNION ALL
		SELECT
			[object_id], [index_id], [allocation_unit_id]
		FROM
			[sys].[allocation_units] AS [au]
		INNER JOIN
			[sys].[partitions] AS [p]
		ON
			[au].[container_id] = [p].[partition_id]
			AND [au].[type] = 2
	) AS [obj]
ON
	[bd].[allocation_unit_id] = [obj].[allocation_unit_id]
WHERE
	[database_id] = DB_ID()
	AND [object_id] = OBJECT_ID('missingIndex', 'U');
GO

-- Drop index
DROP INDEX IF EXISTS [missing_index_feature] ON [missingIndex];
GO
-- Create a filtered on col2
-- Col2 IS NOT NULL
-- Improvement : 94,38%
CREATE NONCLUSTERED INDEX [filtered_index]
ON [dbo].[missingIndex] ([col2] ASC)
INCLUDE ([col1])
WHERE ([col2] IS NOT NULL);
GO

-- 28 pages are allocated for the non clustered filtered index
SELECT *
FROM
	[sys].[dm_db_index_physical_stats](
										  DB_ID()
										, OBJECT_ID('missingIndex', 'U'), NULL
										, NULL, 'detailed'
									  )
WHERE
	[index_id] > 1;
GO

-- Flush pages from buffer
CHECKPOINT;
DBCC DROPCLEANBUFFERS;
GO

-- Costs : 0,0156153
-- Index Seek
-- Table 'missingIndex'. Scan count 1, logical reads 30
SELECT *
FROM
	[dbo].[missingIndex]
WHERE
	[col2] = 9;
GO

-- Get the pages in buffer pool for the table missingIndex
-- 29 pages are loaded into cache
SELECT
	DB_NAME(DB_ID()) AS [db_name]
  , OBJECT_SCHEMA_NAME([object_id], DB_ID()) AS [schema_name]
  , OBJECT_NAME([object_id]) AS [object_name]
  , [file_id]
  , [page_id]
  , [page_level]
  , [page_type]
  , [row_count]
  , ([free_space_in_bytes])
  , ([read_microsec]) AS [read_microsec]
FROM
	[sys].[dm_os_buffer_descriptors] AS [bd]
INNER JOIN
	(
		SELECT
			[object_id], [index_id], [allocation_unit_id]
		FROM
			[sys].[allocation_units] AS [au]
		INNER JOIN
			[sys].[partitions] AS [p]
		ON
			[au].[container_id] = [p].[hobt_id]
			AND
				(
					[au].[type] = 1
					OR [au].[type] = 3
				)
		UNION ALL
		SELECT
			[object_id], [index_id], [allocation_unit_id]
		FROM
			[sys].[allocation_units] AS [au]
		INNER JOIN
			[sys].[partitions] AS [p]
		ON
			[au].[container_id] = [p].[partition_id]
			AND [au].[type] = 2
	) AS [obj]
ON
	[bd].[allocation_unit_id] = [obj].[allocation_unit_id]
WHERE
	[database_id] = DB_ID()
	AND [object_id] = OBJECT_ID('missingIndex', 'U');
GO
-- Housekeeping
DROP TABLE IF EXISTS [dbo].[missingIndex];
GO

The table I created contains of 100,000 records but has an uneven distribution on col2. Only 10,000 or 10% of the records has a value for col2.
SQL Server recommends to create an index on col2 which would improve the execution by 73%.
missing-index-feature-recommendation-execution-plan
When we create the index suggested from the missing index feature, we can see an improvement. The engine uses the index, which reduces logical reads from 211 to 30 pages and the costs are down from 0.268838 to 0.0336894.
The new index allocates 272 pages in the leaf of the index B-Tree, which is approx. 2MB.
Worth to mention that SQL Server only saved 29 pages in the buffer pool because the engine was able to apply a predicate push down.
missing-index-feature-recommendation-index-size