紧急:如何处理 SQL Server2005 占用了8G内存?

[web718]

大家好,

现在数据库服务器共8G内存.SQLserver就占了7.7G,这几天查了一些资料,有人说使用SQL Server属性>内存,这种方法是法标不法本.怎么才可以分析到是哪些语句占用了这么多的内存?我看了所有语句,都没发现,创建的临时表.我也删了.请各忙帮帮忙.谢谢了.

机器情况:
1. 8个cpu
2. 8G内存
3. 做了镜像,读/写分离,做了分发服务器
4. 当前链接数:274
5.10个大型数据库

[xuejie09242]

如果没有别的程序，占用大些没太大的关系了。

[Q315054403]

不用处理呀。。。若仅RUN　SQLSERVER　的话
若要限制，则SP_Configure 关于Max Memory

[nzperfect]

sql server 提速的一个重要的依赖就是缓冲内存，所以sql server 会尽可能多的占用可用内存。
这是sql server 的缓存机制
如果想限制sql server 使用的最大内存，可以设置：
sp_configure 'max server memory', 6144 --设置sql server 最大内存是6G.

[you_tube]

查看哪些内部组件窃取了缓冲区池中大部分的页面；

select top 10 type,
sum(single_pages_kb) as stolen_mem_kb
from sys.dm_os_memory_clerks
group by type
order by sum(single_pages_kb)  desc

[lostwing2005]

"有人说使用SQL Server属性>内存,这种方法是法标不法本."
为什么这么说？不理解。。。你的问题把内存最大值改小就可以解决，5G够了。

[雄牛]

路过~~~~~
友情up~~~~~~~~

[dawugui]

解决SQL Server占用内存过多的问题

来源:IT专家网
　　经常看见有人问，MSSQL占用了太多的内存，而且还不断的增长; 或者说已经设置了使用内存，可是它没有用到那么多，这是怎么一回事儿呢?

　　首先，我们来看看MSSQL是怎样使用内存的。

　　最大的开销一般是用于数据缓存，如果内存足够，它会把用过的数据和觉得你会用到的数据统统扔到内存中，直到内存不足的时候，才把命中率低的数据给清掉。所以一般我们在看statistics io的时候，看到的physics read都是0。

　　其次就是查询的开销，一般地说，hash join是会带来比较大的内存开销的，而merge join和nested loop的开销比较小，还有排序和中间表、游标也是会有比较大的开销的。

　　所以用于关联和排序的列上一般需要有索引。

　　再其次就是对执行计划、系统数据的存储，这些都是比较小的。

　　我们先来看数据缓存对性能的影响，如果系统中没有其它应用程序来争夺内存，数据缓存一般是越多越好，甚至有些时候我们会强行把一些数据pin在高速缓存中。但是如果有其它应用程序，虽然在需要的时候MSSQL会释放内存，但是线程切换、IO等待这些工作也是需要时间的，所以就会造成性能的降低。这样我们就必须设置MSSQL的最大内存使用。可以在SQL Server 属性(内存选项卡)中找到配置最大使用内存的地方，或者也可以使用sp_configure来完成。如果没有其它应用程序，那么就不要限制MSSQL对内存的使用。

　　然后来看查询的开销，这个开销显然是越低越好，因为我们不能从中得到好处，相反，使用了越多的内存多半意味着查询速度的降低。所以我们一般要避免中间表和游标的使用，在经常作关联和排序的列上建立索引。

[--小F--]

[Quote=引用 1 楼 dawugui 的回复:]
解决SQL Server占用内存过多的问题

来源:IT专家网
　　经常看见有人问，MSSQL占用了太多的内存，而且还不断的增长; 或者说已经设置了使用内存，可是它没有用到那么多，这是怎么一回事儿呢?

　　首先，我们来看看MSSQL是怎样使用内存的。

　　最大的开销一般是用于数据缓存，如果内存足够，它会把用过的数据和觉得你会用到的数据统统扔到内存中，直到内存不足的时候，才把命中率低的数据给清掉。所以一般我们在看statistics io的时候，看到的physics read都是0。

　　其次就是查询的开销，一般地说，hash join是会带来比较大的内存开销的，而merge join和nested loop的开销比较小，还有排序和中间表、游标也是会有比较大的开销的。

　　所以用于关联和排序的列上一般需要有索引。

　　再其次就是对执行计划、系统数据的存储，这些都是比较小的。

　　我们先来看数据缓存对性能的影响，如果系统中没有其它应用程序来争夺内存，数据缓存一般是越多越好，甚至有些时候我们会强行把一些数据pin在高速缓存中。但是如果有其它应用程序，虽然在需要的时候MSSQL会释放内存，但是线程切换、IO等待这些工作也是需要时间的，所以就会造成性能的降低。这样我们就必须设置MSSQL的最大内存使用。可以在SQL Server 属性(内存选项卡)中找到配置最大使用内存的地方，或者也可以使用sp_configure来完成。如果没有其它应用程序，那么就不要限制MSSQL对内存的使用。

　　然后来看查询的开销，这个开销显然是越低越好，因为我们不能从中得到好处，相反，使用了越多的内存多半意味着查询速度的降低。所以我们一般要避免中间表和游标的使用，在经常作关联和排序的列上建立索引。

[/Quote]

学习

[nalnait]

    
如何进行SQL性能优化？－使用动态管理视图和函数 

　在SQL Server 2005中，提供了动态管理视图和函数(Dynamic Management Views and Dynamic Management Functions)，大大的方便了我们对系统运行情况的监控，故障诊断和性能优化。现在除了Debug以外，已经很少再对生产系统使用Profiler。 

 

下面我会Step by step的介绍，如何使用DMV和DMF来诊断系统情况，介绍到的全部知识都来自于联机丛书（Books Online）。

 

Issue:在新上线一个系统后，我发现数据库服务器的CPU有所升高，达到20-30%,Peak time的时候甚至会达到50%。于是我打开性能监视器，发现SQL Logins/sec平均超过2000。那么，我希望知道，是哪些SQL语句调用次数如此频繁，找到了这些SQL语句之后，就可以进行有针对的优化。

Step1:首先我们看看有什么DMV是适用的。在联机丛书的索引中输入动态管理视图 [SQL Server]，发现下面有个链接与执行有关的动态管理视图和函数，这里面有一个视图叫sys.dm_exec_query_stats，顾名思义，应该是对执行查询的统计信息。

Step2:看看sys.dm_exec_query_stats的描述和字段，这个对象是返回缓存查询计划的聚合性能统计信息。包含的字段有execution_count，表示计划自上次编译以来所执行的次数。OK，就是它了。

Step3:怎么用这个视图呢？那些列好复杂，一时没有头绪，往下找找看。有了！下面有个示例，按平均 CLR 时间返回有关前五个查询的信息。

 

SELECT TOP 5 creation_time, last_execution_time, total_clr_time,

    total_clr_time/execution_count AS [Avg CLR Time], last_clr_time,

    execution_count,

    SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,

    ((CASE statement_end_offset

        WHEN -1 THEN DATALENGTH(st.text)

        ELSE qs.statement_end_offset END

            - qs.statement_start_offset)/2) + 1) as statement_text

FROM sys.dm_exec_query_stats as qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st

ORDER BY total_clr_time/execution_count DESC;

GO


写不出来我总会改吧，照葫芦画瓢可轻松多了。

Step4: 我要做的是把排序条件的平均CLR时间改成平均每秒执行次数。看了一下字段列表，发现3个字段是有用的。creation_time 编译计划的时间。last_execution_time  上次执行计划的时间。execution_count 计划自上次编译以来所执行的次数。我考虑用(last_execution_time - creation_time ) /execution_count来得到平均每秒执行次数。修改后的SQL语句如下


SELECT TOP 5 creation_time, last_execution_time, 

execution_count / datediff(second, creation_time, last_execution_time) AS [Execute Count Per Second],

    execution_count,

    SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,

    ((CASE statement_end_offset

        WHEN -1 THEN DATALENGTH(st.text)

        ELSE qs.statement_end_offset END

            - qs.statement_start_offset)/2) + 1) as statement_text

FROM sys.dm_exec_query_stats as qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st

ORDER BY execution_count / datediff(second, creation_time, last_execution_time) DESC;


 Step5:执行后发现有点小问题，出现错误信息

Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.

错误信息说被零除，那么是有datediff(second, creation_time, last_execution_time)等于0的。OK，我们加点条件，标准的做法是判断datediff(second, creation_time, last_execution_time)

>0，我判断execution_count > 100，在这个地方也能够达到同样的效果。修改的结果如下：

 

SELECT TOP 5 creation_time, last_execution_time, 

execution_count / datediff(second, creation_time, last_execution_time) AS [Execute Count Per Second],

    execution_count,

    SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,

    ((CASE statement_end_offset

        WHEN -1 THEN DATALENGTH(st.text)

        ELSE qs.statement_end_offset END

            - qs.statement_start_offset)/2) + 1) as statement_text

FROM sys.dm_exec_query_stats as qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st

WHERE execution_count > 100

ORDER BY execution_count / datediff(second, creation_time, last_execution_time) DESC;


 

Step 6:再调整一下所需的字段，我对creation_time, last_execution_time其实没啥兴趣，我更关心这个SQL语句执行的时间，I/O方面的信息，看了下字段列表，有total_physical_reads 此计划自编译后在执行期间所执行的物理读取总次数,total_elapsed_time 完成此计划的执行所占用的总时间（微秒）。再除以execution_count就是平均值了。修改后的SQL语句如下：

 

SELECT     TOP 100 execution_count / datediff(second, creation_time, last_execution_time) AS [Execute Times Per Second], execution_count, 

total_logical_reads /execution_count AS [Avg Logical Reads],

total_elapsed_time /execution_count AS [Avg Elapsed Time],

SUBSTRING(st.text, (qs.statement_start_offset / 2) + 1, ((CASE statement_end_offset WHEN - 1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset) / 2) + 1) AS statement_text

FROM         sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st

WHERE     execution_count > 100

ORDER BY 1 DESC;


 

Step7:OK，差不多能满足我的需求了，但是美中不足的是，我们对数据库的访问全是通过存储过程来实现的，要找到这段SQL语句出自哪个存储过程，虽然不是很麻烦，但是还是要动点手脚的，要是能在这个查询中显示出来就好了。 这个查询中用到了sys.dm_exec_sql_text，看看这里面有啥内容。在联机丛书的索引中输入sys.dm_exec_sql_text。Bingo！对于诸如存储过程、触发器或函数之类的数据库对象，SQL 句柄派生自数据库 ID、对象 ID 和对象编号。太好了，要的就是这个，有了ID就可以转换成名字了，object_name函数是早已熟悉的。最终的结果如下

在实际应用的时候，再根据情况稍做修改即可。希望各位读者从这篇文章学到的不仅是sys.dm_exec_query_stats ，更重要的是了解到使用Books Online的方法。

 

SELECT     TOP 100 execution_count / datediff(second, creation_time, last_execution_time) AS [Execute Count Per Second], execution_count,

total_logical_reads /execution_count AS [Avg Logical Reads],

total_elapsed_time /execution_count AS [Avg Elapsed Time],

db_name(st.dbid) as [database name],

object_name(st.objectid, st.dbid) as [object name],

SUBSTRING(st.text, (qs.statement_start_offset / 2) + 1, ((CASE statement_end_offset WHEN - 1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset) / 2) + 1) AS statement_text

FROM         sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st

WHERE     execution_count > 100

ORDER BY 1 DESC;


本文转自：http://blog.myspace.cn/e/400261243.htm?type=0&comment=417931#417931

 

作者：MingleLui
出处：http://mingle.cnblogs.com/ 
本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，否则保留追究法律责任的权利。 

[menggang9801]

1. 如果你不做内存限制，占用的内存数肯定会越来越多的。

2. 你现在有performance方面的问题吗？ 如果没有，可以忽略这个问题，因为SQL Server可以根据OS内存自动调整。

[Leshami]

学习了。

[ALLOHA1984]

测试环境：P4双核3.0G，1.5G内存，WindowsXP sp3，同时运行办公软件。使用单张表，两个INTEGER型字段，共1000万条记录。数据库文件120M。如果为两个double型字段，1000万条数据数据库文件大小约为240M。SQLite使用文件数据，手动事务处理。使用内存数据库可以提高性能，却会占用大量内存，如1000万条将占用145M的物理内存，这点对于长期监控很不利，故不采用。

[edaix]

我也在查Sql server占用内存的情况，还没有明白。

[andrewSwine]

我就遇上了这个问题，网上很多人都说是正常的，因为会不断的增长，其实要看什么情况，如果是重启服务没多久就迅猛变成这样是不正常的，说明有作业或者其他任务没释放资源。所以建议楼主检查一下作业或其他任务，最好在监视一下。