I need to migrate extremely large quantities of data (millions of files, Terabytes of data) to an SQL cluster. The migration process is partitioned into weekly migrations, with a few hundreds of new users (i.e request-generators) every week.
我需要将极大量的数据(数百万个文件,太字节数据)迁移到SQL群集。迁移过程被划分为每周迁移,每周有几百个新用户(即请求生成器)。
So far things have run pretty smoothly, but lately i've discovered that the SQL cluster started to act quite strangely. The CPU usage is sound and stable at about 20%, but the SQL-process is constantly allocating new memory until there is nothing left (about 12GB). When this happens the process "dumps" all memory and starts climbing towards 12GB anew. During this dump, it is often the case that the server gets unresponsive and eventually timeouts, which just cannot happen during this weeks migration.
到目前为止,事情运行得相当顺利,但最近我发现SQL集群开始表现得非常奇怪。 CPU使用率稳定且稳定在20%左右,但SQL进程不断分配新内存,直到没有任何东西(大约12GB)。当发生这种情况时,该过程“转储”所有内存并开始重新攀升至12GB。在此转储期间,通常情况下服务器无响应并最终超时,这在本周迁移期间不会发生。
Is this allocate-and-dump behaviour common for SQL clusters? Is it possible to make configurations to it so this will never happen, or at least not congest the whole database? Does anyone have experience with large migration jobs?
这种分配和转储行为对于SQL群集是否常见?是否可以对其进行配置,以便永远不会发生,或者至少不会使整个数据库拥塞?有没有人有大型移民工作的经验?
When looking through the event logs i found some WMI-warning preceeding the timout. We're using System Center Operations Manager 2007 to overlook the system, could this explain this behaviour?
查看事件日志时,我发现了一些警告之前的WMI警告。我们使用System Center Operations Manager 2007来忽略系统,这可以解释这种行为吗?
Thankful for any help!
感谢任何帮助!
1 个解决方案
#1
No, this is not normal behaviour.
不,这不是正常行为。
SQL Server will dynamically allocate memory as required and will free memory appropriately when under pressure to do so. It should not however dump the entire contents.
SQL Server将根据需要动态分配内存,并在压力下适当地释放内存。但是它不应该转储整个内容。
Can you please provide more details about your environment and qualify what your SQL memory configuration is.
您能否提供有关您的环境的更多详细信息,并确定您的SQL内存配置。
If you require detailed assistance, feel free to drop me an email and please provide the results of the DBCC command.
如果您需要详细帮助,请随时给我发电子邮件,并提供DBCC命令的结果。
DBCC memorystatus
#1
No, this is not normal behaviour.
不,这不是正常行为。
SQL Server will dynamically allocate memory as required and will free memory appropriately when under pressure to do so. It should not however dump the entire contents.
SQL Server将根据需要动态分配内存,并在压力下适当地释放内存。但是它不应该转储整个内容。
Can you please provide more details about your environment and qualify what your SQL memory configuration is.
您能否提供有关您的环境的更多详细信息,并确定您的SQL内存配置。
If you require detailed assistance, feel free to drop me an email and please provide the results of the DBCC command.
如果您需要详细帮助,请随时给我发电子邮件,并提供DBCC命令的结果。
DBCC memorystatus