we are having a performance problem and we are trying to tune the database.
Decisions were made long ago and here is what I found when I got here:
Current system consists of a SQL Server 2005 Enterprise sp3 Cluster installation with logical disks residing in SANS and 1000 remote users with SQL Server 2005 Express.
It has 4 merge publications and 1000 subscribers to each of them. We usually have between 10 and 40 concurrent users, (the ones with the sql server express), replicating between 08:00 and 19:00 Monday to Friday, as they usually work remotely and may be replicating from 500 kms away or even more.
Steps we have taken include:
- Checking disks --> they seem to have problems coping with the workload but they perform quite well otherwise.
- Checking indexes on server side--> they were causing problems but they are fixed now.
- Improved performance of tempb by icreasing the number of files.
- Reduced the time for the subscription to expire.
Any suggestion would be welcome