1

I support a transactional SQL Server as a dba and have recently been told by a management consultant that they need a new replication server set for reporting only, 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. 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).

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.

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.

  • Prove it to them--if you can, put this on a test server and run one of the slow queries, then add the covering indexes and show how much faster it is. It's crystal clear to a good DBA, but to someone who really doesn't know how a covering index works, they simply can't even fathom that it can make a query 1000x more efficient and faster. They have to see it to believe it. – Tony Hinkle May 7 at 19:42
  • Who is "they"? The consultant or your business / organizational management? Someone else? Does consultant have some sort of incentive (indirect perhaps) to encourage this acquisition? And a good starting point is to ask for an actual definition of "poor performance". There are many good reasons to move reporting off a production server doing transaction processing - but the costs (and don't forget disaster recovery) are very real and usually significant. – SMor May 7 at 20:08
  • Microsoft Assessment and Planning Toolkit can show you the use of resources during a set period of time. – Ronaldo May 7 at 22:07
2

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:

  1. 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.
  2. 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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.