0

While i have got some further insight from this post i am still struggling to get a fuller understanding of my recent use of variable tables and the associated tempdb growth.

I have recently been using variable tables within both stored procedures and table-valued functions. My use of variable tables v's local / global temporary tables is one area that might affect the larger challenge i'm experiencing.

Since using this type of temp table the tempdb has grown to around the 50+GB region and when inspecting the table using exec sp_spaceused @updateusage=true i am seeing: database_size: 51935.13MB unallocated_space: 51908.80MBand when checking the contents of the DB no temporary or system tables are present. For ref the tempdb.ldf is very small.

On inspecting my session usage using exec sp_who i am also seeing multiple rows which indicate sleeping which i suspect may be where we are experiencing issues with connections not closing properly.

From reading various posts and SO's the general consensus is not to attempt shrinking of the tempdb and associated files and in truth i'd prefer to resolve the underlying issue than move to more fragmented data storage.

Is there any advice on why my existing approach could be affecting the tempdb growth and if the use of local / global temporary tables would be more appropriate.

Regarding the tempdb it's self, while storage is cheap, i need to ensure this growth is contained so any advice on maintenance (Splitting the DB into multiple files, possible shrinking, moving the DB to a separate drive) ect would be appreciated.

Community
  • 1
  • 1
campervancoder
  • 1,579
  • 2
  • 11
  • 15
  • Is Sql server working 24/7 days or it can be down ? – M.Hassan Jul 27 '16 at 11:11
  • What is the "underlying issue" you're trying to solve? At some point in the past, you had usage that required TempDB to grow to the size it is today. If it's not continuing to grow, it's the size it needs to be until something comes along that requires more space. – alroc Jul 27 '16 at 16:00

1 Answers1

0

You can inspect objects inside tempdb database to know what is going on

The following code list objects in tempdb and sort asc based on creation date and compute duration in minute

use TempDb
go

SELECT 
 name ,object_id , SCHEMA_NAME(schema_id) obj_schema,
 type ,  type_desc,
 create_date ,  modify_date, 
 OBJECT_NAME(parent_object_id)  parent_obj ,
 DATEDIFF(hour,create_date , GETDATE()) duration_hour
FROM sys.objects  
where name  not like 'sys%'
order by create_date

The objects in tempDb is classified into three groups:

  • Internal objects
  • External objects
  • Version store

The following code show how much the tempdb disk space is allocated to each category 

SELECT 
SUM(user_object_reserved_page_count)/128.0 UserObjectsMB,
SUM(user_object_reserved_page_count) UserObjectPages_count  ,

SUM(version_store_reserved_page_count)/128.0 VersionStoreMB,
SUM(version_store_reserved_page_count) VersionStorePages_count, 

SUM(internal_object_reserved_page_count) InternalObjectPages_count, 
SUM(internal_object_reserved_page_count)/128.0 InternalObjectsMB,

SUM(unallocated_extent_page_count)/128.0 FreeSpaceMB,
SUM(unallocated_extent_page_count)  FreePages_count

FROM sys.dm_db_file_space_usage;

you can review: local-and-global-temporary-tables-in-sql-server

The size of tempdb should be big enough for daily and peak workload, to avoid the grow while the WorkLoad is running.

My advice, Don't shrink tempDb during startup or any other time, unless if necessary needed. Storage is cheap, and you can assign dedicated storage for tempDb (even SSD)

For more details:

Capacity Planning for tempdb

Optimizing tempdb Performance

M.Hassan
  • 10,282
  • 5
  • 65
  • 84