10-22-2013 04:39 AM
We're having some users complain about Act being slow at times.
When we check our server we see the SQL Express process going up and doing, regularly peaking to 50% or higher CPU.
This seems to happen quite constantly and even during moments when people are just doing some lookups and no really heavy activities in Act.
We have about 30 users, on average 20 to 25 active at the same time. About 17 000 contact records in our database and over 1 million history items in total. Nearly 14 years of data in the database. Database size: 1.9GB, 600 000 attachments. Database has been converted again and again so don't know the original version it was made in.
Intel I7 2600 (3.2GHz quad core)
8GB DDR3 Dual Channel RAM
Crucial M4 128GB SSD x 2 in Raid1
I'm pretty much left with the following questions:
1) Are we hitting some kind of limitation of the SQL Express version?
2) What are our options?
3) My colleague who worked here before me mentioned that in the old days we'd get a standard version of MSSQL with Act. Last version was 2005. Did it use to be included with Act?
4) Why does Act use MSSQL and not a free DB like PostgreSQL?
10-22-2013 08:25 AM
Have you tried running database maintenance on the database as this may help with speed issues as it re-indexes the database.
Also SQL Express 2008 R2 has a memory limit of 1GB so you may want to go to SQL Standard Server, but this would cost you as you woulod need to purchase CALS for it.
ACT! 8 Premuim for Workgroups ST version (2006) did come with a second disk that contain SQL standard but I cannot remember in what version it was stopped in. (This came with SQL 2000).
10-22-2013 03:53 PM
With 25 odd users at a time, a 1.9GB database size and SQL Express, I'd say it's fairly certain that you're hitting the limits of SQL Express.
SQL Express has a limit of 1GB RAM used for the instance, you in Task Manager you'd see the sqlsvr.exe process sitting at about 1.4GB of RAM (roughly 400MB for the .exe and it's other stuff, but a full 1GB for the ACT7 SQL Instance).
I'd upgrade to SQL Standard as a first step.
Talk to your ACC for pricing on getting licensed to use SQL Std, here in AU, it's about $120 per licensed user.
Hard drive I/O is the other common bottleneck I see, but running 2 SSD's in RAID means this likely isn't your problem.