Max Server Memory

Sakthivel Chidambaram recently created a calculator which can find out the max server memory value based on the input.
http://blogs.msdn.com/b/sqlsakthi/archive/2012/05/19/cool-now-we-have-a-calculator-for-finding-out-a-max-server-memory-value.aspx

you can try the tool from http://blogs.msdn.com/b/sqlsakthi/p/max-server-memory-calculator.aspx

Then Jonathan Kehayias made a post to clarify why the calculator doesn't work
http://www.sqlskills.com/blogs/jonathan/2012/05/default.aspx
Jonathan has another great post relative the max server memory
http://www.sqlskills.com/blogs/jonathan/post/How-much-memory-does-my-SQL-Server-actually-need.aspx

This reminded me another max server memory formula which I follow up for many years posted by other MVP Glenn Berry

http://www.sqlservercentral.com/blogs/glennberry/2009/10/29/suggested-max-memory-settings-for-sql-server-2005_2F00_2008/

a little script which I used to run from central management server and configured the max server memory for all sql servers

====================================================================
DECLARE @curMem int
DECLARE @maxMem int
DECLARE @sql varchar(max)
select @curMem=physical_memory_in_bytes/1024/1024 from sys.dm_os_sys_info
SET @maxMem = CASE
    WHEN @curMem < = 1024*2 THEN 1500
    WHEN @curMem < = 1024*4 THEN 3200
    WHEN @curMem < = 1024*6 THEN 4800
    WHEN @curMem < = 1024*8 THEN 6400
    WHEN @curMem < = 1024*12 THEN 10000
    WHEN @curMem < = 1024*16 THEN 13500
    WHEN @curMem < = 1024*24 THEN 21500
    WHEN @curMem < = 1024*32 THEN 29000
    WHEN @curMem < = 1024*64 THEN 60000
    WHEN @curMem < = 1024*72 THEN 68000
    WHEN @curMem < = 1024*96 THEN 92000
    WHEN @curMem < = 1024*128 THEN 124000
     END
SET @sql='
EXEC sp_configure ''Show Advanced Options'',1;
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure ''max server memory'','+CONVERT(VARCHAR(6), @maxMem)+';
RECONFIGURE WITH OVERRIDE;'

EXEC(@sql)
====================================================================

However, with Jonathan's post, that formula might not correct, and especially have on the big memory systems. There are 2 options which are mentioned in Jonathan's post
1. "reserve 1 GB of RAM for the OS, 1 GB for each 4 GB of RAM installed from 4–16 GB, and then 1 GB for every 8 GB RAM installed above 16 GB RAM. This has typically worked out well for servers that are dedicated to SQL Server. "

2. "((Total system memory) – (memory for thread stack) – (OS memory requirements ~ 2-4GB) – (memory for other applications) - (memory for multipage allocations; SQLCLR, linked servers, etc)), where the memory for thread stack = ((max worker threads) *(stack size)) and the stack size is 512KB for x86 systems, 2MB for x64 systems and 4MB for IA64 systems. The value for 'max worker threads' can be found in the max_worker_count column of sys.dm_os_sys_info "

I think I will use the first option as an initial setup, then follow up Jonathan's post to monitor the system memory status, and adjust it as needed. here is the script for the option 1

--reserve 1 GB of RAM for the OS,
--1 GB for each 4 GB of RAM installed from 4–16 GB,
--and then 1 GB for every 8 GB RAM installed above 16 GB RAM
DECLARE @curMem int
DECLARE @maxMem int
DECLARE @sql varchar(max)
select @curMem=physical_memory_in_bytes/1024/1024 from sys.dm_os_sys_info
SET @maxMem = CASE
    WHEN @curMem < = 1024*2 THEN @curMem - 512
    WHEN @curMem < = 1024*4 THEN @curMem - 1024
    WHEN @curMem < = 1024*16 THEN @curMem - 1024 - Ceiling((@curMem-4096) / (4.0*1024))*1024
    WHEN @curMem > 1024*16 THEN @curMem - 4096 - Ceiling((@curMem-1024*16) / (8.0*1024))*1024
     END
SET @sql='
EXEC sp_configure ''Show Advanced Options'',1;
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure ''max server memory'','+CONVERT(VARCHAR(6), @maxMem)+';
RECONFIGURE WITH OVERRIDE;'

EXEC(@sql)


except the max server memory, here are some other post regarding the sql memory setting:
1. Fun with Locked Pages, AWE, Task Manager, and the Working Set…
http://blogs.msdn.com/b/psssql/archive/2009/09/11/fun-with-locked-pages-awe-task-manager-and-the-working-set.aspx

2. Be Aware: Using AWE, locked pages in memory, on 64 bit
http://blogs.msdn.com/b/slavao/archive/2005/04/29/413425.aspx

3. Q & A: Does SQL Server always respond to memory pressure?
http://blogs.msdn.com/b/slavao/archive/2006/11/13/q-a-does-sql-server-always-respond-to-memory-pressure.aspx

4. Importance of setting Max Server Memory in SQL Server and How to Set it
http://blogs.msdn.com/b/sqlsakthi/archive/2011/03/12/importance-of-setting-max-server-memory-in-sql-server-and-how-to-set-it.aspx

Clean SQL Server Cache

           I lives alone, usually, I clean my small apartment at every weekend, wipe the table/firniture with cloth, clean the capet with vacuum cleaner, wash clothes with machine. the clea enviroment makes me feel confortable, and have a good start for the new week.

SQL Server memory cache just like an apartment(or house?), before we start testing , we'd better clean the memory cache first. just like we clean the house, we use tools to clean cache as well.

1. DBCC FREESYSTEMCACHE
BOOK ONLINE: manually remove unused entries from all caches or from a specified Resource Governor pool cache.
it has 2 parameters, the format is like:
DBCC FREESYSTEMCACHE ('ALL','default');

this is only the sample in BOOK online, there is no more description of the parameter. then I searched the parameter, here are some findings:

• Clean all caches

         DBCC FREESYSTEMCACHE ('ALL')

        sometimes if you can not shrink the tempdb log file, and get the error below:
“DBCC SHRINKFILE: Page X:xxxxxxx could not be moved because it is a work table"

try this command first, but note, this command will clear all cache and cause your system slower for a period of time.
http://blogs.technet.com/technet_blog_images/b/sql_server_sizing_ha_and_performance_hints/archive/2011/03/03/shrink-tempdb-transaction-log-fails-after-overflow.aspx

• Clean cache for a specific database:

         DBCC freesystemcache ('tempdb');

• Clean adhoc queries from cache

         DBCC freesystemcache ('sql plans');
 
          you can use the query below to check the adhoc queries status

select objtype,
count(*) as number_of_plans,
sum(cast(size_in_bytes as bigint))/1024/1024 as size_in_MBs,
avg(usecounts) as avg_use_count
from sys.dm_exec_cached_plans
group by objtype

sometimes a large number of adhoc query plans in the cache will cause performance issue:
http://sqlblog.com/blogs/lara_rubbelke/archive/2008/04/18/memory-pressure-on-64-bit-sql-server-2005.aspx
clean the sql plan cache is one of the resorts.

• Clear all table variables

        DBCC freesystemcache ('Temporary Tables & Table Variables');

• Clean TokenAndPermUserStore

        DBCC FREESYSTEMCACHE ('TokenAndPermUserStore')
        there is a KB descript it.
        http://support.microsoft.com/kb/927396
http://blogs.msdn.com/b/psssql/archive/2008/06/16/query-performance-issues-associated-with-a-large-sized-security-cache.aspx

• Other Cache Object

you can use the script below to get all cache object in the system, then use DBCC freesystemcache  to clean it
select name  from   sys.dm_os_memory_clerks group by name

2. DBCC DROPCLEANBUFFERS
Removes all clean buffers from the buffer pool.
please remember it only remove the "CLEAN" buffer from buffer pool, for what is "CLEAN" buffer, please refer to http://blogs.msdn.com/b/psssql/archive/2009/03/17/sql-server-what-is-a-cold-dirty-or-clean-buffer.aspx

so it is better to run checkpoint before run DBCC DROPCLEANBUFFERS, checkpoint will write all dirty pages back to disk, so you can release more buffer pool space

3. DBCC FREEPROCCACHE
Removes all elements from the plan cache, removes a specific plan from the plan cache by specifying a plan handle or SQL handle, or removes all cache entries associated with a specified resource pool

I think it is similar with "DBCC FREESYSTEMCACHE ", but it can only clean plan cache, and it provide parameter to let you control the clean more detail. you can specify the planid and pool name.
also this command has less impact than "DBCC FREESYSTEMCACHE ", MVP Glenn Berry mentioned the impact of FREEPROCCACHE is "pretty minor", and it is useful for some senarios
http://www.sqlservercentral.com/blogs/glennberry/2009/12/28/fun-with-dbcc-freeproccache/

4. DBCC FLUSHPROCINDB (@intDBID);
Flush the procedure cache for one database only


5. DBCC FREESESSIONCACHE
Flushes the distributed query connection cache used by distributed queries against an instance of Microsoft SQL Server.

so if you want to make a completely clean on the cache, you can try Rajesh Chandras 's script
DBCC FREESYSTEMCACHE(All)
DBCC FREESESSIONCACHE
DBCC FREEPROCCACHE
DBCC FLUSHPROCINDB( db_id )
CHECKPOINT
DBCC DROPCLEANBUFFERS

http://rschandrastechblog.blogspot.com/2011/06/how-to-clear-sql-server-cache.html