Why is sort so slow?

Why is sort so slow?

• buzzsaw

  Duane Douglas wrote:

  jamie, thanks for the clarification. what happens if a 32 bit package attempts to use more than 3 GB of memory

  It'll spool data to disk as far as I'm aware. Which obviously is going to effect perf.

  -Jamie

  
  
  

• nkw

  I will try that.
  
  
  

• ITLionWoo

  isn't 3 GB of data beyond the capacity of a 32 bit ssis synchronous component
  
  

• ipfeifer

  It is sorting on a text column. But it seems to make no difference which column is the sort key.
  
  
  

• Yenan

  I rather expected it to use the disk for temporary storage rather than try to sort it in memory.
  
  
  

• Millercentral

  All,

  I have to agree that Sort seems inordinantly slow. For example I am running on a dual core, 4 gb ram, and to sort even 200k records it is terribly slow. It takes something into the hours to finish. I know that record size also is likely to affect this, but even still, it just seems unusable for all but the smallest sorting tasks. I have seen the Nsort 3rd party tool, but it seems that they are no longer giving out licenses for it. I assume this is one of those things that MS is currently working on in 2008. Until then, what are the best practices to keep Sort as performant as possible Following is a list I have compiled from this thread, can anyone add others

  Improving Sort Speed:

  1. Limit row count.

  2. Limit row size.

  3. Make sure temp directory is on a fast disk with plenty of space available.

  4. Try not to sort on text fields.

  5. Maximize available system RAM for the SSIS process to use.

  Anyone else have any items to add Are there any tricks that I am missing

  Thanks.

  
  

• vitagoni

  David C Baldauff wrote:

  Improving Sort Speed: 1. Limit row count. 2. Limit row size. 3. Make sure temp directory is on a fast disk with plenty of space available. 4. Try not to sort on text fields. 5. Maximize available system RAM for the SSIS process to use.

  
  
  0. Where possible, sort at the database level by using an ORDER BY clause and setting the IsSorted flag to true on the OLE DB input.
  
  
  

• PjV

  DIdi you try to put an index on the sort column (the best would be a clustered index though this one is physically order by design), that should be helpful.
  
  
  HTH, Jens Suessmeyer.
  
  ---
  http://www.sqlserver2005.de
  ---
  

• GTrz

  And what columns are in the sort key What is the type of those columns

  -Jamie

  
  
  

• CharlyA

  Duane Douglas wrote:

  isn't 3 GB of data beyond the capacity of a 32 bit ssis synchronous component

  Duane,

  No, not really. The total memory available per package is 3GB under certain conditions. That is your limit, there is no limit per component.

  SSIS Memeory usage: http://blogs.conchango.com/jamiethomson/archive/2005/05/29/1486.aspx

  -Jamie

  
  
  

• J1102

  jamie,
  
  thanks for the clarification. what happens if a 32 bit package attempts to use more than 3 GB of memory
  
  
  

• Richard Meyers

  I installed SP1 but it doesn't help. Is sort even capable of sorting something this big It seems like it performs really well until data size reaches some threshold and then it goes pathological. I have 4GB main memory on Windows Server 2003 and 4 CPUs so this is no slouch machine. Looking at Task Manager I'm showing 50GB total I/O and PF Deltas of 8000 after about 30 minutes.
  
  
  

• chief_druid_tma

  Do you have SP1 installed It somewhat improves Sort performance.
  
  Also, make sure the temporary buffer folder is setup on a quick disk.
  
  

• zebob

  I don't understand. I am reading a flat-file source and sorting it using the Sort tool. There are no keys. There is no database behind the data. I don't see any option in the sort tool for that.
  
  
  
• Why is sort so slow?