I was troubleshooting some bad performance for a client yesterday and identified that there was a missing index for a particular set of queries that were heavily used
(this was for an ERP applicaton: Microsoft Dynamics NAV)
After adding these indexes database performance for the areas that were giving the client grief improved massively, however, during the visit I noticed that at times starting up the application took forever and other users were complaining of freezing and slow response (it didn't happen against all users simultaneously).
The infrastructure consists of a DB server, a service tier X 2 (that runs the application logic and caches data/business object metadata) and the client machine.
I checked the server CPU/memory stats on the service tier and it didn't look taxed at any point (CPU barely got above 50% at any one time and memory was barely touched - around 20-30%)
The SQL server was set to use unlimited memory so that needs addressing but there was actually free memory on the SQL box as all their databases combined don't exceed 10 GB and there is 12 in the server (though the page life expectancy perf counter didn't get much above 200-300 so I'm not sure why this would be)
The users mentioned this had only started happening recently (last 3 weeks) and also mentioned that other applications such as Excel were taking a long time to open files from the network so I had a look at some of the performance counters on the server and ran Brent Ozar's sp_Blitz just to check if there were any things that needed addressing immediately
The script gave me this report on the
3437.8 hours of waits, 58.6 minutes average wait time per hour, 100.0% of waits, 0.0% signal wait, 20062055 waiting tasks, 616.9 ms average wait time.
The server has 4 virtual cores
I ran the same script on a server on another client's network (running the same solution) and the script came back with no significant wait stats
I'm suggesting to the client that since they don't need the 16 GB allocated to the service tiers (as they barely get near 4-6 GB usage) that they should consider reallocating some of the memory to SQL as their DB will grow (NAV databases tend to grow quickly), but I want to advise them best on if there could be a network issue
How do I correctly interpret these waits? Am I looking at a potential network issue as my gut says yes?