--Reference
http://blogs.msdn.com/sqlserverstorageengine/archive/2008/03/30/sql-server-table-variable-vs-local-temporary-table.aspx走进SQL Server 2005 tempdb数据库 (一)Working with tempdb in SQL Server 2005 SQL Server 2005 Performance Tuning性能调校(含光盘) 第五章 第二部分 --基础 每次重启都会删除,然后从model系统数据库重建 05以上版本默认大小为8MB 只能有一个文件组放置数据库文件,另一个文件组放置事务记录文件 由于不支持ACID的Durable,05以上版本tempdb事务记录不会放入redo tempdb存放的三种对象 内部对象:排序,hash join,hash aggregate,Instead of触发器,cursor结果,DBCC check, Service Broker,大型的数据,如:XML,text,image,varchar(max)等 版本存放区:事务的snapshot隔离级别或者read committed snapshot,多数据结果集multiple active result sets,在线生成索引,after 触发器 自定义对象:global和local temporary table,数据表变量等 查出tempdb内的对象和所占空间 也可在sys.all_objects和sys.objects里查看 exec sp_MSForEachTable 'begin try exec sp_SpaceUsed ''?'' end try begin catch end catch' 监控 sys.dm_db_file_space_usage 每次重启都会删除,然后从model系统数据库重建05以上版本默认大小为8MB只能有一个文件组放置数据库文件,另一个文件组放置事务记录文件由于不支持ACID的Durable,05以上版本tempdb事务记录不会放入redotempdb存放的三种对象内部对象: 排序,hash join,hash aggregate,Instead of触发器,cursor结果,DBCC check, Service Broker,大型的数据,如:XML,text,image,varchar(max)等版本存放区:事务的snapshot隔离级别或者read committed snapshot,多数据结果集multiple active result sets,在线生成索引,after 触发器自定义对象:global和local temporary table,数据表变量等查出tempdb内的对象和所占空间 也可在sys.all_objects和sys.objects里查看 exec sp_MSForEachTable ' begin try exec sp_SpaceUsed '' ? '' end try begin catch end catch ' --监控 代码列表 7：故意建立各种使用 tempdb 系统数据库空间的语法.sql create database d alter database d set read_committed_snapshot on use d -- 占用 tempdb 上用户自定对象的空间 create table ##t1(c1 int primary key identity ( 1 , 1 ),c2 nvarchar ( 10 ) default ' hello t1 ' ) create table ##t2(c1 int primary key identity ( 1 , 1 ),c2 int ,c3 nvarchar ( 10 ) default ' hello t2 ' ,c4 int default rand ( datepart (ms, getdate ())) * 10000 ) insert ##t1 default values set nocount on declare @i int set @i = 0 while @i < 20 begin insert ##t1(c2) select c2 from ##t1 set @i = @i + 1 end -- 为了要让随机数乱，所以逐条增加... while @i < 1000000 begin insert ##t2(c2) values ( @i ) set @i = @i + 1 end -- 要占用 tempdb 上大量的内部对象 select * from ##t1 join ##t2 on ##t1.c1 = ##t2.c4 order by c4 -- 使用记录版本空间 select top 1000 * into t1 from ##t1 begin tran update t1 set c2 = c2 + ' a '代码列表 8：监控 tempdb 系统数据库空间的使用.sql SELECT SUM (user_object_reserved_page_count) * 8 as [ 用户对象(kb) ] , SUM (internal_object_reserved_page_count) * 8 as [ 内部对象(kb) ] , SUM (version_store_reserved_page_count) * 8 as [ 纪录版本空间(kb) ] , SUM (unallocated_extent_page_count) * 8 as [ 可用空间(kb) ] , SUM (mixed_extent_page_count) * 8 as [ mixedextent(kb) ] FROM sys.dm_db_file_space_usage -- 或是在任务阶段或任务层级查看 tempdb 中的页面设置或取消设置活动 SELECT top 5 * FROM sys.dm_db_session_space_usage ORDER BY (user_objects_alloc_page_count + internal_objects_alloc_page_count) DESC SELECT top 5 * FROM sys.dm_db_task_space_usage ORDER BY (user_objects_alloc_page_count + internal_objects_alloc_page_count) DESC -- 移动Tempdb Alter Database tempdb Modify File (Name = ' tempdev ' ,FileName = ' C:\temp\temp.mdf ' ,size = 30 MB,FileGrowth = 10 MB) Alter Database tempdb Modify File (Name = ' templog ' ,FileName = ' C:\temp\temp.ldf ' ,size = 10 MB,FileGrowth = 10 MB) -- 增加Tempdb相关文件 Alter Database tempdb Add File (Name = ' tempdev2 ' ,FileName = ' C:\temp\temp2.mdf ' ,size = 30 MB,FileGrowth = 10 MB) Alter Database tempdb Modify File (Name = ' templog2 ' ,FileName = ' C:\temp\temp2.ldf ' ,size = 30 MB,FileGrowth = 10 MB) -- 以SQLCMD模式停止并且重新激活服务 !!NET STOP MSSQLSERVER / Y!!NET START MSSQLSERVER -- 可以在sys.master_files系统视图查看到文件位置
转载于:https://www.cnblogs.com/buro79xxd/archive/2010/04/08/1707120.html
