Migration of huge quantities of data to SQL

Question

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.

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.

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?

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?

Thankful for any help!

Answer 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.

Can you please provide more details about your environment and qualify what your SQL memory configuration is.

If you require detailed assistance, feel free to drop me an email and please provide the results of the DBCC command.

DBCC memorystatus

Answer 2

Hi, I ran the DBCC memorystatus-command and this is the output, i don't know if you can make anything out of these numbers but.. :)

Memory Manager KB

---

VM Reserved 17002104 VM Committed 10609592 AWE Allocated 0 Reserved Memory 1024 Reserved Memory In Use 0

(5 row(s) affected)

Memory node Id = 0 KB

---

VM Reserved 16996344 VM Committed 10603920 AWE Allocated 0 MultiPage Allocator 69976 SinglePage Allocator 3598304

(5 row(s) affected)

MEMORYCLERK_SQLGENERAL (Total) KB

---

VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 34904 MultiPage Allocator 6448

(7 row(s) affected)

MEMORYCLERK_SQLBUFFERPOOL (Total) KB

---

VM Reserved 16809984 VM Committed 10419968 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 0 MultiPage Allocator 136

(7 row(s) affected)

MEMORYCLERK_SQLQUERYEXEC (Total) KB

---

VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 240 MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_SQLOPTIMIZER (Total) KB

---

VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 5496 MultiPage Allocator 128

(7 row(s) affected)

MEMORYCLERK_SQLUTILITIES (Total) KB

---

VM Reserved 120 VM Committed 120 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 488 MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_SQLSTORENG (Total) KB

---

VM Reserved 77760 VM Committed 77760 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 11344 MultiPage Allocator 10560

(7 row(s) affected)

MEMORYCLERK_SQLCONNECTIONPOOL (Total) KB

---

VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 9440 MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_SQLCLR (Total) KB

---

VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 8 MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_SQLSERVICEBROKER (Total) KB

---

VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 392 MultiPage Allocator 304

(7 row(s) affected)

MEMORYCLERK_SQLHTTP (Total) KB

---

VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 8 MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_SNI (Total) KB

---

VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 1112 MultiPage Allocator 16

(7 row(s) affected)

MEMORYCLERK_FULLTEXT (Total) KB

---

VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 16 MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_SQLXP (Total) KB

---

VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 16 MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_BHF (Total) KB

---

VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 496 MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_HOST (Total) KB

---

VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 16 MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_SOSNODE (Total) KB

---

VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 43232 MultiPage Allocator 21264

(7 row(s) affected)

MEMORYCLERK_SQLSERVICEBROKERTRANSPORT (Total) KB

---

VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 48 MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_OBJCP (Total) KB

---

VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 1857520 MultiPage Allocator 16952

(7 row(s) affected)

CACHESTORE_SQLCP (Total) KB

---

VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 1474424 MultiPage Allocator 10328

(7 row(s) affected)

CACHESTORE_PHDR (Total) KB

---

VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 46992 MultiPage Allocator 3104

(7 row(s) affected)

CACHESTORE_XPROC (Total) KB

---

VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 64 MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_TEMPTABLES (Total) KB

---

VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 256 MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_NOTIF (Total) KB

---

VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 16 MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_VIEWDEFINITIONS (Total) KB

---

VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 16 MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_XMLDBTYPE (Total) KB

---

VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 8 MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_XMLDBELEMENT (Total) KB

---

VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 8 MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_XMLDBATTRIBUTE (Total) KB

---

VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 8 MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_STACKFRAMES (Total) KB

---

VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 0 MultiPage Allocator 8

(7 row(s) affected)

CACHESTORE_BROKERTBLACS (Total) KB

---

VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 480 MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_BROKERKEK (Total) KB

---

VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 8 MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_BROKERDSH (Total) KB

---

VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 8 MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_BROKERUSERCERTLOOKUP (Total) KB

---

VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 8 MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_BROKERRSB (Total) KB

---

VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 8 MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_BROKERREADONLY (Total) KB

---

VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 32 MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_BROKERTO (Total) KB

---

VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 8 MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_EVENTS (Total) KB

---

VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 16 MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_SYSTEMROWSET (Total) KB

---

VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 7464 MultiPage Allocator 0

(7 row(s) affected)

USERSTORE_SCHEMAMGR (Total) KB

---

VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 24288 MultiPage Allocator 0

(7 row(s) affected)

USERSTORE_DBMETADATA (Total) KB

---

VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 31800 MultiPage Allocator 0

(7 row(s) affected)

USERSTORE_TOKENPERM (Total) KB

---

VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 3896 MultiPage Allocator 0

(7 row(s) affected)

USERSTORE_OBJPERM (Total) KB

---

VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 7736 MultiPage Allocator 0

(7 row(s) affected)

USERSTORE_SXC (Total) KB

---

VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 3816 MultiPage Allocator 0

(7 row(s) affected)

OBJECTSTORE_LBSS (Total) KB

---

VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 256 MultiPage Allocator 496

(7 row(s) affected)

OBJECTSTORE_SNI_PACKET (Total) KB

---

VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 15240 MultiPage Allocator 48

(7 row(s) affected)

OBJECTSTORE_SERVICE_BROKER (Total) KB