February 27, 2018 at 3:30 pm
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*>
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply