Tuesday, July 9, 2013

0 Tempdb usage: Temporary table and table variable

We can create temp table, table variable or event regular table in tempdb.

However, the table variable wont be able to take the advantages for caching . ( see detail here ).

In the example below, you can also see the table variable shows the wrong statistic in the exaction plan.

There are 3 DMV to help us monitor the temp db usage.

  • sys.dm_db_file_space_usage
  • sys.dm_db_session_space_usage
  • sys.dm_db_task_space_usage

 

Temporary table: Global ( ##) or session (#)

 

create table #sunny (c1 int, c2 char(7000) not null)
declare @x int
set @x = 0
while (@x < 100)
begin
insert into #sunny values (@x, 'sunny')
set @x = @x + 1
end
go
 
select count(*) from #sunny;

image

Table variable

 

declare @summer table ( c1 int,c2 char(7000) not null);
declare @x int;
set @x = 0;
while (@x < 100)
begin
insert into @summer values (@x,'summer');
set @x = @x + 1;
end;
 
select COUNT(*) from @summer;

 

image

Check the tempdb usage

 

select * from sys.dm_db_file_space_usage;
select * from sys.dm_db_session_space_usage;
image

0 comments:

Post a Comment

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates