July 5, 2022 at 8:20 pm
Hello,
hope someone can help, maybe a simple question
so i am using the Blitzindex from Brent Ozar, and its great, but one question, with some of the findings being "Missing Index" or "Index phobia", it does say estimated impact or missing index request... I am looking for a way to find out "This would benefit this store procedure OR TSQL statement".. but I dont see that anywhere on the results.. any idea how i can display that? there is many many tsql statements and store procedures to go through, and it would be great if it displays that info on the results... any ideas if it does or not?
July 5, 2022 at 11:17 pm
I strongly suspect that the specific proc nor statement is available, since the "standard" dm_* views don't include that data.
If you could post the proc itself, I will look and verify that. The blitz procs have been around a long time, so I can't imagine they're using any newer method that would show the sql, but it can't hurt to check the proc code and verify that.
I don't use blitz myself (I checked it out, no great value to me, and too many annoying warnings on things I was doing right!), so I don't have the code, and you have to provide email, etc., to get the package from Brent's site, and I'm not interested in doing that again right now.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 6, 2022 at 6:02 am
All the blitz procedures are in GitHub Scott so you don’t need to give Brent your email anymore to get them.
https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit
July 6, 2022 at 7:58 am
The probable best way to achieve this is to run sp_blitzcache and store the results in a table periodically.
The cache will contain which statements wanted a missing index as long as that plan is still in cache but as the plans are a lot of XML shredding, I wouldn’t want to reinvent the wheel.
Storing blitzindex and blitzcache in separate tables would let you write your own master query to join the two result sets together to get the information your looking for.
July 6, 2022 at 12:28 pm
Generally speaking, the missing index recommendations taken from the DMV tables are useless. That's because they are not, and cannot be, linked to a given query. So you just have an index recommendation, divorced from context. Context matters. Was that query that Tim, the junior DBA, ran by mistake on the wrong server with the wrong variables? Or was it a vital query called millions of times a minute by the most important app in the business? Without context, it's impossible to know whether or not the suggested missing index is likely to be useful.
If you really want to use missing index information, the better place to get it is from the execution plans. You can query the cache to retrieve it. You can query Query Store to retrieve it. Both these are somewhat resource intensive queries (XML, not always our friend), so be cautious in a production environment. This allows you to match the suggestion to a query, and further, to performance metrics, so you can better make a decision.
Even with these in hand, remember that these missing indexes are suggestions. They are not necessarily good choices. You still must test them to validate they do in fact help performance.
"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
July 6, 2022 at 5:52 pm
Generally speaking, the missing index recommendations taken from the DMV tables are useless. That's because they are not, and cannot be, linked to a given query. So you just have an index recommendation, divorced from context.
Very interesting. I rely on the dm views for the overwhelming majority of my tuning. For example, we have hundreds and hundreds of dbs and thousands and thousands of queries. And developers in different parts of the globe whose code I don't control. I don't have time to look at query plans for all of them. Moreover, a single query plan only tells me about that specific run of the query, not how it might run with other values specified in the WHERE clause.
I do often check the TOP (nn) worst queries -- particularly in the initial attempts at tuning a given instance -- and will look at those specifically if/as needed. But otherwise, to me the best overview of all activity is the dm view data.
Since the dm views are cleared on restart, as Grant noted, it's best if the instance has been up for at least 7 days, and of course longer than that is even better. I'd be leery of using dm data from only a day or two of uptime (unless the stats were very clear that a certain index was needed).
Now, yes, you do have to carefully analyze the dm data. And you certainly do not want to blindly create all (or often even most) of the indexes that are returned by the missing_indexes stats. It's an involved process that you improve at with experience. Can I give you any easy rules to follow? Not really.
There is one rule, however. For initial tuning, focus on getting the best clustered index on every table. The clus index is the single most important factor in overall table performance. After that you can work on the nonclus index(es).
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 6, 2022 at 7:20 pm
On the missing index views? They just don't say anything. I'm not talking about all the other DMVs. They're vital. 1 million percent so. But the missing index DMVs? Useless because of the lack of context. That's all.
"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
July 6, 2022 at 7:26 pm
On the missing index views? They just don't say anything. I'm not talking about all the other DMVs. They're vital. 1 million percent so. But the missing index DMVs? Useless because of the lack of context. That's all.
How is it useless? When it tells me that 500,000 lookups were done on a particular column, that's vital. Much more so than knowing the query plan for a single, specific query when there are hundreds of thousands of queries running in the db every day.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 6, 2022 at 8:49 pm
Grant Fritchey wrote:On the missing index views? They just don't say anything. I'm not talking about all the other DMVs. They're vital. 1 million percent so. But the missing index DMVs? Useless because of the lack of context. That's all.
How is it useless? When it tells me that 500,000 lookups were done on a particular column, that's vital. Much more so that knowing the query plan for a single, specific query when there are hundreds of thousands of queries running in the db every day.
What if it's just a silly person doing a dumb and it'll never happen again? I just don't make decisions without context. Far too easy to be wrong.
"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
July 6, 2022 at 10:03 pm
ScottPletcher wrote:Grant Fritchey wrote:On the missing index views? They just don't say anything. I'm not talking about all the other DMVs. They're vital. 1 million percent so. But the missing index DMVs? Useless because of the lack of context. That's all.
How is it useless? When it tells me that 500,000 lookups were done on a particular column, that's vital. Much more so that knowing the query plan for a single, specific query when there are hundreds of thousands of queries running in the db every day.
What if it's just a silly person doing a dumb and it'll never happen again? I just don't make decisions without context. Far too easy to be wrong.
To me that makes it literally impossible to manage a site with more than a few users and a few dbs. How on earth would you ever find time to review every query plan by hand before changing any index? I just can't imagine that. I've tuned up dbs for many clients without ever looking at a single query plan.
What if a "silly person" wrote the query you decided to look at the query plan on?
If "silly" people are writing such queries -- and no one in IT can tell you to ignore them -- then you may need to factor them into your index adjustments anyway. It's quite a big stretch to just assume that certain stats will be a one-off from a "silly" person just because you don't see the query.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 7, 2022 at 2:51 am
If "silly" people are writing such queries -- and no one in IT can tell you to ignore them ...
My problem is that those two groups are frequently the same group. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
July 7, 2022 at 12:36 pm
Grant Fritchey wrote:ScottPletcher wrote:Grant Fritchey wrote:On the missing index views? They just don't say anything. I'm not talking about all the other DMVs. They're vital. 1 million percent so. But the missing index DMVs? Useless because of the lack of context. That's all.
How is it useless? When it tells me that 500,000 lookups were done on a particular column, that's vital. Much more so that knowing the query plan for a single, specific query when there are hundreds of thousands of queries running in the db every day.
What if it's just a silly person doing a dumb and it'll never happen again? I just don't make decisions without context. Far too easy to be wrong.
To me that makes it literally impossible to manage a site with more than a few users and a few dbs. How on earth would you ever find time to review every query plan by hand before changing any index? I just can't imagine that. I've tuned up dbs for many clients without ever looking at a single query plan.
What if a "silly person" wrote the query you decided to look at the query plan on?
If "silly" people are writing such queries -- and no one in IT can tell you to ignore them -- then you may need to factor them into your index adjustments anyway. Even just assuming it's a one-off -- from a "silly" person -- is a very huge stretch in and of itself.
<sigh>
Not even remotely what I've been suggesting. Here you go Scott. You're right. 100% of the time. Enjoy. Bye.
"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
July 12, 2022 at 5:50 pm
The missing index suggestions are not connected to specific queries in versions prior to SQL Server 2019. SQL Server 2019 added the sys.dm_db_missing_index_group_stats_query DMV. This DMV tells you which queries contributed to missing index suggestions.
As mentioned by Grant, you can also connect missing index suggestions to specific queries by finding the suggestions in the plan cache. There's an easy way to do it, with the IndexNeedsPlus tool in the free SQLFacts toolkit. The toolkit was discussed in a series of articles here on SQLServerCentral. The most relevant article for the topic at hand is here.
Creator of SQLFacts, a free suite of tools for SQL Server database professionals.
July 12, 2022 at 7:07 pm
The missing index suggestions are not connected to specific queries in versions prior to SQL Server 2019. SQL Server 2019 added the sys.dm_db_missing_index_group_stats_query DMV. This DMV tells you which queries contributed to missing index suggestions.
As mentioned by Grant, you can also connect missing index suggestions to specific queries by finding the suggestions in the plan cache. There's an easy way to do it, with the IndexNeedsPlus tool in the free SQLFacts toolkit. The toolkit was discussed in a series of articles here on SQLServerCentral. The most relevant article for the topic at hand is here.
I will say, again, that most often this is wasted effort. You typically don't really need to know the underlying queries. Aside from the "TOP (nn)" bad I/O queries to find any truly awful queries, you don't generally need to go down to the query level.
Moreover, if as a DBA you're responsible for working across: hundreds of dbs; in half-a-dozen or so instances; with dozen(s)+ of developers; you won't have time to look at each and every query and its plan(s) anyway.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 12, 2022 at 7:42 pm
Wingenious wrote:The missing index suggestions are not connected to specific queries in versions prior to SQL Server 2019. SQL Server 2019 added the sys.dm_db_missing_index_group_stats_query DMV. This DMV tells you which queries contributed to missing index suggestions.
As mentioned by Grant, you can also connect missing index suggestions to specific queries by finding the suggestions in the plan cache. There's an easy way to do it, with the IndexNeedsPlus tool in the free SQLFacts toolkit. The toolkit was discussed in a series of articles here on SQLServerCentral. The most relevant article for the topic at hand is here.
I will say, again, that most often is wasted effort. You typically don't really need to know the underlying queries. Aside from the "TOP (nn)" bad I/O queries to find any truly awful queries, you don't generally need to go down to the query level.
Moreover, if as a DBA you're responsible for working across: hundreds of dbs; in half-a-dozen or so instances; with dozen(s)+ of developers; you won't have time to look at each and every query and its plan(s) anyway.
I did not say the query information was absolutely essential. I simply said it's available in SQL Server 2019 (or using SQLFacts), which previous posts did not mention.
I do not find the missing index suggestions to be useless, but nor do I think connecting them to specific queries is wasted effort. It's a great way to verify/quantify the impact of a new (or modified) index.
Creator of SQLFacts, a free suite of tools for SQL Server database professionals.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply