By: Jared Westover | Updated: 2024-09-11 | Comments | Related: > Performance Tuning
Problem
In the past, while using the missing index Dynamic Management Views (DMVs), something always seemed to be missing from the results. It was hard to put my finger on it then, but looking back, it now seems obvious. Why can't we see the queries prompting SQL Server to give suggestions? Did you know Microsoft added a DMV with the query text? Since discovering this gem, we no longer need to search through Plan Cache or Query Store.
Solution
In this article, I'll review a DMV that Microsoft added in SQL Server 2019. This DMV helps find queries that trigger the missing index recommendations. I'll start by exploring the common DMVs Microsoft provided before 2019. Finally, we'll take a close look at this new hidden gem in SQL Server 2019.
Missing Index Recommendations
When someone asks me for help with tuning a query, one of the first things I'll do is enable the actual execution plan and run it. There's nothing better than seeing that green text indicating SQL Server thinks you should add an index. The feeling is even better when the impact shows 90% or higher. But this feeling of exhilaration quickly leads to sadness when the optimizer wants you to include almost all the columns in a table. The million-dollar question is: Should you create that index? This is where the art of indexing comes into play.
Missing Index DMVs
Looking at one query at a time can be helpful, but you often don't know when queries are slow or when the optimizer has a suggestion. Running every query through SSMS to check the execution plan isn't practical for most people. Good luck using that approach with an ORM like Entity Framework.
For me, the easiest way to find all missing index suggestions is by using some helpful DMVs. However, you don't want the built-in DMVs to be your only index-creating tool since SQL Server offers a recommendation for nearly every query with a filter or join predicate. But they're a nice addition to your toolbox.
Building Test Data
To explore the missing index DMVs, we need to build a dataset and execute a few queries against it. The query below creates one database and one table with one million rows, big enough to work for this example.
-- mssqltips.com
USE master;
IF DATABASEPROPERTYEX('MissingIndexDemo', 'Version') IS NOT NULL
BEGIN
ALTER DATABASE MissingIndexDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE MissingIndexDemo;
END;
GO
CREATE DATABASE MissingIndexDemo;
ALTER DATABASE MissingIndexDemo SET RECOVERY SIMPLE;
GO
USE MissingIndexDemo;
GO
CREATE TABLE dbo.BigTable
(
Column1 INT IDENTITY(1, 1) NOT NULL,
Column2 INT NOT NULL,
Column3 INT NOT NULL,
Column4 INT NULL,
Column5 VARCHAR(2000)
CONSTRAINT PK_BigTable_Column1
PRIMARY KEY CLUSTERED (Column1)
);
INSERT INTO dbo.BigTable
(
Column2,
Column3,
Column4,
Column5
)
SELECT (ABS(CHECKSUM(NEWID()) % (100000 - 1 + 1)) + 1) AS Column2,
(ABS(CHECKSUM(NEWID()) % (10000 - 1 + 1)) + 1) AS Column3,
(ABS(CHECKSUM(NEWID()) % (100 - 1 + 1)) + 1) AS Column4,
SUBSTRING(
REPLICATE('abcdefghijklmnopqrstuvwxyz', 2),
(ABS(CHECKSUM(NEWID())) % 26) + 1,
(ABS(CHECKSUM(NEWID()) % (1000 - 100 + 1)) + 100)
) AS Column5
FROM
(
SELECT TOP 1000000
ROW_NUMBER() OVER (ORDER BY s1.object_Id) AS Number
FROM sys.all_columns AS s1
CROSS JOIN sys.all_columns AS s2
) numbers;
GO
Next, we need to look at a query that runs often in my environment. The 1 = (SELECT 1) predicate forces SQL Server to generate a non-trivial plan. Check out this article by Erik Darling to learn more. If SQL Server picks a trivial plan, it skips this step in the optimization process to provide the index recommendation.
-- mssqltips.com
SELECT Column1,
Column5
FROM dbo.BigTable
WHERE Column4 = 10
AND 1 =
(
SELECT 1
);
GO
Results:
If this query runs often and we can't rewrite it, adding an index seems obvious. However, we often don't know which query triggers the optimizer to recommend an index.
Before SQL Server 2019, I relied on three DMVs to find missing index information. There are more available, but these three were (and still are) my go-to:
- sys.dm_db_missing_index_groups: This view captures missing index groups and behaves like the bridge table between the other two DMVs.
- sys.dm_db_missing_index_group_stats: This view shows how many times the optimizer recommended the missing index, the last time it was recommended, and other related stats.
- sys.dm_db_missing_index_details: This view details which columns to add as the key and which ones to include.
Below, you'll find a sample query to pull missing index data. The query isn't original—everyone, including Microsoft, has a version of it. The columns I find helpful are listed in the code but feel free to add more if needed. I also included the sqlserver_start_time column from the DMV sys.dm_os_sys_info. If a DBA restarted the service yesterday, I'll likely wait a few days before reviewing the results.
-- mssqltips.com
SELECT TOP (25)
CAST(migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) AS DECIMAL(20, 2)) AS improvement,
CAST(migs.avg_total_user_cost AS DECIMAL(20, 2)) AS avg_total_user_cost,
CAST(migs.avg_user_impact AS DECIMAL(20, 2)) AS avg_user_impact,
migs.user_scans,
migs.user_seeks,
migs.last_user_scan,
migs.last_user_seek,
DB_NAME(mid.database_id) AS database_name,
mid.statement AS user_table,
mid.equality_columns,
mid.inequality_columns,
mid.included_columns,
(
SELECT TOP 1 sqlserver_start_time FROM sys.dm_os_sys_info
) AS start_time
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs
ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid
ON mig.index_handle = mid.index_handle
WHERE mid.database_id = DB_ID()
ORDER BY improvement DESC;
GO
Results:
Let's review a few columns from the query:
- Improvement: We calculate this column based on the sum of seeks and scans multiplied by the cost and impact. I first saw this formula in Glenn Berry's performance scripts. You need a larger sample of queries and executions to glean something meaningful from this metric, but I pay close attention to it.
- user_seeks: This column indicates how often the recommended index could have helped one or more queries.
- last_user_seek: This column shows the last date and time SQL gave the recommendation. If a few weeks passed without a recommendation, it would weigh into my decision to create the index.
Based on the additional columns below, the optimizer suggests creating an index with Column4 as the key and including Column5 to cover the query.
It's important to note that different queries can generate the same index recommendation.
Multiple Queries
In the previous section, I shared one query that runs often. Yet, it's not the only query. The screenshot above indicates seven user seeks, but several queries contribute to the total. Before SQL Server 2019, I usually searched the Plan Cache using a script by Jonathan Kehayias to find any plans with a missing index, or I hoped someone had enabled Query Store. But everything changed after we upgraded to SQL Server 2019.
New SQL Server 2019 DMV
Microsoft added tons of new features and fixes to SQL Server 2019, including:
- Scalar UDF inlining.
- Batch mode on rowstore.
- Table variable deferred compilation.
One of my favorite additions was a new DMV called sys.dm_db_missing_index_group_stats_query. I know it's a mouthful, but that last word, "query," is the key. This DMV works with sys.dm_db_missing_index_group_stats, and it includes something important: the last_sql_handle. You can pass the last_sql_handle into the Dynamic Management Function (DMF) sys.dm_exec_sql_text and return the query text.
The example below is like the previous query for finding missing indexes but uses the new DMV. The sql_text column comes directly from the Microsoft documentation.
-- mssqltips.com
SELECT TOP (25)
improvement = CAST((misq.avg_total_user_cost * misq.avg_user_impact * (misq.user_seeks + misq.user_scans)) AS DECIMAL(20, 2)),
(SUBSTRING(
sql_text.text,
misq.last_statement_start_offset / 2 + 1,
(CASE misq.last_statement_start_offset
WHEN-1 THEN
DATALENGTH(sql_text.text)
ELSE
misq.last_statement_end_offset
END - misq.last_statement_start_offset
) / 2 + 1
)
) AS sql_text,
CAST(misq.avg_total_user_cost AS DECIMAL(20, 2)) AS avg_total_user_cost,
CAST(misq.avg_user_impact AS DECIMAL(20, 2)) AS avg_user_impact,
misq.user_scans,
misq.user_seeks,
misq.last_user_seek,
misq.last_user_scan,
DB_NAME(mid.database_id) AS database_name,
mid.statement AS user_table,
mid.equality_columns,
mid.inequality_columns,
mid.included_columns
FROM sys.dm_db_missing_index_group_stats_query AS misq
INNER JOIN sys.dm_db_missing_index_groups AS mig
ON mig.index_group_handle = misq.group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid
ON mig.index_handle = mid.index_handle
CROSS APPLY sys.dm_exec_sql_text(misq.last_sql_handle) AS sql_text
WHERE mid.database_id = DB_ID()
ORDER BY improvement DESC;
GO
Results:
With the sql_text column, we can now see all the queries triggering the missing index recommendation. I don't include the CREATE statement in my examples because I find it too easy to add an index without giving it the needed thought. Below are the additional two queries:
-- mssqltips.com
-- query 1
SELECT Column1,
Column5
FROM dbo.BigTable
WHERE Column4 = 10
ORDER BY Column5 ASC;
-- query 2
SELECT LEN(Column5)
FROM dbo.BigTable
WHERE Column4 = 10
AND 1 =
(
SELECT 1
);
Like the other DMVs, the results vanish when SQL Server restarts. If a DBA restarts your servers monthly, consider creating a job that saves the results into a table. To handle this, I built a Power BI report with the main dataset that refreshes before the server restarts, capturing the results.
Summary
The missing index recommendations are not perfect. Microsoft has a list of limitations. People often don't realize that the key column recommendations are ordered based on how the columns were created. However, this new DMV cuts down on where I look to find all the facts about missing indexes.
If you're not using this new DMV yet, I encourage you to try it. Some people prefer to use Query Store to gather similar results, and if that works for you, keep using it. Like most performance-tuning tools, the new DMV shouldn't be the only one you use, but it's a helpful addition to your performance-tuning toolbox.
Key Points
- The new DMV sys.dm_db_missing_index_group_stats_query is only available starting with SQL Server 2019. The view works on Azure flavors of SQL, including Azure SQL Database and a Managed Instance.
- Several limitations exist for the missing index recommendations. One of the most frustrating is the need to open the execution plan and review the XML to see if there are multiple recommendations.
- Keep in mind that the optimizer will not provide a missing index recommendation for a trivial plan. Depending on how simple your queries are and the cost threshold for parallelism, they may never show up.
Next Steps
- I mentioned Glenn Berry's scripts earlier in this article. If you haven't checked them out yet, I highly recommend it. There's one for everything you could ever need related to SQL Server, and Glenn updates them with each new release.
- Are you interested in creating a report in Power BI to capture the view results but don't know where to start? If so, look at the article "Getting Started with Power BI - Part 1" by Eduardo Pivaral.
- Erik Darling created a version of this script focusing on queries with high CPU in the article "How SQL Server 2019 Helps You Find Queries That Have Missing Index Requests."
About the author
Jared Westover is a passionate technology specialist at Crowe, helping to build data solutions. For the past two decades, he has focused on SQL Server, Azure, Power BI, and Oracle. Besides writing for MSSQLTips.com, he has published 12 Pluralsight courses about SQL Server and Reporting Services. Jared enjoys reading and spending time with his wife and three sons when he's not trying to make queries go faster.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2024-09-11
