I'm running Microsoft SQL on a Windows Server 2016 virtual machine that supports Microvellum, a CAD application layered over AutoCAD. We have around 10 engineers using Microvellum, yet it has always performed poorly. The engineers have mentioned that the software was much faster at other companies. We did upgrade our physical server recently, and while we saw a slight improvement, we still face significant performance issues.
None of us have database administration experience, and Microvellum hasn't provided substantial help regarding this. The existing databases have gone without maintenance since their creation. I'm eager to get some guidance or be pointed in the right direction and I'm even open to hiring a consultant for assistance.
Here are some details about our databases:
1. data: 23GB
2. geometry: 17GB
3. workorder: 274GB
Although some of these databases are somewhat large, they aren't excessively so. We've noticed that our OS and data drives are dynamic, and the data drive is consistently active at nearly 100%, with an average response time of 70ms.
I've also checked the SQL Server wait statistics and noticed:
- PAGEIOLATCH_SH: 718 seconds (45.8 million waits)
- HADR_FILESTREAM_IOMGR_IOCOMPLETION: 490 seconds
- LATCH_EX: 23 seconds
I'm considering converting the OS and data drives to static. Would that help? Any insights would be appreciated!
1 Answer
First, can you let us know which version of SQL Server you’re using? That can impact performance. It sounds like converting the disks to static is definitely a good move; you want to make sure the space is constantly available to avoid any delays from movement or rewrites. Also, check if your CPU is fully utilized, and keep an eye on pagination issues. Have you looked into SQL Server optimizations like splitting page storage into multiple files or improving your indexing? Oh, and consider using RAID or NVMe for your VM disks if possible.
Thanks for the advice! We’re running SQL Server 2017 now. The host is on a new RAID setup, but I need help with SQL optimizations. Any tips on where to start, especially with indexing?