I don't think this is necessarily a good fit for the SE Q&A model, but I sympathize with datadawg2000's pain so will add some words that may help.
...claiming poor performance on the main server due to too many processes running at once. When I open activity monitor at any given time, I see no more than 5% processor time, 1 mb/sec, etc.
Let's be honest - that isn't the right away to get a snapshot of current server load nor is a screenshot of activity monitor going to be convincing to anyone. And besides, you really shouldn't be relying on Activity Monitor for...anything, anyways.
You didn't specify your SQL Server version, but at your disposal to see performance you have SSMS performance reports, perfmon, any number of third party applications, open source tools like Brent's First Responder Kit and sp_WhoIsActive, and a ton more with a little Googling (or Binging if you really love Microsoft).
Those can help you see if you really have a problem, and where it is. Being able to proactively come to management when there is a problem and/or retroactively assess the root cause will be a huge start.
I have worked on performance issues with them before but it always
comes down to code errors, inefficient queries, or missing indexes
(for example there is one index that according to the missing index
dmv has an avg_estimated_impact of 33 minutes).
Great! So you have a proven track record of fixing issues. Use that to make sure that you or someone who knows what they're doing is reviewing code, queries, etc. before they get pushed to production. 99% of the time these issues can be stamped out in a lower environment or through proper testing.
I'm fighting it because it will cost them a lot of money in our org and also I don't believe it will help them with their performance issues that much/at all.
I don't know your setup, but this will play out in one of two ways assuming you're correct:
- They add a new reporting instance, nothing gets better. You can say "I told you so" and proceed to get them to fix their bad code / indexing / testing process.
- They listen to you and fix their bad code / indexing / testing process.
Either way, you can win here. Unless the cost of the new server is coming out of your paycheck, make your advisement and then relax. You probably don't have the power to make the final decision, so don't lose sleep over it.
Does anyone have any nice queries or methods they can share to help
prove server performance is fine in terms of computing resources? I
can screenshot activity monitor but I'd like to provide management
with something more concrete (perhaps over 24 hours) that it is not a
lack of computing resources that is part of the perceived slowness.
See the list of tools above for this. Painting a long term picture is key here, which third party tools and/or pretty graphs will probably help with. Remember that no one cares if you have good performance for 23 hours out of the day if the Big Boss's report runs slow during the literal 11th hour. Clearly management has valid concerns things are not fine and you've said the same more or less - so don't fight them on that point.
You are disagreeing on what the root cause of the problems are and that is where you need to focus your efforts. Spend some time to agree on metrics that both you and management find to be important and start reporting on those. You can highlight you're meeting RTO and RPO SLAs, have good CPU usage, etc. but also remember they probably care about their reporting SLAs just as much (lets be honest, way more) than the boring technical details. Then, as long as you can show positive trends in making those metrics healthier, it should be easy to get them on your side of reasoning.
Let me also add that I have these and more tools/resources compiled on my site here if you'd like to explore more free and community driven software/projects.