November 21, 2014 at 9:40 am
Hi
We are experiencing performance related issues and I was wondering if anybody can help me interpret the results of sp_whoisactive
Basically it returns that a query has been running for 1hr.30mins, wait info = NULL, CPU = 1,270,125 and tempdb_allocations = 43,631, TempDB_Current = 6,150
We have 1 Tempdb file on system with 4 Virtual CPU's.
What does the tempdb_allocations = 43,631 actually mean ?
If this is a bit vague I apologise - more info can be provided if required ?
November 21, 2014 at 5:42 pm
http://lmgtfy.com/?q=sp_whoisactive+tempdb_allocations
The most confusing of these columns are those related to tempdb. Each of the columns reports a number of 8 KB pages. The [tempdb_allocations] column is collected directly from the tempdb-related DMVs, and indicates how many pages were allocated in tempdb due to temporary tables, LOB types, spools, or other consumers. The [tempdb_current] column is computed by subtracting the deallocated pages information reported by the tempdb DMVs from the number of allocations. Seeing a high number of allocations with a small amount of current pages means that your query may be slamming tempdb, but is not causing it to grow. Seeing a large number of current pages means that your query may be responsible for all of those auto-grows you keep noticing.
November 22, 2014 at 8:23 am
To really help out, the execution plan and the query would be best.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply