PAE SQL server questions...

Hey all,

I'm wondering what command I could issue to see the amount of memory a named instance is using from within the instance itself. We've enabled address extensions (PAE), and task manager no longer shows the correct amount of mem being used by the process. Under perfmon, I've added the object MSSQL$INSTANCENAME:Memory and I'm looking at the Total Server Memory. I'm seeing 4 gigs, when max memory is capped at 2gigs. This must show the server memory and not the instance memory ? Is there some way to see the instance memory ?

Cheers,
-KilkaDBCC MEMORYSTATUS. It'll give you enough to start with. Coupled with MEMUSAGE, you can pi-point the exact object that hogs the memory.|||Thanks rdjabarov,

I'll have to check this out on our testing environments. I've read that MEMUSAGE can cause the instance to crash, I'll have to do some more testing when I get back to work to verify that it'll be safe to use on live.

Cheers,
-Kilka

Package Execution takes 100% CPU

Hi all,

I have a package which updates a table which contains a large amount of rows, when this package is runnning the process consumes 100% CPU and other services are crashing (SSAS, SPS, SSRS).

Is there any parameter which could be set to avoid SSIS to consume 100% of the CPU during the execution of this package ?

Try to redefine your SSIS package...

Regards!

|||Is SSIS taking up the processor, or is it the relational engine? I assume it is the latter.

First off, if SQL Server is sharing the box with other processes, you need to limit the max amount of memory it can take. Otherwise, it is likely to hog it all and starve the other processes.

Second, if you have a lot of rows to update you should bulk insert the changes to a staging table and then run a single UPDATE statement joined between the staging and the target table. Using the OLE DB Command, SSIS can only update a single row at a time, which is very inefficient.

Finally, for a large table you need to be very careful about your indexes. A table with a lot of indexes will be much slower to update than one without. Ideally, you only want one index on this table, the one that will allow you to find the row to update. Obviously, you'll need to balance this with the intended use of the table.

|||Thanks for your answer.

Package error: Cannot create thread

I have a child package that has been run successfully multiple times in the last month +. Each time with roughly the same amount of data, give or take a few thousand rows.

Suddenly, this child package is now giving me the following errors from the log file:

Error: 2006-11-17 12:04:19.98
Code: 0xC0047031
Source: DFLT Primary DTS.Pipeline

Description: The Data Flow task failed to create a required thread and cannot begin running. The usually occurs when there is an out-of-memory state.

End Error

Error: 2006-11-17 12:04:20.03
Code: 0xC004700E
Source: DFLT Primary DTS.Pipeline

Description: The Data Flow task engine failed at startup because it cannot create one or more required threads.

End Error

I tried taking the child out of parent and running it by itself. I still get the same error. There are three other child packages that run on the exact same data and they have no problems. The control flow for the package first runs an SQL command. Then it has a data flow. The data flow grabs records from the source, adds two derived columns, looks up data and then stores to the destination. Relatively easy compared to other packages that are running just fine.

I've had our network people check the both the server running SSIS and the database server (two different machines) and there are no memory spike while the package is running.

Any ideas?

The problem could also be related with all the other applications/processes running on that box. Can you try stopping other SSIS packages and applications, and try running the problematic one? if that works, that means the machine is maxed out on available number of threads. or memory...

It's also possible that other applications might be running zombie processes on the box, even if the process seems to exit, there could be a thread or memory leak.

I'd use perfmon tool to read some of the critical resources for the box in which SSIS is running. You don't need to look at the database server, as SSIS creates a new process only on the box it's running. I'd check : thread/process, total thread, memory/process, total memory, page faults.