W P Staff asked 7 years ago
My CPU and memory consumption is through the roof, what need to be done?
W P Staff answered 7 years ago
SBS 2011 in it’s default configuration will experience very high memory usage caused by the three SQL databases that are running and in my experience will eventually cause poor performance where physical memory is being consumed by these databases. In particular I have noticed extremely High memory usage in SBS 2011 by the DataCollectorSvc.exe process, it was using around 12 GB!
There are three databases:
- SBSMONITORING
- SHAREPOINT
- WINDOWS INTERNAL DATABASE (MICROSOFT##SSEE)
All three of them have the potential to consume a great deal of memory and by default the memory limit on these instances is set to 2147483647 MB – which is 2048 Terabytes. Given that SBS 2011 supports a maximum of 32Gb of memory, restricting the memory usage on the databases can give you some of your memory back and improve the performance of the machine.
Because all three databases are separate instances you need to connect and modify each one separately.
We can use one instance of SQL Server Management Studio to manipulate all three instances, which makes things a bit easier. Open SQL Server 2008 Management studio by doing the following:
Start -> All Programs -> Microsoft SQL Server 2008 R2 -> Right click on “SQL Server Management Studio” and choose, run as administrator:
Reduce memory usage of the SBSMONITORING, SHAREPOINT and Windows Internal Databases
Once SQL Server Management Studio has opened you will be prompted to connect to a server, enter SERVERNAME\SBSMONITORING. In this example the server is simply called SBS:
Once connected to your database, connect to the other two databases at the same time by going to File -> “Connect Object Explorer”. This time in the server name enter:
SERVERNAME\SHAREPOINT
Once again to go File -> “Connect Object Explorer”. Enter:
\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query
All being well you should now have SQL Management Studio with all three databases listed like this:
Repeat the following procedure for each database:
Right click on the name of the SQL Instance and choose properties:
Click on the “Memory” option on the left hand side and adjust the memory figure as appropriate for your system. The system in this example has 32Gb of physical ram so I have chosen to give each database 2Gb. If you have less memory you will need to adjust according to your own system.
After you have adjusted all three databases you can quit SQL Server Management Studio and then restart the following services for the changes to take effect:
- SQL Server (SBS Monitoring)
- SQL Server (Sharepoint)
- Windows Internal Database (MICROSOFT##SSEE)
Hopefully after doing this SBS 2011 should run a bit smoother, on this system the SQL Databases were using over 16Gb of Ram and it was running very slowly.