WOOOOHOOOOOO
Thanks for watching!
Video Summary
In this video, I share an exciting new feature in SQL Server 2019 CTP 2.4 that makes tracking down missing index requests much easier. Typically, identifying the query responsible for a missing index has been a bit of a challenge, involving either sifting through plan cache entries or running complex DMV queries. However, with these recent updates, you can now directly access the query hash, plan hash, and last SQL handle associated with missing index requests in the `sys.dm_db_missing_index_group_stats` DMV. This allows for seamless joining to `sys.dm_exec_query_stats` and `sys.dm_exec_query_plan`, providing a straightforward way to retrieve the exact query that requested the missing index. While it’s worth noting that if the execution plan is no longer cached, this information won’t be available, I find this feature incredibly useful and believe it will greatly enhance troubleshooting and optimization efforts in SQL Server environments.
Full Transcript
Howdy folks, Erik Darling here with Erik Darling Data, being mildly distracted by a pen that I got. It has my company name on it. Eh, I know that I’m doing incredibly well as a limited liability corporation when I’ve started receiving personalized office supplies. Very good feeling. But today I’m here to talk about something really cool that I just noticed in SQL Server, 2019, CTP 2.4, which just dropped today like a few hours ago or a couple hours ago or some, some amounts of time ago that I can’t really recall because my brain doesn’t work anymore. But really neat and quick video just to show you what it is. Now, usually when you run a query and you know, whether you get the actual plan or whether you go into the plan cache or whether you, you know, go off and run crazy DMV queries, when you have missing indexes, it’s always been kind of tough to tie what query asked for that and that missing index and the missing index request over in the DMVs. For instance, I have this query here, which is asking for a nonclustered index on the post table that includes owner user ID. And I guess I suppose that’s an okay index request. It’s not the, maybe not the greatest missing index request in the world because, you know, owner user ID is, in the join and we might, might, might, might want to index the columns that we join on. But that’s not the point here. The point here is this. Now, rather than either having to by chance come across this plan in the plan cache or run a DMV query and say, huh, I wonder, I wonder what query query was I was asking for that. Well, now we can get that.
So, uh, in, uh, in the, uh, in the system, DM, DB missing index group stats query, uh, there are a couple new columns and I’m going to, I’ve already run this and I’m not going to run it again cause that would be ridiculous, but we have some new columns where, holy crow, that zoomed in big. Uh, but we get the query hash query plan hash and last SQL handle of, uh, the query that asked for the missing index. So now, when we, uh, query this DMV, we can join it off to sys.dm exec query stats and we can cross apply sys.dm exec query plan handle to find the query plan of the query that asked for the missing index. And I think that’s really, really spiffy. The only thing is, let’s see, let’s run this and let’s, let’s rerun this and let’s see, let’s see if this changes. Yeah. Now, now we get nothing back. So if, if you’re, if you’re, if the execution plan isn’t in the plan cache anymore, uh, it’s not going to tell us about that missing index.
Which is neither good nor bad, but it’s just a thing. Bummer, huh? Anyway, uh, that’s about all I had for this one. For now, I might come back to this as time allows. But anyway, uh, thank you for watching. Uh, I hope that you are excited, uh, or at least as, as, like, like the neighborhood of excited as I am about, uh, this new SQL Server version. And I hope that someday you too start getting personalized office supplies is a sign of your business’s ultimate success.
Anyway, uh, thanks for watching or whatever. I don’t know, listening, maybe just staring blankly at a screen pretending to work. It could be anything really. Anyway, thanks. See you next time. Goodbye. Button.
Video Summary
In this video, I share an exciting new feature in SQL Server 2019 CTP 2.4 that makes tracking down missing index requests much easier. Typically, identifying the query responsible for a missing index has been a bit of a challenge, involving either sifting through plan cache entries or running complex DMV queries. However, with these recent updates, you can now directly access the query hash, plan hash, and last SQL handle associated with missing index requests in the `sys.dm_db_missing_index_group_stats` DMV. This allows for seamless joining to `sys.dm_exec_query_stats` and `sys.dm_exec_query_plan`, providing a straightforward way to retrieve the exact query that requested the missing index. While it’s worth noting that if the execution plan is no longer cached, this information won’t be available, I find this feature incredibly useful and believe it will greatly enhance troubleshooting and optimization efforts in SQL Server environments.
Full Transcript
Howdy folks, Erik Darling here with Erik Darling Data, being mildly distracted by a pen that I got. It has my company name on it. Eh, I know that I’m doing incredibly well as a limited liability corporation when I’ve started receiving personalized office supplies. Very good feeling. But today I’m here to talk about something really cool that I just noticed in SQL Server, 2019, CTP 2.4, which just dropped today like a few hours ago or a couple hours ago or some, some amounts of time ago that I can’t really recall because my brain doesn’t work anymore. But really neat and quick video just to show you what it is. Now, usually when you run a query and you know, whether you get the actual plan or whether you go into the plan cache or whether you, you know, go off and run crazy DMV queries, when you have missing indexes, it’s always been kind of tough to tie what query asked for that and that missing index and the missing index request over in the DMVs. For instance, I have this query here, which is asking for a nonclustered index on the post table that includes owner user ID. And I guess I suppose that’s an okay index request. It’s not the, maybe not the greatest missing index request in the world because, you know, owner user ID is, in the join and we might, might, might, might want to index the columns that we join on. But that’s not the point here. The point here is this. Now, rather than either having to by chance come across this plan in the plan cache or run a DMV query and say, huh, I wonder, I wonder what query query was I was asking for that. Well, now we can get that.
So, uh, in, uh, in the, uh, in the system, DM, DB missing index group stats query, uh, there are a couple new columns and I’m going to, I’ve already run this and I’m not going to run it again cause that would be ridiculous, but we have some new columns where, holy crow, that zoomed in big. Uh, but we get the query hash query plan hash and last SQL handle of, uh, the query that asked for the missing index. So now, when we, uh, query this DMV, we can join it off to sys.dm exec query stats and we can cross apply sys.dm exec query plan handle to find the query plan of the query that asked for the missing index. And I think that’s really, really spiffy. The only thing is, let’s see, let’s run this and let’s, let’s rerun this and let’s see, let’s see if this changes. Yeah. Now, now we get nothing back. So if, if you’re, if you’re, if the execution plan isn’t in the plan cache anymore, uh, it’s not going to tell us about that missing index.
Which is neither good nor bad, but it’s just a thing. Bummer, huh? Anyway, uh, that’s about all I had for this one. For now, I might come back to this as time allows. But anyway, uh, thank you for watching. Uh, I hope that you are excited, uh, or at least as, as, like, like the neighborhood of excited as I am about, uh, this new SQL Server version. And I hope that someday you too start getting personalized office supplies is a sign of your business’s ultimate success.
Anyway, uh, thanks for watching or whatever. I don’t know, listening, maybe just staring blankly at a screen pretending to work. It could be anything really. Anyway, thanks. See you next time. Goodbye. Button.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.
It’s the little things that make us happy right? Having just spent 6 days doing a full indexing review of our EMR system… I can also say this is a feature I’m pretty excited about. Thanks for the post.