0

I'm trying to identify what users are describing as slow application performance. I have a hunch it may be related to the application itself and not an issue with the SQL server. I've went over many of the usual suspects to identify common bottlenecks.

  • Is there anything i interpreted incorrectly?
  • Is there anything i neglected to review?

The MS SQL server (11.0.5058) is running on Hyper-V, 2012r2 with the following assigned to the guest: 72GB ram, 16 processors, 4 disk controllers with tempDB, OS, DB, Program Files all on separate VHDs.

Ran perfmon over the course of a day

% Processor Time < 40% Max

Avg Disk Q Length 0.003 Avg - 0.41 Max

% Disk Time Avg 0.0038

Avg Avail MBytes 5010

Buffer Cache Hit Ratio 99.996 Avg 98.614 Min

I don't see any issues with disk, mem, or processor. Where should i look next?

  • Brent's right, you're looking at the wrong things. First, also ask what's slow in particular? Have they used a stopwatch, not just gut feeling? What does Extended Events or Profiler tell you? Have you worked with whoever managed infrastructure and network and SAN at least to ask them if they know of anything? Is the host overprovisioned? Has it always been slow, or did that just start? Are there huge end of year/beginning of year sets of work happening on any hardware involved (hosts and SAN included)? – Anti-weakpasswords Jan 10 '18 at 3:19
9

I'm going to be brutally honest here: those are not the right metrics to look at, and wherever you're looking that gives you those metrics is wildly outdated. Those are not the usual suspects.

In the year 2018 (and indeed, for the last several years), the way you diagnose a slow SQL Server is by using wait stats. SQL Server is constantly tracking what queries are waiting on, and you can see it in the DMV sys.dm_os_wait_stats.

My favorite way to visualize that is with sp_BlitzFirst @SinceStartup = 1. (Disclaimer: it's my favorite because I wrote it, and it's free & open source.)

Try editing your answer to include a screenshot of your wait stats from sp_BlitzFirst, and we may be able to get you a lot closer.

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.