It seems clear that DNA matches have made it possible for some people to solve some of their family history mysteries, but that hasn't been the case for me.
I have an extensive, well-researched tree and I've gotten some really tantalizing DNA matches but I've been unable to figure out the exact connections because the required paper records are missing (this is especially important for family connections that pre-date the census).
I paid for me and one of my siblings to be tested. Has the cost of the DNA testing been worth it?
On the positive side, these matches have at least proven that there is a connection between certain families with the same surname in the same small towns but, on the negative side, I've spent many additional hours tracing as many of them as I possibly can and am no closer to finding answers than I was 5, 10 or 15 years ago. This has been frustrating and probably not worth the cost of the DNA, unless it pans out at some point in the future. But you can't know unless you try to do the research and follow those trails. In my case, the records either don't exist or they aren't available online yet, but in someone else's case, they just might, and wouldn't that be amazing?
I had also hoped to figure out who my great-grandfather's father was... hahahahahahahaaaaaa. Yeah, that's not going to happen. It's like trying to prove a negative. There are thousands of names out there but absolutely no way for me to build working trees for each one of them, just so I can rule them out. It was impossible enough with the known surnames. If I had done the DNA with just that goal in mind, no, it wouldn't have been worth it.
However, my main motivation in taking the DNA test was to provide a way for any potential half-siblings to contact me. I haven't heard from anyone yet, and maybe I never will, but knowing I've made it possible for someone to reach out has given me some peace of mind and, for that reason, I'd say the DNA test was worth it.
As others have said, it all depends on why someone decided to spend the money on the DNA test in the first place. It's not cheap, it definitely isn't always easy, and it isn't guaranteed to solve those mysteries but, if someone can afford it, and if they're lucky in terms of the right records being extant, and if they're a good enough researcher, it's probably worth it.
I enjoy doing the research, so the process itself isn't too onerous for me, but it does feel frustrating when there's no big pay-off at the end.