Dynamic Management Objects

Missing Index DMVs and Indexed Views

Tim Ford #dm_db_missing_index_details, #indexed views, #missing, index 3 Comments Jan 10, 2014

The Question at Hand

I recently had someone ask about whether the missing index dynamic management objects returned results for the indexed view or the underlying table.  If you were to go strictly by the what is presented from the Microsoft TechNet the answer would be: the underlying table.  That answer would be wrong.

Let’s Turn this Mutha Out

For this example I’m going to create a copy of an existing table I have that stores metadata about all the databases in my environment, their file sizes, growth units, and data collection information.  There is approximately 500,000 records in total.  On this table I have a primary key defined with an associated clustered index on a composite of server, database, logical file name, and date stamp as well as a handful of non-clustered indexes.  The table I’m creating will be a copy of that table, but I’m adding an identity column (id) and creating the primary key clustered index on just that column.

 

CREATE TABLE [dbo].[aaa_Files]
    (
		 [id] INT IDENTITY(1, 1)
	   , [Server] [nvarchar](128) NOT NULL
	   , [DatabaseName] [nvarchar](128) NOT NULL
	   , [Name] [nvarchar](128) NOT NULL
	   , [Filename] [nvarchar](260) NOT NULL
	   , [FileType] [varchar](4) NULL
	   , [Size_In_Mb] [int] NOT NULL
	   , [Available_Space_In_Mb] [int] NULL
	   , [Growth_Increments] [int] NOT NULL
	   , [Growth_Units] [varchar](2) NULL
	   , [Max_File_Size_In_Mb] [int] NULL
	   , [date_stamp] [datetime] NOT NULL
    )
ON  [PRIMARY]
GO

ALTER TABLE dbo.aaa_Files ADD CONSTRAINT [PK_aaa_Files] PRIMARY KEY CLUSTERED ([id]);
GO

INSERT INTO dbo.aaa_Files
    (
	Server
	, DatabaseName
	, Name
	, Filename
	, FileType
	, Size_In_Mb
	, Available_Space_In_Mb
	, Growth_Increments
	, Growth_Units
	, Max_File_Size_In_Mb
	, date_stamp
    )

SELECT  Server
      , DatabaseName
      , Name
      , Filename
      , FileType
      , Size_In_Mb
      , Available_Space_In_Mb
      , Growth_Increments
      , Growth_Units
      , Max_File_Size_In_Mb
      , date_stamp FROM dbo.Database_Files_History;
GO

Now I’ll go ahead and create an indexed view on that new table.  Don’t judge me, this view is only for the purpose of this post and would otherwise be completely worthless as would the subsequent query I’m going to run to prove my point:

 

CREATE VIEW dbo.aaa_Files_view 
	WITH SCHEMABINDING AS
		SELECT id AS vw_id
			, DatabaseName AS vw_Database
			, Name AS vw_Name
			, FileType AS vw_FileType
			, Size_In_Mb AS vw_Size_In_Mb
			, date_stamp AS vw_date_stamp
		FROM dbo.aaa_Files;
GO

CREATE UNIQUE CLUSTERED INDEX aaa_Files_view_IndexedView
	ON dbo.aaa_Files_view
		(
			vw_id
			,vw_FileType
		);

I’ve aliased the columns so the results will be obvious later on…

Prove It Real Good

Now, onto the proving!  Let’s run this worthless query (you’ve seen plenty of those before in your environments, right?)

SELECT DISTINCT vw_Name
FROM dbo.aaa_Files_view 
WHERE vw_Size_In_Mb > 86753 AND vw_Size_In_Mb < 8675309;
GO

We don’t care about the results. We’re just looking for activity for the purpose of the next query.

SELECT OBJECT_NAME(MID.object_id) AS the__object
	, MID.* 
FROM sys.dm_db_missing_index_details MID;

 

… and bibitty-bobbity-bacon there you have it; the object_id, object name, statement and all column references point to the indexed view, not the underlying table.