Hey there - have worked in a few different forms of Agile, and am currently a software engineering manager helping my company along the path to something utilizing Agile practices.
With my professional hat on - it's got some pretty good perks when compared to the waterfall-style SDLC type approach. When teams are organized well, with strong developers, project managers, and clear product vision involved, Agile can be a ridiculously efficient beast for churning out products, especially when it comes to iterative design principles and incorporating user feedback directly into the cycle.
With my professional hat off - there's some pretty big caveats to the Agile approach; namely, getting the higher-ups that see the term "Agile" and decide they want the new shiny implemented in their company. For one, there is no singular Agile approach for any organization, or team, for that matter. The strengths with Agile come from a pretty strong sense of self-ownership, self-organization, and team cohesiveness. The process only works when everyone is bought in, but even so you have to find what works for your organization. I've seen a lot of organizations go the approach of "we have to use Agile for everything", including daily standups, full retrospectives, etc. etc. That's not necessarily a bad approach, but if an organization has never used Agile before, it can be absolutely demoralizing to (especially senior) developers, and add a ton of overhead they're not used to. If teams have some level of self-organization on how they want to use Agile, there's generally better acceptance across teams for it. On top of the organizational and psychological effects of Agile overall, there's also the tooling aspect - frankly, most of the tooling sucks. Atlassian makes cool tools, but I'd bet a beer any day that one of the biggest drawbacks and complaints you'll hear from teams is "Jira absolutely sucks, I hate it and everything about it".
TL;DR: Agile is... fine, for the most part - as long as expectations are set within an organization on what it is, how it'll be utilized, and some autonomy for teams to organize around what works for them. If not everyone is bought in, it's gonna be like pulling teeth. Productivity-wise, it makes sense for a lot of web development, SaaS providers, those types of things, but it can be trickier for embedded devices, upgrading legacy applications, etc.
If you have more targeted, specific questions or anything please feel free to ping me.