SQLServer最大内存设置的参考表
来源:互联网 发布:excel两列找出相同数据 编辑:程序博客网 时间:2024/06/07 02:53
为保证系统有足够的内存,减少虚拟内存交换的影响,SQLServer的最大内存应有设置,经验表如下。如果系统还运行了其他服务,SQLServer的最大内存应相应减少。
原帖
http://www.sqlservercentral.com/blogs/glennberry/2009/10/29/suggested-max-memory-settings-for-sql-server-2005_2F00_2008/
Suggested Max Memory Settings for SQL Server 2005/2008
- Posted on 29 October 2009
- Comments
- Briefcase
It is pretty important to make sure you set the Max Server memory setting for SQL Server 2005/2008 to something besides the default setting (which allows SQL Server to use as much memory as it wants, subject to signals from the operating system that it is under memory pressure). This is especially important with larger, busier systems that may be under memory pressure.
This setting controls how much memory can be used by the SQL Server Buffer Pool. If you don’t set an upper limit for this value, other parts of SQL Server, and the operating system can be starved for memory, which can cause instability and performance problems. It is even more important to set this correctly if you have “Lock Pages in Memory” enabled for the SQL Server service account (which I always do for x64 systems with more than 4GB of memory).
These settings are for x64, on a dedicated database server, only running the DB engine, (which is the ideal situation).
Physical RAM MaxServerMem Setting
2GB 1500
4GB 3200
6GB 4800
8GB 6400
12GB 10000
16GB 13500
24GB 21500
32GB 29000
48GB 44000
64GB 60000
72GB 68000
96GB 92000
128GB 124000
If you are running other SQL Server components, such as SSIS or Full Text Search, you will want to allocate less memory for the SQL Server Buffer Pool. You also want to pay close attention to how much memory is still available in Task Manager. This is how much RAM should be available in Task Manager while you are under load (on Windows Server 2003):
Physical RAM Target Avail RAM in Task Manager
< 4GB 512MB – 1GB
4-32GB 1GB – 2GB
32-128GB 2GB – 4GB
> 128GB > 4GB
You can use T-SQL to set your MaxServerMemory setting. The sample below sets it to 3500, which is the equivalent of 3.5GB. This setting is dynamic in SQL Server 2005/2008, which means that you can change it and it goes into effect immediately, without restarting SQL Server.
-- Turn on advanced options
EXEC sp_configure'Show Advanced Options',1;
GO
RECONFIGURE;
GO
-- Set max server memory = 3500MB for the server
EXEC sp_configure'max server memory (MB)',3500;
GO
RECONFIGURE;
GO
-- See what the current values areEXEC sp_configure;
You can also change this setting in the SSMS GUI, as you see below:
Finally, I have learned that it is a good idea to temporarily adjust your MaxServerMemory setting downward by a few GB if you know you will be doing a large file copy on your database server (such as copying a large database backup file).
- SQLServer最大内存设置的参考表
- YARN和MapReduce的内存设置参考
- IDEA内存设置参考
- 查看和设置SQLServer数据库的最大连接数
- Ant中设置JVM的最大内存
- Ant中设置JVM的最大内存
- SQL设置SQLServer最大连接数
- SQL设置SQLServer最大连接数
- SQLSERVER SQLServer 设置“打开表”的行数方法
- Sql server 设置最大内存
- java查看最大内存设置
- java 可设置最大内存
- java 可设置最大内存
- 设置Redis最大占用内存
- tomcat最大最小内存设置
- 设置Redis最大占用内存
- 设置tomcat5.5以上的jvm最大内存值
- 设置tomcat5.5以上的jvm最大内存值
- view的getChildMeasureSpace方法总结
- C# 反射技术简介
- 异常java.lang.Thread.dumpStack(Unknown Source)
- 手把手入门神经网络系列(2)_74行代码实现手写数字识别
- 机器学习:Bootstrap
- SQLServer最大内存设置的参考表
- PhotoView点击事件
- 杭电ACM1021
- 关于操作系统中pv操作的实现
- zhphp framework (二十八) token 加密与解密
- 导数据有感
- Web容器
- php 生成 ueediter 网页编辑器
- 【leetcode】4. Median of Two Sorted Arrays