I'm not talking about the type that is done at the airport, but more the type that lets you see how long or how many times each section of code is called. There is most likely a wide variety of tools for profiling your code in a variety of languages. I'm going to focus on Java in this post using a tool called YourKit, but I assume other tools will give the same basic info.
From time to time, especially when you're having performance problems, it's a good idea to run your code through a profiler and gain a readout of how long methods take to run and how many times they are called. A good profiler will break it down into a nice tree view that shows which methods took the longest and which methods inside of those methods took a long time based on the number of invocations. Looking at these numbers you can sometimes find places where your code has a nested loop that isn't required or is calling an expensive method or function that returns the exact same data multiple times.
A developer is trained to trace code in his/her head while writing it, however sometimes you write a method that uses the functions of an already written piece of code. If you don't know how efficient that code is, you may place it in a loop without realizing the performance penalty. When testing it locally it might seem to run fine because you're a single user with very low load. A method that called the DB 10k times might return in seconds and seem to display fine for you, but under load it can slow down in production.
While a load test suite can find that a problem exists in the code, profiling can narrow it down to what's really causing it. You can profile your code without a commercial or open source profiler by manually adding timestamp information before and after method calls. This isn't very usable to do for every single call, but if you have an idea of what areas are causing a slow down and you want to narrow it down it can be very helpful.
Recently I ran a profiler on a section of a project I'm working on. I was able to find that to load a fairly simple page that there was a database query being called 22k times. That DB query was pulling in information related to an item I actually used, but was never used itself. While those queries return fairly quickly normally, having multiple users hit the page at the same time will multiply the number of queries. Also, if the latency between the server and the DB increases, we will definitely see a decrease in the responsiveness of the application. If we can eliminate these unneeded DB calls, we can speed up the application for the end users and also increase scalability.
Just because you don't see an issue when you're running your quick visual test doesn't mean that the issue doesn't exist. Performance is not something that should be tacked on at the last minute, and code needs to be analyzed regularly to see where inefficiencies are introduced.