Recently I received an e-mail alert that my collect statistics job had a spool space error (“Failure 2646 No more spool space in…”) for one of the statements it ran on one of my Teradata systems.
I went in to Viewpoint to look at the table in the “Space Usage” portlet, and found the skew was almost 100% for that table. There are many other ways to find how a table is skewed, but I find using Viewpoint easiest.
Then in SQL Assistant, I ran a group by count of the primary key, and found that the table had all NULL values in the primary key field for the 21 million plus records. At that point, I notified the developer that something was very wrong.
If at this point, the problem is still not clear, Teradata distributes the table data across its AMPs by the table’s primary key. If their is only one value for the primary key (in this case it being NULL), then all the data will be loaded into one amp. Spool space limits are also divided equally among all the AMPs for a user. So if all the 21 millions are loaded in one AMP, that user could possibly use all the available space trying to collect the statistics for that table.
Leave a Comment