Post date: Dec 13, 2011 7:50:40 PM
Standard configuration for our production SQL servers includes a dedicated drive for tempdb, SQL server's 'scratchpad' database. Our configuration follows Microsoft's recommended best practices.
With a complex query, DTA encountered the following SQL error:
Could not allocate space for object 'dbo.SORT temporary run storage: 150581025964032' in database 'tempdb' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
The issue was caused by a FROM clause sub-query doing three things:
wanted to use an index different than the outer query,
included an ORDER BY clause in the sub-select, and
was on a table with 10's of millions of rows (or more)
This essentially forced SQL to do an ORDER of the entire table in tempdb.
The fix is to have the outer query and sub-query use the same index.
Wrong version was:
SELECT ...
FROM Debt (NOLOCK)
INNER JOIN Ledger (nolock)
ON Ledger.LedgerID = ( ... )
INNER JOIN LedgerItem (nolock) -- this uses PK_LedgerItem
ON LedgerItem.LedgerItemID = (
SELECT Top 1 LI.LedgerItemID
FROM LedgerItem LI (nolock)
WHERE LI.LedgerID = Ledger.LedgerID -- this uses IX_LedgerItemByLedgerID
AND ...
ORDER BY Li.PostedDate DESC, Li.LedgerItemID DESC
)
to this:
SELECT ...
FROM Debt (NOLOCK)
INNER JOIN Ledger (nolock)
ON Ledger.LedgerID = ( ... )
INNER JOIN LedgerItem (nolock)
ON LedgerItem.LedgerID = Ledger.LedgerID -- this uses IX_LedgerItemByLedgerID
AND LedgerItem.LedgerItemID = (
SELECT Top 1 LI.LedgerItemID
FROM LedgerItem LI (nolock)
WHERE LI.LedgerID = Ledger.LedgerID -- this also uses IX_LedgerItemByLedgerID
AND ISNULL(LI.LedgerTemplateItemID,1) not in (55,56,57,175)
AND ...
ORDER BY Li.PostedDate DESC, Li.LedgerItemID DESC
)
WHERE ...