Performance Profiling | Database Tool

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. [MUSIC] >> Hey, everyone. Welcome to Visual Studio Toolbox. I’m your host, Leslie Richardson. Today, I’m joined once again by Esteban Herrera as part of our larger series on performance profiling. Today, we’ll be talking about the database tool. How are you doing, Esteban? >> I’m doing great. Happy to be here. >> Longtime no see. >> Yeah. >> So the database tool, has that been around for a while, or is that one of the newer profiling tools? >> It’s relatively new.

00:46 - 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 computing Alt F2, we’re going to debug in the performance profiler. What it does, is it captures more data about your program as it runs.

01:23 - In this case, it captures database activity, queries, transactions stuff like that. >> That’s great. >> When you run it alongside the CPU usage tool, it 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 it to do database, and hopefully, you can tweak them to make your application a little bit faster. >> That’s excellent. What kind of databases is this tool applicable to? SQL, Entity Framework? >> Yeah, Entity Framework, anything that falls under the ADO.NET family. In this case, I believe I’m using MySQL database with Entity Framework, but any Entity Framework, or ADO.NET database should work with this tool. >> Cool. What query are we are going to be profiling today? >> Today, I have a pretty simple demo for you.

02:29 - This application is one I wrote a while back. It keeps track of a movie collection, and you can add movies, edit movies, etc. 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 start, because there’s price lists. >> Wow. Those are some cheap prices. I’m paying four dollars to rent things on Apple Store these days. >> Maybe you should block those for days. >> Yeah. Good times. >> I can delete a movie. >> You deleted Die Hard? Why would you do that? >> I already have three copies. It’s just further down 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 start collection. 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’s tool alongside it, you’ll see the same things that you would expect.

03:41 - There’s categories graph, there’s top functions and hot paths, 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. It tells you a bunch of really useful things.

04:21 - 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. If it’s a select, I might get two returns, or if I deleted a movie, it might be one record affected, and so on 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. Between the graph, this table of queries, how long each one lasted, and even what database it happened in, you should be able to identify a query that might be worth taking a closer look at.

05:57 - 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. 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 want to understate. The fact then you can go from one year queries to your source and see what what part of your code was responsible for that query makes it really easy to go exactly where you need to hopefully make some changes that can make your application more performant. >> That’s great. 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 want to make it better.

06:55 - 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. 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. 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. 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.

08:36 - This tool is purposely trusted 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. Another thing that might be interesting to look at is, if you make a database call, and especially if you’re using Entity 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. This tool alongside a memory tool would hopefully surface addition. >> I agree. Super nifty. 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 09:48 - and asked about what issues they have with doing investigations with their applications. The two things that came up often is, if something is 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. The other thing we 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. >> That can be overwhelming. >> We really made those two things the focus of this tool. >> Great. You answered my own question. I was just going to ask, was this the hardest part? But putting it together and stuff, so yeah.

10:37 - Folks, just know that we do listen to your concerns. >> I would love to hear more feedback about how to make this tool even more useful. My contact information will be in the description. Please go ahead and try the tool and if you run into any issues, just send me an e-mail, or tweet me, or anything, and I’ll get in touch. >> Awesome. To learn more about the database tools specifically, is there a resource for that, like a doc? >> Yeah. There is a doc in the Visual Studio docs. We’ll make sure that’s linked as well.

11:16 - We actually recently made some updates to that doc, so it should be very complete. 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. 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. >> Short, sweet, and to the point. Until next time, happy profiling. >> Happy profiling. [MUSIC] .