We’ve had some users reporting poor performance, so ran the PDT tool which reported our DB is more than 2x to slow. Any recommendations on where to start looking to troubleshoot why our DB is so slow?
Our IT manager responsible for our infrastructure thinks it’s crazy how much hardware we have available(CPUs, RAM, etc), how few user’s we have(<10) and yet the PDT is reporting such slow performance… He jokingly asked if we need a super computer to support Epicor for such few users.
Edit: Here are the test results I’m referring to:
[10,334.0] milliseconds taken to finish SQL Query test. During the test the loop executed [1000] times. Each iteration of loop had 2 SQL read queries. Time <= 3500ms PASS, Time >= 4000ms FAIL, else WARNING.
How is the maintenance of the DB? I’m not a db admin but I know there are some tasks like index rebuild, update stats, defragmentation and maybe some other cleanup.
We are virtualized and hosted in a datacenter. We had horrible PDT numbers and I opened a support ticket. I was advised to delete the virtual NIC on the app server and recreate it. Once I did we went from 10+ seconds down to 1.5 seconds in this test.
This might not pertain to your situation, but might want to look at your network as well as your DB.
What are your server specs? Physical or VM? Does SQL have enough memory (or perhaps too much memory)? Are you separating MDFs, LDFs, and TempDB on their own drives?
Our IT manager figured it out! It looks like there was another BIOS setting that controlled power / performance of the CPU other than C States. He changed it to Max performance and our DB query is now less than 3 sec where it was 8-10 sec.