SQLTeam.com | Weblogs | Forums

Server Capacity


#1

Hi,
Now our current server is server 2008 R2 and 24 GB RAM. Now all our
application And SQL(2008R2) with 15 GB DB in same server and now a days
finding many issues in the performance of the website. Here we are
planning to split our server to two ,One
for Application and another for DB.here I wanted to ask u which
configuration is good for our purpose.

1.We need 100 GB SQL DB CApacity iN DB

Which SQL version is good?
What configuration of Database server will give good performance?

2.Application Includes crystal reports which is eating more RAM ,This case what is the good configuration of application server.

I am not much aware about the server configuration details. Can anyone suggest me to find the better choice?


#2

before spending the money on new servers, figure out what is causing the performance problems. Usually it's one of these (or some combination)

  1. CPU
  2. RAM
  3. IOPS
  4. Network

It is a good idea to separate your application (web) server from the DB server and not just for performance. Security, availability and maintainability are also big drivers. I'd go with SQL Server 2014. If it's the only thing on the new box (should be) set the max memory to 2gb less than the installed ram.


#3

The first thing I would do is check that the Maximum Memory for SQL has been restricted.
If it has not been then SQL will try and grab it all leaving little room for Windows thus causing everything to slow down.

If the result of the following is greater than 23 there is a problem

SELECT cntr_value/1024/1024 AS MaxMemoryinGB
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Total Server Memory (KB)';

If there is a problem and given that there are other processes on the server, I would first try setting MaxMemory to 15GB.

EXEC sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE;  
GO  
EXEC sp_configure 'max server memory', 15360;  
GO  
RECONFIGURE;  
GO  

You could then monitor server memory and maybe increase MaxMemory up to about 21GB depending on what other processes and Windows are using.