Being in Palo Alto, I've sat in on and overheard a lot of clearly valuable, constructive advisor meetings. I've also sat in on and overheard a lot of advisor meetings that were cringeworthy and made me want to run over and slap either the entrepreneur or the advisor for wasting each others' time. You need to be really selective when choosing who you'd like as an advisor - this person is going to take percentage of your company, and be with you the whole time you work on your company. It's an important decision!
Welcome to an up-and-down, long-term relationship
You're entering into a relationship with the person that could last for years, so get to know them first. Spend some time with a potential advisor - have a few meetings, and talk to other people that have had this person as an advisor. Great advisors will want to do this too, and won't be put off by a couple non-committal meetings to suss each other out. One lesson I learned - while someone might seem like they'd be a good fit as an advisor, meeting them in-person can be very different… you might not actually like them!
You want someone who has done something similar to what you're working on. If the advisor is too far away from your niche (i.e. they're consumer, you're ecommerce) the advice and value that you'll be able to get from that person will either be:
A) Too specific - "why does this happen when I push this button?", or
B) Too high-level - "focus on growth."
You need someone who can help you with the in-between of those extremes. It will be more valuable to you, but also means that the advisor will actually be an expert in the information that they're sharing with you, so they'll be happier too - they'll feel like they're actually adding value and moving the needle.
Not too early, not too late
There are a lot of really great, smart people who have helped companies do amazing things at any & all stages. Make sure any potential advisor is someone who has been through the stage that you're currently going through. For example - If you're still in closed beta, it probably doesn't make sense to get an advisor from BigTechCo who joined them once they had 100M users, and has been with that company since then. The advice that's most valuable for any startup is very often stage-specific. Also, it's often best to find someone who was in your current stage not too long ago, I'd say max 3 or 4 years ago. Tech and startups change so quickly, so the more current the person is, the more valuable the pertinent their advice will be. If they're from a previous generation, the advice they'll offer can still be immensely valuable - but may require more levels of abstraction.
Will call out your BS
This is important, but hard to judge early on. When you're off wandering in the desert, when you're in the trough of sorrow, when you're fighting with your co-founder and want to quit - you need your advisor to be able to tell you to keep going (or not!), and tell you how they pulled through a similar situation, how they pivoted, how they packed up and moved on. And vice versa - when you think everything is rosy, but you're tracking the wrong metrics, you have fake traction, or your product simply isn't working - you need your advisor to call you out on that too. This starts to get into more mentor territory* - but an advisor focuses on the company and product, versus you as a person. This is one of the big reasons why it's important to meet with the person a few times before officially making them your advisor, to get to know them and their style, and to make sure that they're not overly formal/nice/non-confrontational.
Clarity on expectations
It's really important that you choose your advisor for actual, specific reasons. You need to enter into the relationship with a purpose, vet potential people, and then choose who you want to work with based on that. A good way to go about this would be to think about "What are going to be my biggest risks?" and then trying to find people who you think have either encountered those same risks (and dealt with them successfully, or at least learned a lot from their unsuccessful experience), or have shown that they have a lot of knowledge around that area (through their writing, speaking events, etc.). You also need to be clear with each other on what those areas are, but also what's not expected (e.g. VC intros, product evangelism). Don't assume that because you think someone would be a great advisor on a particular area that they'll then be willing and excited to introduce you to potential investors.
Remember that choosing an advisor an important decision, and don't jump too quickly to sign someone up officially. Also be sure to think about it from the advisor's perspective, and make sure that they'll feel that they're adding value, and are able to really open up to you. It's not an easy relationship to get out of once you've signed the papers, so make sure it's one that makes sense for you and your startup - now and in the coming years.
* A mentor is more of a coach than an advisor. They're with you beyond 1 single company, and focus on you verus the company. Also, mentors are not paid or given equity, and you meet with them less frequently. It's more common for mentors to be successful beyond your specific vertical/product type, and also more common for them to be from another generation (as a person, as well as in terms of products).