0

I've been performance tuning SQL servers for a few years as a DBA.

I'm trying to create a fast-food version of performance metrics that can quickly (in 5 minutes) and accurately (provable) answer a question from Management "Does this server need more/faster _ ?"

'_' being one of these 4 possible bottelenecks in an IT-stack bottom up (from server-perspective, without going in to the app/code/ui):

  1. NETWORK

  2. DISK

  3. MEMORY

  4. CPU

There are thousands of counters, articles, products to help monitor these. But is there a simple, instant and accurate script that can pinpoint if any of these 4 need to be scaled up or scaled out ?

e.g. sys.dm_os_wait_stats - SOS_SCHEDULER_YIELD has high signal waits => need either more or faster CPU.
PAGEIO_LATCH => need more files or faster DISK

Are these 2 accurate? Does Page Life Expectancy accurately 'PROVE' the need for more memory ? What is your GO-TO script for diagnosing a performance issue ?

I've used sp_whoisactive, sp_blitz, Glenn's DMVs, Spotlight, Idera etc. but am yet to come across a script that will satisfy a CIO's question about where to spend budget money, or that will correctly blame the problems on bad code, or slow SAN, or the ISP.

Everytime any (Network/Systems/DBA/App) engineer points fingers at the other team, we have to 'PROVE' our statement, and with Virtualization and Cloud, without ideal test environments, without downtime, it's getting increasingly difficult to provably pinpoint the source of server performance issues, other than maybe Task Manager <excuse the rant>

closed as off-topic by Joe Obbish, hot2use, Tom V, McNets, Mr.Brownstone Mar 1 '18 at 11:53

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Shopping list question - questions about which tool, library, product or resource you should use are off-topic here because they quickly become obsolete and often are just about the preferences of the answerer. If you have an issue with or a question about a specific tool, please revise your question to conform to that scope." – Joe Obbish, hot2use, Tom V, McNets, Mr.Brownstone
If this question can be reworded to fit the rules in the help center, please edit the question.

  • do you baseline your servers ? if not then that is the first step to identify what is not normal. Then spend time going after the unusual stuff like CPU / Memory / Disk etc. – Kin Shah Feb 28 '18 at 3:51
  • I'm sure you have your reasons but I think you're making a mistake omitting the application from consideration. No amount of memory will compensate for cross-joining a ten million row table to itself. – Michael Green Mar 1 '18 at 3:13
6

No. Say you have a server with:

  • High PAGEIOLATCH waits
  • High IO stall times
  • Low Page Life Expectancy (say, consistently under 30)
  • 128GB RAM

You could fix this scenario with:

  • Index tuning - because maybe the queries are scanning a table too large to fit in memory
  • Query tuning - because maybe they're using non-sargable predicates, or not caching
  • Configuration - because maybe some wacko set max server memory too low, or we're using the wrong Cardinality Estimator for our code
  • Adding memory - but now you have to check version & edition, because your Windows or SQL Server may not support more memory
  • Faster storage - but this is one heck of an expensive fix

There's no one metric that tells you which of those solutions is the right one. Welcome to performance tuning. Ain't no easy button here.

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