May 26, 2015 at 2:50 pm
Hi,
SELECT TOP 20
ROUND(s.avg_total_user_cost *
s.avg_user_impact
* (s.user_seeks + s.user_scans),0)
AS [Total Cost]
,d.[statement] AS [Table Name]
,equality_columns
,inequality_columns
,included_columns
FROM sys.dm_db_missing_index_groups g
INNER JOIN sys.dm_db_missing_index_group_stats s
ON s.group_handle = g.index_group_handle
INNER JOIN sys.dm_db_missing_index_details d
ON d.index_handle = g.index_handle
ORDER BY [Total Cost] DESC
when we use the query it will gives us missing index details but how to find out what are queries that are missing those indexes?
May 26, 2015 at 3:08 pm
ramana3327 (5/26/2015)
Hi,SELECT TOP 20
ROUND(s.avg_total_user_cost *
s.avg_user_impact
* (s.user_seeks + s.user_scans),0)
AS [Total Cost]
,d.[statement] AS [Table Name]
,equality_columns
,inequality_columns
,included_columns
FROM sys.dm_db_missing_index_groups g
INNER JOIN sys.dm_db_missing_index_group_stats s
ON s.group_handle = g.index_group_handle
INNER JOIN sys.dm_db_missing_index_details d
ON d.index_handle = g.index_handle
ORDER BY [Total Cost] DESC
when we use the query it will gives us missing index details but how to find out what are queries that are missing those indexes?
Those "Missing index details" are applicable to the query at hand. and the specific index it would create would be applicable to that specific query, and not necessarily to any other. A DBA is generally the "go to" person for this kind of analysis, where you need to ensure proper indexing is in place. However, even if you could look at the estimated execution plan for every query to run in an entire database, you wouldn't necessarily have a good solution in hand, as indexes have a cost at insert time, so the workload has to be known and quantified to some degree, in addition to understanding the queries that will run against the database and the frequency.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
May 26, 2015 at 3:47 pm
To clarify that a bit, I believe what Steve was referring to is the missing index information displayed when viewing a query plan. That is certainly applicable to the query at-hand, and not necessarily any other.
However, those DMVs are updated each time the a query is optimized by the optimizer, except for missing_index_group_stats, which is updated by every execution.
It is possible to tie the information from those DMVs with the actual queries using the query plan information from the plan cache, but it might be quite slow if you have a lot of plans in the cache.
Jonathan Kehayias has a quick bit about that here: https://www.sqlskills.com/blogs/jonathan/digging-into-the-sql-plan-cache-finding-missing-indexes/.
Now, having said that, as Steve pointed out, you have to be careful with what you do with this information, since the gains from indexing don't come for free, and the cost to writes, storage, and memory (all those new index pages need to be stored somewhere, and take up space in the buffer pool when read) can easily outweigh the gain to reads from the queries that would utilize them.
With that caveat out of the way, the direct answer to your question would be to join the results from a query like Jonathan's to those DMVs.
I hope this helps!
May 27, 2015 at 6:22 am
There's no way to correlate what's stored in those DMVs to any query. So, instead, you need to query directly against the XML of the execution plans in order to correlate the suggested indexes to a query. I have a blog post here [/url]that shows. Be cautious though. These are suggestions. They're frequently wrong. They're sometimes bad. Very carefully evaluate if you the index is indeed helpful and if you don't already have another index that might work just as well.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply