Memory and CPU is very high for sql related processes on SBS 2011

0

My CPU and memory consumption is through the roof, what need to be done?

asked March 24, 2015
Andrey
0

1 Answer

0

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:

How to start SQL Server Management Studio in SBS 2011 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:

Connect to SBSMONITORING Database with SQL Server Management Studio

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

Connect to SHAREPOINT database on SBS 2011 with SQL Server Management Studio

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:

SBS 2011 - SQL Server Management Studio connected to SBSMONITORING, SHAREPOINT and Windows Internal Databases

 

Repeat the following procedure for each database:

Right click on the name of the SQL Instance and choose properties:

Modify SQL Instance Properties in SBS 2011 using SQL Management Studio

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.

Setting the maximum memory usage for an SQL Instance on SBS 2011

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.

 

Andrey
0

Your Answer

Please login to post questions.