Badly tuned Hibernate queries gobbling tempdb space

Due to some poorly performing HQL, our Sybase server (12.5) was running out of tempdb space.

Debugging the issue has been something of a problem because of the way Sybase manages this odd table.

What is tempdb?

"tempdb needs to be big enough to handle the following processes for every concurrent Adaptive Server user:

• Worktables for merge joins
• Worktables that are created for distinct, group by, and order by, for reformatting, and for the OR strategy, and for materializing some views and subqueries

.
.
.
”

From the Sybase manual.

Since our HQL was using the distinct keyword, we were filling up the tempdb table.

A naive way of analyzing which query was taking up all this space is offered by Sybase themselves here. Basically, the idea is to drop the distinct keyword from your query and select the results into a temp table. For example:

select city
into #tempcity
from authors

"You could use this query to create a temporary table, and then use sp_spaceused," they advise. That is:

use tempdb

go

sp_spaceused

What I had difficulty finding was that this is not the whole story. It will only tell you the size of the data retrieved and not the whole amount of tempdb used. For instance, you might only get 10 rows of just a single integer each if you performed a distinct select on an ID. But if the same query has lots of complicated joins, you're actually using up a lot more tempdb space than this as the DB builds worktables with which to process the join relationships.

Our DBA sent me this email:

"Definitely the number of pages read in a single scan of a worktable is an absolute indicator of the size of that table (since the worktable is created on the fly and has n pages that are scanned once). The dataserver will have a 2K page size which means the table is n x 2Kb in size."

So, one way to find the size of these queries is to enable resource limits and see at what point your query breaks. For instance:

sp_configure "allow resource limits"
go
sp_add_resource_limit sa, NULL, 'at all times', tempdb_space, 5, 2, 3, 1
go

This sp_add_resource_limit stored procedure is saying for user sa connecting via an undefined application (NULL), at all times, limit the space in tempdb to 5 pages. In the event of any limit being exceeded (2), abort the transaction (3) for that query (1). See here for the full details of how to execute this SP.

Then, execute your query and see if it fails because of the limited resource. You may see something like this:

com.sybase.jdbc3.jdbc.SybSQLException: Exceeded tempdb space limit of 5 pages.

SQLWarning: ErrorCode: 3618 SQLState: 01ZZZ --- Transaction has been aborted.
Query 1 of 1, Rows read: 0, Elapsed time (seconds) - Total: 0.214, SQL query: 0.214, Building output: 0

Even a query that returns no rows whatsoever but that does a lot of joining may hit this limit.

By the way, when you're finished, run:

sp_drop_resource_limit sa, NULL
go