 Have you ever gotten stuck trying to figure out where your database related performance issues are coming from? Look no further than the database tool in Visual Studio, and find out more on this episode of VS Toolbox. Hey, everyone. Welcome to Visual Studio Toolbox. I'm your host, Leslie Richardson, and today I'm joined once again by Esteban Herrera as part of our larger series on performance profiling, and today we'll be talking about the database tool. How are you doing, Esteban? I'm doing great. I'm happy to be here. Oh, long time no see. Yeah. The database tool, has that been around for a while, or is that one of the newer profiling tools? It's relatively new. At this point, it's been around for several months, if not a year, but we're still actively doing work on it to make it even better. Awesome. I'm excited to show it off a little bit today. Yeah, I'm excited to see it. What exactly is it? It's another one of those tools that we see when you enter the performance profiling menu, which again, you can reach that by hitting Alt-F2, or going to debug in the performance profiler. What it does is it captures more data about your program as it runs. In this case, it captures database activity, queries, transactions, stuff like that. That's great. When you run it alongside the CPU usage tool, I can be a powerful way of narrowing down pieces of your code that might be generating a lot of work for your database, or might be waiting on a database, and hopefully you can tweak that to make your application a little bit faster. That's excellent. What kinds of databases is this tool applicable to? Like SQL, Entity Framework? Yeah, Entity Framework. Anything that falls under the ADO.NET family. In this case, I believe I'm using my SQL database with Entity Framework, but any Entity Framework or ADO.NET database should work with this tool. Cool. What kind of query are we going to be profiling today? Today, I have a pretty simple demo for you. This application is one I wrote a while back, that it keeps track of a movie collection, and you can add movies, edit movies, et cetera. I'm just going to launch this application. If I click on this link, we'll see a list of movies. I think it was actually for a video store because there's some cheap prices, but I can- Wow, those are some cheap prices. I'm paying like $4 to rent things on Apple Store these days. Yeah, maybe it's for blockbuster days. Yeah. Oh, good times. I can delete a movie. Delete a die-hard? Why would you do that? I already have three copies. It's just for me on the list. Okay. Once I've driven my application a little bit and created some of the activity that I might care about, I can go back to Visual Studio and stop collection, and it's going to massage all that data that we collected into this view. From the CPU summary page that we see, because we ran the CPU usage tool alongside it, you'll see the same things that you would expect. There's categories graph, there's top functions and hot path, but you'll also see these graphs at the top. Again, these graphs can help you filter whatever tools you ran to just that time span. If there was a ton of CPU activity or a ton of database queries during a certain time, in order to narrow down that data and not be overwhelmed, if it's a ton of data, you might want to filter there. Makes sense. But the database tool, what it really gives you is this table that you see here under these queries graph. And it tells you a bunch of really useful things. It tells you the actual query that happened. So select from where the whole query is there. It tells you what time it started and how long it lasted. These unknowns, in this case, are there because the query didn't end in time. I stopped collecting before it ended so we don't know how long the query lasted. But if I had let it run a little bit longer, all of these would have a duration as well. This is the number of records that were affected or returned. So if it's a select, I might get two returns or if I deleted a movie, it might be one record affected song and so forth. You have this database column, which if your application is calling several databases, maybe one is holding movie posters or covers. That might be useful as well. The actual connection string that's used to connect to that database. And these can all be sorted. And so between the graph, this table of queries, how long each one lasted and even what database happened in, you should be able to identify a query that might be worth taking a closer look at. And when you right click on it and click go to source file, we'll try to take you to where in your code that query actually happened. And it looks like in this case, that query had to do with actually seeding the database at startup. Makes sense. That's a long process. It can be. Yeah, it can be. But that's a really powerful thing that I don't wanna understate. The fact that you can go from one of your queries to your source and see what part of your code was responsible for that query, makes it really easy to go exactly where you need to to hopefully make some changes that can make your application more performant. That's great. So let's say that you might have written the query in the database in a way that makes it have a heavier performance by default and you wanna like make it better. Can you, from the database tool window, can you get redirected to the database itself? Is that possible? Not directly to the database itself. Most of the changes that we can make from within Visual Studio have to do with the way that our client interacts with the database. But it can still point you to maybe a table that needs to be re-indexed or a transaction that can be tweaked a little bit that can be taken care of on whatever database management tool you're using. So it can't take you directly to changes on your database, but it can point you to a specific transaction that performed differently than you expected and can hopefully take you to a possible change faster than if you were just guessing or going down a list of everything that happened during that time. That's great. So are there any other roadblocks that people should be aware of when they use this tool? Other than being aware that if you get an unknown in one of these columns, it's because the query didn't complete in time. It's a pretty straightforward tool. I mean, like I mentioned a few times now, this performance investigations are usually about just getting to the place in your code where you can make a change as quickly as possible. And so this tool is purposely, tries to give you as much useful information as possible and then make it really easy to go to your source and be able to make a change there. A lot of the power that comes with this tool is that you can run it alongside the CPU usage tool or several other tools. And so another thing that might be interesting to look at is if you make a database call and especially if you're using the energy framework or link, you might be allocating objects for all your returned records and your memory consumption might be different than what you would expect. And this tool alongside a memory tool would hopefully surface that issue. I agree, super nifty. So since this is a new tool and you directly worked on this one, right? Yes, I did. I talked to dozens and dozens of developers and asked about what kind of issues they have with doing investigations with their applications The two things that came up often is if something's performing slowly and there's a database involved, they almost always immediately look at the database. So we knew this was a useful place to have a tool. And yeah, and the other thing they said is just that there's so much data when you collect a trace that it's all about being able to get to your code as quickly as possible. So we tried to make those. We really made those two things the focus of this tool. Great, you answered my own question. I was just gonna ask what was the hardest part about putting it together and stuff. So yeah, folks, just know that we do listen to your concerns. Yeah, and I would love to hear more feedback about how to make this tool even more useful. So my contact information will be in the description. Please go ahead and try the tool if you run into any issues, just send me an email or tweet me or anything and I'll get in touch. Awesome. And to learn more about the database tool specifically, is there a resource for that or like a doc? Yeah, there is a doc in the Visual Studio docs. We'll make sure that's linked as well. We actually recently made some updates to that doc so it should be very complete. And between that and just trying it and reaching out, we will hopefully be able to help you diagnose any database issues that your application might have. Sounds great. Well, hopefully the next time people are stuck with their database related performance issues, it won't be a problem anymore with the new database tool. So thanks for being here Esteban. Yeah, it was a quick one today but I'm very proud of this tool and I hope it's useful to everyone. Yep, short, sweet and to the point. And yeah, until next time, happy profiling. Happy profiling.