 |
 |
Archives of the TeradataForum
Message Posted: Wed, 17 Mar 2004 @ 22:01:37 GMT
| Subj: | | Re: Spool Space issues |
| |
| From: | | Ray, Edmond |
Rachel,
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
| |
