Tuesday, March 24, 2015

What is recommemded allocation memory for SQL Server ?

Best recommended allocation memory for SQL Server: 80 % of Total Memory

Step 1:
Find out the total server Merory

Step 2:
Select OBJECT_NAME, counter_name, cntr_value
From sys.dm_os_performance_counters Where counter_name = 'Total Server Memory (KB)'
GO
sp_configure 'show advanced options',1;
go
reconfigure
GO
--- Assume  Server Memory in total is 10GB
---- Allocate 80% of 10GB = 8GB = 8192 MB for SQL Server
sp_configure 'max server memory',8192
----in MB
go
reconfigure
go
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OVERRIDE
GO

Step 3:
Select OBJECT_NAME, counter_name,cntr_value
From sys.dm_os_performance_countersWhere counter_name ='Total Server Memory (KB)'
GO

No comments:

Post a Comment