Temporary Tables in SQL Server

01 September 2011

by Phil Factor

Temporary tables are used by every DB developer, but they're not likely to be too adventurous with their use, or exploit all their advantages. They can improve your code's performance and maintainability, but can be the source of grief to both developer and DBA if things go wrong and a process grinds away inexorably slowly. We asked Phil for advice, thinking that it would be a simple explanation.

Temporary tables are just that. They are used most often to provide workspace for the intermediate results when processing data within a batch or procedure. They are also used to pass a table from a table-valued function, to pass table-based data between stored procedures or, more recently in the form of Table-valued parameters, to send whole read-only tables from applications  to SQL Server routines, or pass read-only  temporary tables as parameters. Once finished with their use, they are discarded automatically.

Temporary tables come in different flavours including, amongst others, local temporary tables (starting with #), global temporary tables (starting with ##), persistent temporary tables (prefixed by TempDB..), and table variables.(starting with (@)
Before we get too deep into the technology, I’d advise that you should use table variables where possible. They’re easy, and SQL Server does the work for you. They also tend to cause fewer problems to a hard-working OLTP system. Just occasionally, you may need to fine-tune them to get good performance from them in joins, but I'll explain that in a moment, However, if you are doing more complex processing on temporary data or likely to use more than reasonably small amounts of data in them, then  local temporary tables are likely to be  a better choice.

Table Variables

Table variables are used within the scope of the routine or batch within which they are defined, and were originally created to make table-valued functions possible. However, they are good for many of the uses that the traditional temporary table was put to. They behave like other variables in their scoping rules. Once out of scope, they are disposed of. These are much easier to work with, and pretty secure, and they trigger fewer recompiles in the routines where they’re used than if you were to use temporary tables. Table variables require less locking resources as they are 'private' to the process that created them. Transaction rollbacks do not affect them because table variables have limited scope and are not part of the persistent database, so they are handy for creating or storing data that ought to survive roll backs such as log entries. The downside of table variables is that they are often disposed of before you can investigate their contents for debugging, or use them to try out different SQL expressions interactively.
If your application is conservative and your data volumes light you’ll never want anything else. However, you can hit problems. One difficulty is that table variables can only be referenced in their local scope, so you cannot process them using dynamic SQL as you might with a temporary table or table-valued parameter. This is because you can’t refer an externally-defined table variable within dynamic SQL that you then execute via the EXEC statement or the sp_ExecuteSQL stored procedure because the dynamic SQL is executed outside the scope of the table variable. You can, of course, create, and then use, the table variable inside the dynamic SQL because  the table variable would be in scope. However, once the dynamic SQL is run, there would be no table variable
There are a few anomalies to be aware of too. You can’t, for example, change the table definition after the initial DECLARE statement. In SQL Server 2000,  a table variable can’t be the destination of a SELECT INTO statement or a INSERT EXEC (now fixed); You can’t call user-defined functions from CHECK constraints, DEFAULT values, and computed columns in the table variable. The only constraints that you're allowed beyond CHECK constraints are PRIMARY KEY, UNIQUE KEY, and NULL / NOT NULL
The trickiest problems, though, come with increasing size of the tables, because you can’t declare an index explicitly and distribution statistics aren’t maintained on them. The Query Optimiser assumes that there is only one row in the table. You also cannot generate parallel query plans for a SQL expression that is modifying the table's contents. To partially get around the index restriction, you can use constraints to do the same thing. Most essential is the Primary Key constraint which allows you to impose a clustered index, but unique constraints are useful for performance. The Query optimiser will happily use them if they are around. The biggest problem with table variables is that statistics aren’t maintained on the columns. This means that the query optimiser has to make a guess as to the size and distribution of the data and if it gets it wrong, then you’re going to see poor performance on joins: If this happens, there is little you can do other than to revert to using classic local temporary tables. One thing you can try is to add option (recompile) to the statement that involves the table variable joining with other tables. .By doing this, SQL Server will be able to detect number of rows at recompile because the rows will have already been populated. This doesn't entirely solve the problem since the optimiser still has no distribution statistics and can, usually where the distribution is skewed, produce a bad plan. In this demo, the join was reduced in time by three quarters simply by adding the OPTION (RECOMPILE)

SET nocount ON

DECLARE @FirstTable TABLE (RandomInteger INT)
DECLARE @SecondTable TABLE (RandomInteger INT)
DECLARE @WhenWeStarted DATETIME
DECLARE @ii INT

BEGIN TRANSACTION
SET @ii = 0
WHILE @ii < 100000
  BEGIN
    INSERT  INTO @FirstTable
    VALUES  (RAND() * 10000)
    SET @ii = @ii + 1
  END
SET @ii = 0
WHILE @ii < 100000
  BEGIN
    INSERT  INTO @SecondTable
    VALUES  (RAND() * 10000)
    SET @ii = @ii + 1
  END
COMMIT TRANSACTION
SELECT  @WhenWeStarted = GETDATE()
SET STATISTICS PROFILE ON
SELECT  COUNT(*)
FROM    @FirstTable first
        INNER JOIN @SecondTable second
        ON first.RandomInteger = second.RandomInteger OPTION (RECOMPILE)
  -- 153Ms  as opposed to 653Ms without the hint
SET STATISTICS PROFILE OFF
SELECT  'That took '
    + CONVERT(VARCHAR(8), DATEDIFF(ms, @WhenWeStarted, GETDATE()))
    + ' ms'
go

Now if you can make what goes into the tables unique, you can then use a primary key constraint on these tables. This allowed the optimiser to use a clustered index seek instead of a table scan and the execution time was too rapid to measure
Start with table variables, but drop back to using local temporary tables if you hit performance problems. Some people are bold enough to give advice in terms of the number of rows in a table, and I've seen 100 or 1000 offered as a maximum; but  I've seen far larger table variables perform perfectly satisfactorily over time, and far smaller ones give trouble. However, in smaller tables, the trouble is less detectable!

Table-Valued Parameters

The Table-Valued Parameter (TVP) is a special type of  table variable that extends its use. When table variables are passed as parameters, the table is materialized in the TempDB system database as a table variable and passed by reference,  a pointer to the table in the TempDB.
Table-valued parameters have been  used since SQL Server 2008 to send several rows of data to a Transact-SQL routine or to a batch via sp_ExecuteSQL.. Their particular value to the programmer is that they can be used  within TSQL code as well as in the client application, so they are good for sending client tables to the server.  From TSQL, you can declare table-valued variables, insert data into them, and  pass these variables as table-valued parameters to stored procedures and functions.Their more general usefulness is limited by the fact that they are only passed as  read-only. You can't do UPDATE, DELETE, or INSERT statements on a table-valued parameter in the body of a routine.
You need to create a User-Defined Table Type and define a table structure to use them. Here is a simple example of their use in TSQL

/* First you need to create a table type. */
CREATE TYPE Names AS TABLE
(Name VARCHAR(10)) ;
GO

/* Next, Create a procedure to receive data for the table-valued parameter, the table of names and select one item from the table*/
CREATE PROCEDURE ChooseAName
  @CandidateNames Names READONLY
AS
DECLARE @candidates TABLE (NAME VARCHAR(10),
                           theOrder UNIQUEIDENTIFIER)
INSERT  INTO @candidates (name, theorder)
        SELECT  name, NEWID()
        FROM    @CandidateNames
SELECT TOP 1
        NAME
FROM    @Candidates
ORDER BY theOrder
GO

/* Declare a variable that references the type for our list of cows. */
DECLARE @MyFavouriteCowName AS Names ;

/* Add data to the table variable. */
INSERT  INTO @MyFavouriteCowName (Name)
 SELECT 'Bossy' UNION SELECT 'Bessy' UNION SELECT 'petal' UNION SELECT 'Daisy' UNION SELECT 'Lulu' UNION SELECT 'Buttercup' UNION SELECT 'Bertha' UNION SELECT 'Bubba' UNION SELECT 'Beauregard' UNION SELECT 'Brunhilde' UNION SELECT 'Lore' UNION SELECT 'Lotte' UNION SELECT 'Rosa' UNION SELECT 'Thilde' UNION SELECT 'Lisa' UNION SELECT 'Peppo' UNION SELECT 'Maxi' UNION SELECT 'Moriz' UNION SELECT 'Marla'

/* Pass the table with the list of traditional nemes of cows to the stored procedure. */
EXEC chooseAName @MyFavouriteCowName
GO

As with Table Variables, the table-valued parameter ceases to exist once it is out of scope but the type definition remains until it is explicitly  dropped. Like Table Variables they do not acquire locks when the data is being populated from a client, and  statistics aren't maintained  on columns of table-valued parameters. You cannot use a table-valued parameter as target of a SELECT INTO or INSERT EXEC statement. As you'd expect, a table-valued parameter can be in the FROM clause of SELECT INTO or in the INSERT EXEC string or stored-procedure.
The TVP solves the common problem of wanting to pass a local variable to dynamic SQL that is then executed  by a sp_ExecuteSQL. It is poorly documented by Microsoft, so I'll show you a worked example to get you started

DECLARE @SeaAreas TABLE (NAME Varchar(20))
INSERT INTO @SeaAreas (name)
SELECT 'Viking' UNION SELECT 'North Utsire' UNION SELECT 'South Utsire' UNION SELECT 'Forties' UNION SELECT 'Cromarty' UNION SELECT 'Forth' UNION SELECT 'Tyne' UNION SELECT 'Dogger' UNION SELECT 'Fisher' UNION SELECT 'German Bight' UNION SELECT 'Humber' UNION SELECT 'Thames' UNION SELECT 'Dover' UNION SELECT 'Wight' UNION SELECT 'Portland' UNION SELECT 'Plymouth' UNION SELECT 'Biscay' UNION SELECT 'Trafalgar' UNION SELECT 'Finisterre' UNION SELECT 'Sole' UNION SELECT 'Lundy' UNION SELECT 'Fastnet' UNION SELECT 'Irish Sea' UNION SELECT 'Shannon' UNION SELECT 'Rockall' UNION SELECT 'Malin' UNION SELECT 'Hebrides' UNION SELECT 'Bailey' UNION SELECT 'Fair Isle' UNION SELECT 'Faeroes' UNION SELECT 'Southeast Iceland'

CREATE TYPE seanames AS TABLE (Name VARCHAR(20)) ;
DECLARE @SeaAreaNames AS SeaNames ;
INSERT  INTO @SeaAreaNames (name)
        SELECT  *
        FROM    @SeaAreas
EXEC sp_executesql N'SELECT * FROM @MySeaAreas',
  N'@MySeaAreas [dbo].[seanames] READONLY', @MySeaAreas = @SeaAreaNames

Before we move on to describe the more traditional  temporary tables and their use, we'll need to delve into the place where temporary tables are held. TempDB.

TempDB

Temporary tables and table variables are created in the TempDB database, which is really just another database with simple recovery: With TempDB, only sufficient 'minimal'  logging is done to allow rollback, and other ACID niceties. The special difference of TempDB is that any objects such as tables are cleared out on startup. Because TempDB always uses the simple recovery model, the completed transaction are cleared from the log log on the next TempDB checkpoint, and only the live transactions are retained. This all means that temporary tables behave like any other sort of base table in that they are logged, and stored just like them. In practice, temporary tables are likely to remain cached in memory, but only if they are frequently-used: same as with a base table. TempDB operates a system called temporary object reuse, which will cache a portion of the temporary objects with the plan, if there is sufficient memory. This may account for the legend that temporary objects exist only in memory. The truth as ever is 'it depends...'.
A lot of other things go on in TempDB: The database engine can use it  for placing work tables for DBCC checks, for creating or rebuilding  indexes, cursors,  for example. Intermediate tables in queries described as 'hashes', 'sorts' and 'spools' are materialized in TempDB, for example, along with those required for several 'physical' operations in executing SQL Statements. It is also used as a version store for Snapshot isolation, Multiple Active Results Sets (MARS), triggers and online-index-build.
Because temporary tables are stored just like base tables, there are one or two things you need to be wary of. You must, for example, have CREATE TABLE permission in TempDB in order to create a normal table. To save you the trouble, this is assigned by default to the DBO (db owner) role, but you may need to do it explicitly for users who aren’t assigned the DBO role. All users have permissions to create local or global temporary tables in TempDB because this is assigned to them via the GUEST  user security context.
The classic temporary table comes in two flavors, the Global, or shareable, temporary table, prefixed by ‘##’, and the local temporary table, whose name is prefixed with ‘#’.The local temporary tables are less like normal tables than the Global temporary tables: You cannot create views on them, or associate triggers with them. It is a bit tricky to work out which process, session or procedure created them. We’ll give you a bit of help with that later. Most importantly, they are more secure than a global temporary table as only the owning process can see it.
Another oddity of the local temporary table (and the local temporary stored procedure)  is that it has a different name in the metadata to the one you give it in your routine or batch. If the same routine is executed simultaneously by several processes, the Database Engine needs to be able to distinguish between the identically-named local temporary tables created by the different processes. It does this by adding a numeric string to each local temporary table name left-padded by underscore characters. Although you specify the short name such as #MyTempTable, what is actually stored in TempDB is made up of the table name specified in the CREATE TABLE statement and the suffix. Because of this suffix, local temporary table names must be 116 characters or less.
If you’re interested in seeing what is going on, you can view the tables in TempDB just the same way you would any other table. You can even use sp_help work on temporary tables only if you invoke them from TempDB.

USE TempDB
go
execute sp_Help #mytemp 

or you can find them in the system views of TempDB without swithching databases.

SELECT name, create_date FROM TempDB.sys.tables WHERE name LIKE '#%'

Or the Information Schema

SELECT * FROM TempDB.information_schema.tables

Even better, you can find out what process, and user, is holding on to enormous temporary tables in TempDB and refusing to give up the space

-- Find out who created the temporary table,and when; the culprit and SPId.
SELECT DISTINCT te.name, t.Name, t.create_date, SPID, SessionLoginName
FROM ::fn_trace_gettable(( SELECT LEFT(path, LEN(path)-CHARINDEX('\', REVERSE(path))) + '\Log.trc'
                            FROM    sys.traces -- read all five trace files
                            WHERE   is_default = 1
                          ), DEFAULT) trace
INNER JOIN sys.trace_events te on trace.EventClass = te.trace_event_id
INNER JOIN TempDB.sys.tables  AS t ON trace.ObjectID = t.OBJECT_ID
WHERE trace.DatabaseName = 'TempDB'
  AND t.Name LIKE '#%'
  AND te.name = 'Object:Created'
  AND DATEPART(dy,t.create_date)= DATEPART(Dy,trace.StartTime)
  AND ABS(DATEDIFF(Ms,t.create_date,trace.StartTime))<50 --sometimes slightly out
ORDER BY t.create_date

You cannot use user-defined datatypes in temporary tables unless the datatypes exist in TempDB; that is, unless the datatypes have been explicitly created

User Tables in TempDB

In normal use, you will create temporary tables, or table variables without thinking too deeply about it. However, it is interesting, though, that TempDB is there for any sort of sandbox activity. You can create ordinary base tables, views, or anything else you want. You can create schemas, stored procedures and so on. You’re unlikely to want to do this, but it is certainly possible since TempDB is just another database. I've just had to restart my development SQL Server after proving this to myself by installing AdventureWorks onto it. This means that it is possible to create a base table in TempDB, a sort of ..er... temporary permanent table. Unlike the global temporary table, you’d have to do all your own housekeeping on it: you’re on your own. The same is true of routines. The advantage of doing this is that any processing that you do uses TempDB’s simple recovery so that, if you fail to mop up, SQL Server acts as mother on the next startup: though this could be a very long time. The next stage is to have what I call a ‘persistent temporary’ table. In this table, the data itself is volatile when the server restarts, but the table itself persists. Probably the most common way to create a persistent Temporary table is to recreate on startup a global temporary table. This can be done automatically when all databases are recovered and the "Recovery is completed" message is logged. Even though this is a ‘global temporary’, it isn’t deleted when all connections using it have disappeared, because the process that runs it never disappears. Arguably, it is better to create this kind of  work table in the database that uses it, though, if you are using full recovery, the temporary work will remain in the log. You can, of course, just create an ordinary table in TempDB. You can create these ‘persistent’ tables on startup by defining a stored procedure in master that creates the global temporary table

USE master
go
CREATE PROCEDURE createMyGlobalTables  AS
   CREATE TABLE ##globalTemporary1
      (-- Blah blah (insert DDL here)
   CREATE TABLE ##globalTemporary2
      (-- Blah blah (insert DDL here)
--and so on….
   CREATE TABLE ##globalTemporaryn
      (-- Blah blah (insert DDL here)

go
EXEC sp_procoption 'createMyGlobalTables', 'startup', 'true'
-- A stored procedure that is set to autoexecution runs every time an instance of SQL Server is started

Why use this sort of hybrid table? There are, for example, a number of techniques for passing tables between procedures via ‘persistent’ tables in a multiprocess-safe way, so as to do a series of processing to the data. These are referred to a Process-keyed tables (see ‘How to Share Data Between Stored Procedures: Process-Keyed table by  Erland Sommarskog). They will initially raise the eyebrows of any seasoned DBA but they are an effective and safe solution to a perennial problem, when they are done properly.
As well as temporary tables, there are also a number of  table types that aren’t directly derived from base tables, such as ‘fake’ tables and derived tables: some of these are so fleeting that they are best thought of as ephemeral rather than temporary. The CTE uses ephemeral tables that are ‘inline’ or ‘derived’ and aren’t materialised. BOL refers to them as ‘temporary named result sets’. They exist only within the scope of the expression. In a CTE, they have the advantage over derived tables in that they can be accessed more than once.

Local Temporary Table

With Local temporary table (names that begin with #), what goes on under the hood is surprisingly similar to table variables. As with Table Variables, Local Temporary tables are private to the process that created it. They cannot therefore be used in views and you cannot associate triggers with them.
They are handier than table variables if you like using SELECT INTO to create them, but I'm slightly wary about using SELECT INTO in a system that is likely to require modification, I'd much rather create my temporary tables explicitly, along with all the constraints that are needed.
 You cannot easily tell which session or procedure has created these tables. This is because, if the same stored procedure is executed simultaneously by several processes, the Database Engine needs to be able to distinguish the same tables created by the different processes. The Database Engine does this by internally appending a left-padded numeric suffix to each local temporary table name. The full name of a temporary table as stored in the sys.objects view in TempDB is made up of the table name specified in the CREATE TABLE statement and the system-generated numeric suffix. To allow for the suffix, the table name specified for a local temporary name must be less than 116 characters.
You get housekeeping with Local Temporary tables; they are automatically dropped when they go out of scope, unless explicitly dropped by using DROP TABLE. Their scope is more generous than a table Variable so you don't have problems referencing them within batches or in dynamic SQL. Local temporary tables are dropped automatically at the end of the current session or procedure. Dropping it at the end of the procedure that created it can cause head-scratching: a local temporary table that is created within a stored procedure or session is dropped when it is finished so it cannot be referenced by the process that called the stored procedure that created the table. It can, however, be referenced by any nested stored procedures executed by the stored procedure that created the table. If the nested procedure references a temporary table and two temporary tables with the same name exist at that time, which table is the query is resolved against?
As a curiosity, you can also create Local Temporary Stored Procedures with the same scope and lifetime as a local temporary table. You can't do the same for other routines.

Global Temporary Tables.

Like Local temporary tables, Global temporary tables (they begin with ##) are automatically dropped when the session that created the table ends: However, because global tables aren’t private to the process that created it, they must persist thereafter until the last Transact-SQL statement that was actively referencing the table at the time when the creating session ended has finished executing and the locks are dropped. Anyone who has access to TempDB at the time these Global Temporary tables exist can directly query, modify or drop these temporary objects.
You can associate rules, defaults, and indexes with temporary tables, but you cannot create views on temporary tables or associate triggers with them. You can use a user-defined datatype when creating a temporary table only if the datatype exists in TempDB
Stored procedures can reference temporary tables that are created during the current session. Within a stored procedure, you cannot create a temporary table, drop it, and then create a new temporary table with the same name.
Although this works….

CREATE table #Color(
Color varchar(10) PRIMARY key)
INSERT INTO #color SELECT 'Red' UNION SELECT 'White'
 UNION SELECT 'green'UNION SELECT'Yellow'UNION SELECT'blue'
DROP TABLE #color
go
CREATE table #Color(
Color varchar(10) PRIMARY key)
INSERT INTO #color SELECT 'Red' UNION SELECT 'White'
 UNION SELECT 'green'UNION SELECT'Yellow'UNION SELECT'blue'
DROP TABLE #color

…this doesn’t

CREATE PROCEDURE MisbehaviourWithTemporaryTables  AS
 CREATE table #Color(
Color varchar(10) PRIMARY key)
INSERT INTO #color SELECT 'Red' UNION SELECT 'White'
 UNION SELECT 'green'UNION SELECT'Yellow'UNION SELECT'blue'
DROP TABLE #color
CREATE table #Color(
Color varchar(10) PRIMARY key)
INSERT INTO #color SELECT 'Red' UNION SELECT 'White'
 UNION SELECT 'green'UNION SELECT'Yellow'UNION SELECT'blue'
DROP TABLE #color
go

You can, by using local temporary tables, unintentionally  force recompilation on the stored procedure every time it is used. This isn’t good because the stored procedure is unlikely to perform well. To avoid recompilation, avoid referring to a temporary table created in a calling or called stored procedure: If you can’t do so, then put the reference in a string that is then executed using the EXECUTE statement or sp_ExecuteSQL stored procedure. Also, make sure that the temporary table is created in the stored procedure or trigger before it is referenced and dropped after these references. Don’t create a temporary table within a control-of-flow statement such as IF... ELSE or WHILE.
You are allowed to create Global Temporary Stored Procedures, but I've yet to find a use for them. Global temporary functions aren't permitted.

Conclusions

In any shared playground, be very careful how you swing that bat. You'll have realized, whilst reading this, that a lot of activity goes on in TempDB, and you can cause havoc to the whole SQL Server by using long-running processes that fill temporary tables, whatever type they are, with unnecessary quantities of data. In fact, I've given you clues in this article how to really, really, upset your DBA by inconsiderate use of that precious shared resource, the TempDB. (In the old days before S2005, using SELECT INTO with a huge table was the great V-weapon (Vergeltungswaffe)
I'm always wary of providing over-generalized advice, but I always prefer my databases to use Table Variables, and TVPs wherever possible,  They require less resource, and you're less likely to hold onto them when you're finished with them.  I like to use them to the max, with column and table checks and constraints. You may find times when they run out of steam, especially when table sizes get larger. In cases like this, or where it isn't practical to use table variables because of their restricted scope, then I'll use local temporary tables. It takes a lot of pursed lips and shaking of heads before I'll agree to a global temporary table or persistent temporary table. They have a few valid and perfectly reasonable uses, but they place reliance on the programmer to do the necessary housekeeping
Always bear in mind that misuse of temporary tables, such as unnecessarily large, or too long-lived,  can have effects on other processes, even on other databases on the server. You are, after all, using a shared resource, and you wouldn't treat your bathroom  that way would you?

Source: https://www.simple-talk.com

How to Tell if You Need More Tempdb Files

You may have read that you need to have more than one data file in SQL Server’s tempdb. This can happen even if you’re using blazing fast storage. If you create a lot of tiny objects in tempdb you may hit a bottleneck on special pages that SQL Server uses internally to allocate all those objects. For certain workloads, adding more tempdb files speeds up SQL Server.
Most database administrators aren’t sure how to tell when they need to add more tempdb files. Here’s how you can find out for yourself.

Tempdb stress: my virtual machine setup

I stressed out my tempdb on a virtual machine with 6GB of memory, 4 virtual CPUs, Windows Server 2012 R2 and SQL Server 2014. The virtual machine is backed by solid state storage. My tempdb was configured with a single data file.

How I ran my stress test against tempdb

I used ostress.exe. This command line utility lets you easily run a query multiple times across many threads. You can get ostress.exe for free by downloading the RML utilities.
Here’s the ostress.exe command that I called:
[code]
ostress.exe -E -d"tempdb" -Q"exec dbo.tempdbstress" -n5 -r300 -b -q
[/code]
The dbo.tempdbstress procedure looks like this:

1. use tempdb;
2. GO
3. CREATE PROCEDURE dbo.tempdbstress
4. AS
5. SET NOCOUNT ON;
6. SELECT TOP(5000) a.name, replicate(a.status,4000) as col2
7. into #t1
8. FROM master..spt_values a
9. CROSS JOIN master..spt_values b OPTION (MAXDOP 1);
10. GO

Finding GAM and PFS contention in tempdb with sp_AskBrent® and Wait Stats

While this is running, look at waits in two ways. In one session, take a sample of waits with sp_AskBrent®:

1. exec sp_AskBrent @ExpertMode=1, @Seconds=10;
2. GO

I see PAGELATCH_UP, PAGELATCH_SH and PAGELATCH_EX waits:

I also have a lot of just plain old CPU wait (the SOS_SCHEDULER_YIELD), because I’m bashing at only 4 virtual CPUs (and I’m running ostress.exe on the same VM as SQL Server).

Finding GAM and PFS contention in tempdb with sp_whoisactive

You can see this in another way: using Adam Machanic‘s free sp_whoisactive tool:

1. exec sp_whoisactive;
2. GO

This lets you see the contention if you catch it at the right instant. Bonus: you can confirm exactly what type of pages its occuring on! I can see here that this is explicitly PFS waits. “PFS” means “page free space”: poor tempdb is hitting a hot spot on one of those special pages just recording how much space is available and where.

And here I can see the GAM waits. “GAM” means “Global Allocation Map”. That’s another one of those special pages that can get hot and slow things down in tempdb. It’s tracking where objects are allocated. (There’s also “SGAM” or “Shared Global Allocation Map” pages that you might see.)

Both of these types of waits can be alleviated if you add more data files to tempdb, because then you’ll get more PFS pages and GAM/SGAM pages across the files.

How to tell if you need more tempdb data files

First of all, if you just have one tempdb file, consider adding more files as preventive medicine. Don’t go crazy with this. The formula recommended by Microsoft in KB 2154845 to use one tempdb data file for each logical processor up to 8 processors is a good place to start.
Look at waits that have occurred since startup. Do you have PAGELATCH_UP or PAGELATCH_EX waits? (Note that these don’t have an “IO” in them. PAGEIOLATCH waits are different.)
If you do, that’s not necessarily 100% proof that the issue is in tempdb, but it’s a good indicator. If you don’t, well, it’s probably not a big issue.
If you do find these waits, dig deeper:

• Identify when the PAGELATCH_% waits are growing the most, and dig into those periods with sp_whoisactive. You can log sp_whoisactive results to a table
• Look at your top queries. The queries that are part of the issue might be suitable for caching in an application tier.

Tempdb file configuration reminders

Remember: this only applies to tempdb data files. You only need one tempdb log file.
Also remember that the tempdb data files will be used proportionally with their size. This means that you should keep the files equally sized. You can use trace flag 1117 to force all tempdb files to grow at the same time when one grows, but this will apply that behavior to every database on the instance (not just tempdb).
We recommend pre-growing out your tempdb files when you configure your SQL Server instance.
You can add tempdb files without restarting the SQL Server instance. However, we’ve seen everything from Anti-Virus attacking the new files to unexpected impacts from adding tempdb files. And if you need to shrink an existing file, it may not shrink gracefully when you run DBCC SHRINKFILE. For best results, deploy the configuration first to your pre-production environments, use a change request, and perform the change in a planned maintenance window.

SOURCE: http://www.brentozar.com 

Troubleshooting Insufficient Disk Space in tempdb

SQL Server 2008 R2

Other Versions

This topic provides procedures and recommendations to help you diagnose and troubleshoot problems caused by insufficient disk space in the tempdb database. Running out of disk space in tempdb can cause significant disruptions in the SQL Server production environment and can prohibit applications that are running from completing operations.

tempdb Space Requirements

The tempdb system database is a global resource that is available to all users that are connected to an instance of SQL Server. The tempdb database is used to store the following objects: user objects, internal objects, and version stores.
You can use the sys.dm_db_file_space_usage dynamic management view to monitor the disk space used by the user objects, internal objects, and version stores in the tempdb files. Additionally, to monitor the page allocation or deallocation activity in tempdb at the session or task level, you can use the sys.dm_db_session_space_usage and sys.dm_db_task_space_usage dynamic management views. These views can be used to identify large queries, temporary tables, or table variables that are using a large amount of tempdb disk space.

Diagnosing tempdb Disk Space Problems

The following table lists error messages that indicate insufficient disk space in the tempdb database. These errors can be found in the SQL Server error log, and may also be returned to any running application.

Error	Is raised when
1101 or 1105	Any session must allocate space in tempdb.
3959	The version store is full. This error usually appears after a 1105 or 1101 error in the log.
3967	The version store is forced to shrink because tempdb is full.
3958 or 3966	A transaction cannot find the required version record in tempdb.

tempdb disk space problems are also indicated when the database is set to autogrow, and the size of the database is quickly increasing.

Monitoring tempdb Disk Space

The following examples show how to determine the amount of space available in tempdb, and the space used by the version store and internal and user objects.

Determining the Amount of Free Space in tempdb

The following query returns the total number of free pages and total free space in megabytes (MB) available in all files in tempdb.

SELECT SUM(unallocated_extent_page_count) AS [free pages], 
(SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]
FROM sys.dm_db_file_space_usage;

Determining the Amount Space Used by the Version Store

The following query returns the total number of pages used by the version store and the total space in MB used by the version store in tempdb.

SELECT SUM(version_store_reserved_page_count) AS [version store pages used],
(SUM(version_store_reserved_page_count)*1.0/128) AS [version store space in MB]
FROM sys.dm_db_file_space_usage;

Determining the Longest Running Transaction

If the version store is using a lot of space in tempdb, you must determine what is the longest running transaction. Use this query to list the active transactions in order, by longest running transaction.

SELECT transaction_id
FROM sys.dm_tran_active_snapshot_database_transactions 
ORDER BY elapsed_time_seconds DESC;

A long running transaction that is not related to an online index operation requires a large version store. This version store keeps all the versions generated since the transaction started. Online index build transactions can take a long time to finish, but a separate version store dedicated to online index operations is used. Therefore, these operations do not prevent the versions from other transactions from being removed. For more information, see Row Versioning Resource Usage.

Determining the Amount of Space Used by Internal Objects

The following query returns the total number of pages used by internal objects and the total space in MB used by internal objects in tempdb.

SELECT SUM(internal_object_reserved_page_count) AS [internal object pages used],
(SUM(internal_object_reserved_page_count)*1.0/128) AS [internal object space in MB]
FROM sys.dm_db_file_space_usage;

Determining the Amount of Space Used by User Objects

The following query returns the total number of pages used by user objects and the total space used by user objects in tempdb.

SELECT SUM(user_object_reserved_page_count) AS [user object pages used],
(SUM(user_object_reserved_page_count)*1.0/128) AS [user object space in MB]
FROM sys.dm_db_file_space_usage;

Determining the Total Amount of Space (Free and Used)

The following query returns the total amount of disk space used by all files in tempdb.

SELECT SUM(size)*1.0/128 AS [size in MB]
FROM tempdb.sys.database_files

Monitoring Space Used by Queries

One of the most common types of tempdb space usage problems is associated with large queries that use a large amount of space. Generally, this space is used for internal objects, such as work tables or work files. Although monitoring the space used by internal objects tells you how much space is used, it does not directly identify the query that is using that space.
The following methods help identify the queries that are using the most space in tempdb. The first method examines batch-level data and is less data intensive than the second method. The second method can be used to identify the specific query, temp table, or table variable that is consuming the disk space, but more data must be collected to obtain the answer.

Method 1: Batch-Level Information

If the batch request contains just a few queries, and only one of them is a complex query, this is typically enough information to know just which batch is consuming the space instead of the specific query.
To continue with this method, a SQL Server Agent Job must be set up to poll from the sys.dm_db_session_space_usage and sys.dm_db_task_space_usage dynamic management views by using a polling interval in the range of few minutes. A polling interval of three minutes is used in the following example. You must poll from both views because sys.dm_db_session_space_usage does not include the allocation activity of the current active task. Comparing the difference between the pages allocated at two time intervals lets you calculate how many pages are allocated in between the intervals.
The following examples provide the queries that are required for the SQL Server Agent job.

A. Obtaining the space consumed by internal objects in all currently running tasks in each session.

The following example creates the view all_task_usage. When queried, the view returns the total space used by internal objects in all currently running tasks in tempdb.

CREATE VIEW all_task_usage
AS 
    SELECT session_id, 
      SUM(internal_objects_alloc_page_count) AS task_internal_objects_alloc_page_count,
      SUM(internal_objects_dealloc_page_count) AS task_internal_objects_dealloc_page_count 
    FROM sys.dm_db_task_space_usage 
    GROUP BY session_id;
GO

B. Obtaining the space consumed by internal objects in the current session for both running and completed tasks

The following example creates the view all_session_usage. When queried, the view returns the space used by all internal objects running and completed tasks in tempdb.

CREATE VIEW all_session_usage 
AS
    SELECT R1.session_id,
        R1.internal_objects_alloc_page_count 
        + R2.task_internal_objects_alloc_page_count AS session_internal_objects_alloc_page_count,
        R1.internal_objects_dealloc_page_count 
        + R2.task_internal_objects_dealloc_page_count AS session_internal_objects_dealloc_page_count
    FROM sys.dm_db_session_space_usage AS R1 
    INNER JOIN all_task_usage AS R2 ON R1.session_id = R2.session_id;
GO

Assume that when these views are queried at a three-minute interval, the result sets provide the following information.

• At 5:00 P.M., session 71 allocated 100 pages and deallocated 100 pages since the start of the session.
• At 5:03 P.M., session 71 allocated 20100 pages and deallocated 100 pages since the start of the session.

When you analyze this information, you can tell that between the two measurements: The session allocated 20,000 pages for internal objects, and did not deallocate any pages. This indicates a potential problem.

Note
As the database administrator, you may decide to poll more frequently than three minutes. However, if a query runs for less than three minutes, the query probably will not consume a significant amount of space in tempdb.

To determine the batch that is running during that time, use SQL Server Profiler to capture the RPC:Completed and SQL:BatchCompleted event classes.
An alternative to using SQL Server Profiler is to run DBCC INPUTBUFFER once every three minutes for all the sessions, as shown in the following example.

DECLARE @max int;
DECLARE @i int;
SELECT @max = max (session_id)
FROM sys.dm_exec_sessions
SET @i = 51
  WHILE @i <= @max BEGIN
         IF EXISTS (SELECT session_id FROM sys.dm_exec_sessions
                    WHERE session_id=@i)
         DBCC INPUTBUFFER (@i)
         SET @i=@i+1
         END;

Method 2: Query-Level Information

Sometimes just looking at the input buffer or the SQL Server Profiler event SQL:BatchCompleted does not always tell which query is using most of the disk space in tempdb. The following methods can be used to find this answer, but these methods require collecting more data than the procedures defined in Method 1.
To continue with this method, set up a SQL Server Agent Job job that polls from the sys.dm_db_task_space_usage dynamic management view. The polling interval should be short, once a minute, as compared to Method 1. This short interval is because sys.dm_db_task_space_usage does not return data if the query (task) is not currently running.
In the polling query, the view defined on the sys.dm_db_task_space_usage dynamic management view is joined with sys.dm_exec_requests to return the sql_handle, statement_start_offset, statement_end_offset, and plan_handle columns.

CREATE VIEW all_request_usage
AS 
  SELECT session_id, request_id, 
      SUM(internal_objects_alloc_page_count) AS request_internal_objects_alloc_page_count,
      SUM(internal_objects_dealloc_page_count)AS request_internal_objects_dealloc_page_count 
  FROM sys.dm_db_task_space_usage 
  GROUP BY session_id, request_id;
GO
CREATE VIEW all_query_usage
AS
  SELECT R1.session_id, R1.request_id, 
      R1.request_internal_objects_alloc_page_count, R1.request_internal_objects_dealloc_page_count,
      R2.sql_handle, R2.statement_start_offset, R2.statement_end_offset, R2.plan_handle
  FROM all_request_usage R1
  INNER JOIN sys.dm_exec_requests R2 ON R1.session_id = R2.session_id and R1.request_id = R2.request_id;
GO

If the query plan is in cache, you can retrieve the Transact-SQL text of the query and the query execution plan in XML showplan format at any time. To obtain the Transact-SQL text of the query that is executed, use the sql_handle value and the sys.dm_exec_sql_text dynamic management function. To obtain the query plan execution, use the plan_handle value and the sys.dm_exec_query_plan dynamic management function.

SELECT * FROM sys.dm_exec_sql_text(@sql_handle);
SELECT * FROM sys.dm_exec_query_plan(@plan_handle);

If the query plan is not in cache, you can use one of the following methods to obtain the Transact-SQL text of the query and query execution plan.

A. Using the polling method

Poll from the view all_query_usage, and run the following query to obtain the query text:

SELECT R1.sql_handle, R2.text 
FROM all_query_usage AS R1
OUTER APPLY sys.dm_exec_sql_text(R1.sql_handle) AS R2;

Because sql_handle should be unique for each unique batch, you do not have to save duplicate sql_handle entries.
To save the plan handle and XML plan, run the following query.

SELECT R1.plan_handle, R2.query_plan 
FROM all_query_usage AS R1
OUTER APPLY sys.dm_exec_query_plan(R1.plan_handle) AS R2;

B. Using SQL Server Profiler events

As an alternative to polling the sys.dm_exec_sql_text and sys.dm_exec_query_plan functions, you can use SQL Server Profiler events. There are profiler events that can be used to capture the query plan and query text that is generated. For example, Event 165 returns performance statistics for trace, SQL text, query plans, and query statistics.

Monitoring Space Used by Temp Tables and Table Variables

You can use an approach similar to polling queries for monitoring the space used by temp tables and temp variables. Applications that acquire a large amount of user data inside temp tables or temp variables can cause space use problems in tempdb. These tables or variables belong to the user objects. You can use the user_objects_alloc_page_count and user_objects_dealloc_page_count columns in the sys.dm_db_session_space_usage dynamic management view and follow the methods described earlier.

Monitoring Page Allocation and Deallocation by Session

The following table shows the results returned by the sys.dm_db_file_space_usage, sys.dm_db_session_space_usage, and sys.dm_db_task_space_usage dynamic management views for a specified session. Each row represents an allocation or deallocation activity in tempdb for a specified session. The activity is listed in the Event column. The remaining columns show the values that would be returned in the columns of the dynamic management views.
For this scenario, assume that the tempdb database starts with 872 pages in unallocated extents, and 100 pages in user-object reserved extents. The session allocates 10 pages for a user table, and then deallocates all of them. The first 8 pages are in mixed extent. The remaining 2 pages are in uniform extent.

Event	dm_db_file_space_usage unallocated_extent_page_count column	dm_db_file_space_usage user_object_reserved_page_count column	dm_db_session_space_usage and dm_db_task_space_usage user_object_alloc_page_count column	dm_db_session_space_usage and dm_db_task_space_usage user_object_dealloc_page_count column
Start	872	100	0	0
Allocate page 1 from existing mixed extent	872	100	1	0
Allocate pages 2 to 8: consuming one new mixed extent	864	80	8	0
Allocate page 9: consuming one new uniform extent	856	108	16	0
Allocate page 10 from existing uniform extent	856	108	16	0
Deallocate page 10 from existing uniform extent	856	108	16	0
Deallocate page 9, and the uniform extent	864	100	16	8
Deallocate page 8	864	100	16	9
Deallocate page 7 to 1, and deallocate on mixed extent

Troubleshooting Insufficient Disk Space in tempdb

SQL Server 2008 R2

Other Versions

This topic provides procedures and recommendations to help you diagnose and troubleshoot problems caused by insufficient disk space in the tempdb database. Running out of disk space in tempdb can cause significant disruptions in the SQL Server production environment and can prohibit applications that are running from completing operations.

tempdb Space Requirements

The tempdb system database is a global resource that is available to all users that are connected to an instance of SQL Server. The tempdb database is used to store the following objects: user objects, internal objects, and version stores.
You can use the sys.dm_db_file_space_usage dynamic management view to monitor the disk space used by the user objects, internal objects, and version stores in the tempdb files. Additionally, to monitor the page allocation or deallocation activity in tempdb at the session or task level, you can use the sys.dm_db_session_space_usage and sys.dm_db_task_space_usage dynamic management views. These views can be used to identify large queries, temporary tables, or table variables that are using a large amount of tempdb disk space.

Diagnosing tempdb Disk Space Problems

The following table lists error messages that indicate insufficient disk space in the tempdb database. These errors can be found in the SQL Server error log, and may also be returned to any running application.

Error	Is raised when
1101 or 1105	Any session must allocate space in tempdb.
3959	The version store is full. This error usually appears after a 1105 or 1101 error in the log.
3967	The version store is forced to shrink because tempdb is full.
3958 or 3966	A transaction cannot find the required version record in tempdb.

tempdb disk space problems are also indicated when the database is set to autogrow, and the size of the database is quickly increasing.

Monitoring tempdb Disk Space

The following examples show how to determine the amount of space available in tempdb, and the space used by the version store and internal and user objects.

Determining the Amount of Free Space in tempdb

The following query returns the total number of free pages and total free space in megabytes (MB) available in all files in tempdb.

SELECT SUM(unallocated_extent_page_count) AS [free pages], 
(SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]
FROM sys.dm_db_file_space_usage;

Determining the Amount Space Used by the Version Store

The following query returns the total number of pages used by the version store and the total space in MB used by the version store in tempdb.

SELECT SUM(version_store_reserved_page_count) AS [version store pages used],
(SUM(version_store_reserved_page_count)*1.0/128) AS [version store space in MB]
FROM sys.dm_db_file_space_usage;

Determining the Longest Running Transaction

If the version store is using a lot of space in tempdb, you must determine what is the longest running transaction. Use this query to list the active transactions in order, by longest running transaction.

SELECT transaction_id
FROM sys.dm_tran_active_snapshot_database_transactions 
ORDER BY elapsed_time_seconds DESC;

A long running transaction that is not related to an online index operation requires a large version store. This version store keeps all the versions generated since the transaction started. Online index build transactions can take a long time to finish, but a separate version store dedicated to online index operations is used. Therefore, these operations do not prevent the versions from other transactions from being removed. For more information, see Row Versioning Resource Usage.

Determining the Amount of Space Used by Internal Objects

The following query returns the total number of pages used by internal objects and the total space in MB used by internal objects in tempdb.

SELECT SUM(internal_object_reserved_page_count) AS [internal object pages used],
(SUM(internal_object_reserved_page_count)*1.0/128) AS [internal object space in MB]
FROM sys.dm_db_file_space_usage;

Determining the Amount of Space Used by User Objects

The following query returns the total number of pages used by user objects and the total space used by user objects in tempdb.

SELECT SUM(user_object_reserved_page_count) AS [user object pages used],
(SUM(user_object_reserved_page_count)*1.0/128) AS [user object space in MB]
FROM sys.dm_db_file_space_usage;

Determining the Total Amount of Space (Free and Used)

The following query returns the total amount of disk space used by all files in tempdb.

SELECT SUM(size)*1.0/128 AS [size in MB]
FROM tempdb.sys.database_files

Monitoring Space Used by Queries

One of the most common types of tempdb space usage problems is associated with large queries that use a large amount of space. Generally, this space is used for internal objects, such as work tables or work files. Although monitoring the space used by internal objects tells you how much space is used, it does not directly identify the query that is using that space.
The following methods help identify the queries that are using the most space in tempdb. The first method examines batch-level data and is less data intensive than the second method. The second method can be used to identify the specific query, temp table, or table variable that is consuming the disk space, but more data must be collected to obtain the answer.

Method 1: Batch-Level Information

If the batch request contains just a few queries, and only one of them is a complex query, this is typically enough information to know just which batch is consuming the space instead of the specific query.
To continue with this method, a SQL Server Agent Job must be set up to poll from the sys.dm_db_session_space_usage and sys.dm_db_task_space_usage dynamic management views by using a polling interval in the range of few minutes. A polling interval of three minutes is used in the following example. You must poll from both views because sys.dm_db_session_space_usage does not include the allocation activity of the current active task. Comparing the difference between the pages allocated at two time intervals lets you calculate how many pages are allocated in between the intervals.
The following examples provide the queries that are required for the SQL Server Agent job.

A. Obtaining the space consumed by internal objects in all currently running tasks in each session.

The following example creates the view all_task_usage. When queried, the view returns the total space used by internal objects in all currently running tasks in tempdb.

CREATE VIEW all_task_usage
AS 
    SELECT session_id, 
      SUM(internal_objects_alloc_page_count) AS task_internal_objects_alloc_page_count,
      SUM(internal_objects_dealloc_page_count) AS task_internal_objects_dealloc_page_count 
    FROM sys.dm_db_task_space_usage 
    GROUP BY session_id;
GO

B. Obtaining the space consumed by internal objects in the current session for both running and completed tasks

The following example creates the view all_session_usage. When queried, the view returns the space used by all internal objects running and completed tasks in tempdb.

CREATE VIEW all_session_usage 
AS
    SELECT R1.session_id,
        R1.internal_objects_alloc_page_count 
        + R2.task_internal_objects_alloc_page_count AS session_internal_objects_alloc_page_count,
        R1.internal_objects_dealloc_page_count 
        + R2.task_internal_objects_dealloc_page_count AS session_internal_objects_dealloc_page_count
    FROM sys.dm_db_session_space_usage AS R1 
    INNER JOIN all_task_usage AS R2 ON R1.session_id = R2.session_id;
GO

Assume that when these views are queried at a three-minute interval, the result sets provide the following information.

• At 5:00 P.M., session 71 allocated 100 pages and deallocated 100 pages since the start of the session.
• At 5:03 P.M., session 71 allocated 20100 pages and deallocated 100 pages since the start of the session.

When you analyze this information, you can tell that between the two measurements: The session allocated 20,000 pages for internal objects, and did not deallocate any pages. This indicates a potential problem.

Note
As the database administrator, you may decide to poll more frequently than three minutes. However, if a query runs for less than three minutes, the query probably will not consume a significant amount of space in tempdb.

To determine the batch that is running during that time, use SQL Server Profiler to capture the RPC:Completed and SQL:BatchCompleted event classes.
An alternative to using SQL Server Profiler is to run DBCC INPUTBUFFER once every three minutes for all the sessions, as shown in the following example.

DECLARE @max int;
DECLARE @i int;
SELECT @max = max (session_id)
FROM sys.dm_exec_sessions
SET @i = 51
  WHILE @i <= @max BEGIN
         IF EXISTS (SELECT session_id FROM sys.dm_exec_sessions
                    WHERE session_id=@i)
         DBCC INPUTBUFFER (@i)
         SET @i=@i+1
         END;

Method 2: Query-Level Information

Sometimes just looking at the input buffer or the SQL Server Profiler event SQL:BatchCompleted does not always tell which query is using most of the disk space in tempdb. The following methods can be used to find this answer, but these methods require collecting more data than the procedures defined in Method 1.
To continue with this method, set up a SQL Server Agent Job job that polls from the sys.dm_db_task_space_usage dynamic management view. The polling interval should be short, once a minute, as compared to Method 1. This short interval is because sys.dm_db_task_space_usage does not return data if the query (task) is not currently running.
In the polling query, the view defined on the sys.dm_db_task_space_usage dynamic management view is joined with sys.dm_exec_requests to return the sql_handle, statement_start_offset, statement_end_offset, and plan_handle columns.

CREATE VIEW all_request_usage
AS 
  SELECT session_id, request_id, 
      SUM(internal_objects_alloc_page_count) AS request_internal_objects_alloc_page_count,
      SUM(internal_objects_dealloc_page_count)AS request_internal_objects_dealloc_page_count 
  FROM sys.dm_db_task_space_usage 
  GROUP BY session_id, request_id;
GO
CREATE VIEW all_query_usage
AS
  SELECT R1.session_id, R1.request_id, 
      R1.request_internal_objects_alloc_page_count, R1.request_internal_objects_dealloc_page_count,
      R2.sql_handle, R2.statement_start_offset, R2.statement_end_offset, R2.plan_handle
  FROM all_request_usage R1
  INNER JOIN sys.dm_exec_requests R2 ON R1.session_id = R2.session_id and R1.request_id = R2.request_id;
GO

If the query plan is in cache, you can retrieve the Transact-SQL text of the query and the query execution plan in XML showplan format at any time. To obtain the Transact-SQL text of the query that is executed, use the sql_handle value and the sys.dm_exec_sql_text dynamic management function. To obtain the query plan execution, use the plan_handle value and the sys.dm_exec_query_plan dynamic management function.

SELECT * FROM sys.dm_exec_sql_text(@sql_handle);
SELECT * FROM sys.dm_exec_query_plan(@plan_handle);

If the query plan is not in cache, you can use one of the following methods to obtain the Transact-SQL text of the query and query execution plan.

A. Using the polling method

Poll from the view all_query_usage, and run the following query to obtain the query text:

SELECT R1.sql_handle, R2.text 
FROM all_query_usage AS R1
OUTER APPLY sys.dm_exec_sql_text(R1.sql_handle) AS R2;

Because sql_handle should be unique for each unique batch, you do not have to save duplicate sql_handle entries.
To save the plan handle and XML plan, run the following query.

SELECT R1.plan_handle, R2.query_plan 
FROM all_query_usage AS R1
OUTER APPLY sys.dm_exec_query_plan(R1.plan_handle) AS R2;

B. Using SQL Server Profiler events

As an alternative to polling the sys.dm_exec_sql_text and sys.dm_exec_query_plan functions, you can use SQL Server Profiler events. There are profiler events that can be used to capture the query plan and query text that is generated. For example, Event 165 returns performance statistics for trace, SQL text, query plans, and query statistics.

Monitoring Space Used by Temp Tables and Table Variables

You can use an approach similar to polling queries for monitoring the space used by temp tables and temp variables. Applications that acquire a large amount of user data inside temp tables or temp variables can cause space use problems in tempdb. These tables or variables belong to the user objects. You can use the user_objects_alloc_page_count and user_objects_dealloc_page_count columns in the sys.dm_db_session_space_usage dynamic management view and follow the methods described earlier.

Monitoring Page Allocation and Deallocation by Session

The following table shows the results returned by the sys.dm_db_file_space_usage, sys.dm_db_session_space_usage, and sys.dm_db_task_space_usage dynamic management views for a specified session. Each row represents an allocation or deallocation activity in tempdb for a specified session. The activity is listed in the Event column. The remaining columns show the values that would be returned in the columns of the dynamic management views.
For this scenario, assume that the tempdb database starts with 872 pages in unallocated extents, and 100 pages in user-object reserved extents. The session allocates 10 pages for a user table, and then deallocates all of them. The first 8 pages are in mixed extent. The remaining 2 pages are in uniform extent.

Event	dm_db_file_space_usage unallocated_extent_page_count column	dm_db_file_space_usage user_object_reserved_page_count column	dm_db_session_space_usage and dm_db_task_space_usage user_object_alloc_page_count column	dm_db_session_space_usage and dm_db_task_space_usage user_object_dealloc_page_count column
Start	872	100	0	0
Allocate page 1 from existing mixed extent	872	100	1	0
Allocate pages 2 to 8: consuming one new mixed extent	864	80	8	0
Allocate page 9: consuming one new uniform extent	856	108	16	0
Allocate page 10 from existing uniform extent	856	108	16	0
Deallocate page 10 from existing uniform extent	856	108	16	0
Deallocate page 9, and the uniform extent	864	100	16	8
Deallocate page 8	864	100	16	9
Deallocate page 7 to 1, and deallocate on mixed extent	872	100	16	16