Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Wed, 17 Mar 2004 @ 22:01:37 GMT

  <Prev Next>   <<First <Prev Next> Last>>  

Subj:   Re: Spool Space issues
From:   Ray, Edmond


Ulrich is mistaken on several points. Spool space is never really assigned to any user. Not even at the system level. Normally NCR recommends that you create a database to reserve space for spool on every system. Spool space comes from all available space on the system at the time a query is issued. Each userid is assigned a spool limit, which is the maximum amount of spool that userid can use at any point in time, no matter how many sessions are running under that userid. Because Teradata is an MPP system that spool limit is divided among all the Amps. So if you have a system with 40 Amps and your spool limit is 40 GB you will be limited to approximately 1GB per amp. All user are also limited by how much unused space is available on each Amp. You may have a limit of 10GB on each Amp but if the load is unbalanced one Amp may only have 1GB of unused space for spooling while the others have 20GB. This is where skewing can cause sessions to run out of spool space. To solve this collect statistics on the queried columns, and indexes, check you PIs and SIs against what is being queried.

Also spool is not doled out evenly amongst active sessions, it is first come first serve. If the space is available and it does not exceed any of the limits listed above the system will grant it to the first session asking for it.

Finally if you have queries that vary widely in response time look at your data and check for variable skewing in the queried columns. For instance, if your query always runs against yesterday's data, maybe on odd days you get 10 times more rows with a certain value in column x, and when you get that value it joins with 90% of the rows in a second table with 2 billion rows. Walk the query through the data model and figure out what you think it should do, then look at the explain and see what it really does.

Edmond Ray
Senior Data Warehouse Consultant
Special Programs, NCR Government Systems

  <Prev Next>   <<First <Prev Next> Last>>  
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 23 Jun 2019