sqlsrvr.exe 消耗大量内存
我有一个在 Windows XP 和 SQL Server 2000 Service Pack 3 上运行的 .NET 1.1 应用程序。该应用程序使用 Enterprise Library Data & 数据库。异常处理 dll。
当我提交一个调用一些存储过程的表单时最后在表中执行 INSERT 时，sqlsrvr.exe 进程不断增加，最多达到 300-500 MB。该表格需要 10 多分钟才能执行。
正常情况下同一页面执行时间不超过1分钟。
这里可能发生了什么，如何解决？
I have a .NET 1.1 application running on Windows XP and with SQL Server 2000 Service Pack 3. The application uses Enterprise Library Data & Exception Handling dlls.
When I submit a form which calls a few stored procs & finally execute an INSERT into a table, the sqlsrvr.exe process keeps on increasing up to 300-500 MB. The form takes more than 10 mins to execute.
The same page in normal condition, takes not more than 1 min to execute.
What could be happening here, and how could it be fixed?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
Sql Server 进程消耗该内存量是正常的。 SQL Server 将消耗大量内存，但会在操作系统内存压力下将其返还（基于 SQL Server 配置的最小和最大内存设置）。
如果您的查询速度很慢，请查看您的 SQL 查询和/或丢失的索引。
您描述的症状听起来可能是由参数嗅探引起的。确保您的统计数据和索引是最新的，也许可以发布您的查询。
The Sql Server process consuming that amount of memory is normal. SQL Server will consume a large amount of memory, but will give it back under OS memory pressure (based upon the SQL server configured min and max memory settings)
If your query is slow, look to your SQL queries and/or missing indexes.
The symptom you describe sounds like it may be caused by parameter sniffing. Ensure your statistics and indexes are up to date, and perhaps post your query.
好吧，休在哪里？我有一个 16GB 的 SQL Server ;)
SQL Server，除非在 CNOFIGURATION 中另有说明，否则将尽可能多地缓存。它读取的每一页（8kb）都会保留在内存中。光盘速度很慢。内存很快。它假设它是一个服务器，因此可以使用内存。
嗯，让我们看看。
我非常强烈地指出 1（索引缺失）或 2（蹩脚的硬件）。不要认为这是针对个人的，但你的整个设置听起来真的已经过时了，而且你并不真正觉得自己掌握了一切，所以这只是基于没有过去经验的一次尝试。
我在这方面遇到的最残酷的事情是一个索引为零的人，并且一个网页执行了大约 30 个查询，所有这些查询都导致表扫描并覆盖了可怜的普通廉价光盘，以至于计算机在半个小时内完全没有响应。
首先：
除了——嗯——你没有提供足够的信息之外，很难说什么。
我曾见过这样的说法：
会导致 12 层级联存储过程中出现大量低效操作。
也就是说，如果是硬件 - 升级到 SSD。如今 120GB SSD 非常便宜，而且速度非常快。我现在使用 Velociraptors 3 进行一些操作，其最高可达 60.000 IPS，而更高端的光盘则难以达到 400。
Ok, where is the hugh here? I have a 16gb sql server ;)
SQL Server, UNLESS TOLD OTHERWISE IN THE CNOFIGURATION, will cache as much as it can. Every page (8kb) it reads stays in memor. Discs are slow. ram is fast. It asumes it is a server and thus can use the memory.
Hm, lets see.
I am quite strongly pointuing into either 1 (indices missing) or 2 (crappy hardware). Dont take it personal, but you whole setup soudns really outdated and you dont really yound like you are on top of things, so this is just a shot into the wild based no past experience.
The most brutal I had in this area was a person with zero indices and a web page that executed like 30 queries all resulting in table scans and overlaoding the poor normal cheap disc so much the computer got toally unresponsive for half an hour.
To start:
It is VERY hard to say ANYTHING except - well - that you dont give nough information.
I have seen statements like this:
explode into tons of inefficient operations in 12 layers of cascaded stored procedures.
That said, if it is hardware - upgrade to a SSD. A 120gb SSD is VERY cheap these days and EXTREMELY fast. I have Velociraptors 3 now for some oeprations and tehy do up to 60.000 IPS - while a higher end disc struggles with 400.