MSSQL临时(tempdb)系统数据库
--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事务记录不会放入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'
--监控
- 代码列表 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系统视图查看到文件位置
作者:Buro#79xxd
出处:http://www.cnblogs.com/buro79xxd/
文章版权归本人所有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接,否则保留追究法律责任的权利。
【推荐】国内首个AI IDE,深度理解中文开发场景,立即下载体验Trae
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 如何编写易于单元测试的代码
· 10年+ .NET Coder 心语,封装的思维:从隐藏、稳定开始理解其本质意义
· .NET Core 中如何实现缓存的预热?
· 从 HTTP 原因短语缺失研究 HTTP/2 和 HTTP/3 的设计差异
· AI与.NET技术实操系列:向量存储与相似性搜索在 .NET 中的实现
· 10年+ .NET Coder 心语 ── 封装的思维:从隐藏、稳定开始理解其本质意义
· 地球OL攻略 —— 某应届生求职总结
· 提示词工程——AI应用必不可少的技术
· Open-Sora 2.0 重磅开源!
· 周边上新:园子的第一款马克杯温暖上架