We have a customer whose database has grown by about 50% over the last year - to around 12GB now. They have been complaining in a fall off in performance which has been hard to quantify - but can be seen in some (fairly complex) reports. The number of users is relatively small - 20ish.
The server is running Windows Server 2008 R2 on a virtulised platform (VMWare) with pretty fast links to a SAN. Initially 8GB of RAM was available, 6GB allocated to a fixed db engine cache.
An initial look at the server showed heavy fragmentation of the database file (1800 segments), that was defragmented but without any noticeable improvement. Looking at the resource monitor showed that the working set of the dbsrv10 process was using pretty well all the allocated RAM. It also showed very heavy disk activity on the dbfile when the "complex report" was run - taking about 2 mins to complete. A second run of the same report was almost instantaneous with no disk activity. Processor activity remained around 5%. If the same report was run a few hours later - it was slow again, suggesting that the required data had been moved out of cache.
From this we concluded that more RAM might help, and accordingly it was increased to 14GB and the fixed dbcache to 12GB. Re-running the same report after the database had been running for a day or two, produced little improvement. The working set of the db engine had increased, varying between 8 and about 10GB. From previous db activity all the required data should have been in cache. Looking at the disk activity showed a major difference - whereas before the activity related to the db file - it is now related almost completely to the Windows swap file. If I understand it correctly, the "hard faults" number also suggests that data required by the db engine is having to be retrieved from the paging file. Overall physical memory usage (reported by Resource Monitor) seems to vary between 75% & 100%, but doesn't seem to be driven by running particularly demanding queries.
Our thought now is try try reducing the db cache to give the RAM to the OS - say an additional 2GB to start with, but does that make sense?