临时库tempdb如何优化
临时库tempdb如何优化 
临时库tempdb如何优化
经常出现这样 临时库?tempdb?
[解决办法]
tempdb是否初始化过小？增长幅度过大？是否放在磁盘不快的地方？存储过程里面的临时表是否过大？持续过久？是否用了行版本存储？这些都需要考虑，
[解决办法]
一般tempdb存在的问题，主要是空间管理的问题，比如：由于使用tempdb比较多，所以空间增长很快，另外，由于在tempdb上需要大量并发的创建临时表，所以一般的优化思路是：
1、首先，在tempdb中再创建多个文件，比如你可以一次创建5个问题，如果可以的话，放到不同的硬盘上，这样如果同时有多个建临时表的请求，可以分散到这个5个不同的文件中，因为在建临时表的时候，需要访问每个问题的sgam页、gam页等，否则有可能导致pageiolatch，也就是闩锁。
2、尽量设置这几个文件的大小相同，比如都是2G的大小，尽量关闭自动增长，而是手动提前给增长。
[解决办法]
接下来，因为上面设置的tempdb的大小，以及每个文件的大小，不一定能满足你的要求，所以要进行监控：
--1.从文件级看tempdb的整体使用情况，每个extent是64KB
dbcc showfilestats
--2.所有做过空间申请的session信息
Select --@i,
getdate() as Time,
'Tempdb' as DB,
max(FILE_ID) as '文件id',
SUM (user_object_reserved_page_count) as '用户对象保留的页数', ----包含已分配区中的未使用页数
SUM (internal_object_reserved_page_count) as '内部对象保留的页数', --包含已分配区中的未使用页数
SUM (version_store_reserved_page_count)*8 as '版本存储保留的页数',
SUM (unallocated_extent_page_count) as '未分配的区中包含的页数', --不包含已分配区中的未使用页数
SUM(mixed_extent_page_count) as '文件的已分配混合区中:已分配页和未分配页' --包含IAM页
--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'
From sys.dm_db_file_space_usage
Where database_id = 2
--3.能够反映当时tempdb空间的总体分配,申请空间的会话正在运行的语句
SELECT --@i,
t1.session_id,
t1.internal_objects_alloc_page_count,
t1.user_objects_alloc_page_count,
t1.internal_objects_dealloc_page_count ,
t1.user_objects_dealloc_page_count,
t.text
from sys.dm_db_session_space_usage t1 --反映每个session的累计空间申请
inner join sys.dm_exec_sessions as t2
on t1.session_id = t2.session_id
inner join sys.dm_exec_requests t3
on t2.session_id = t3.session_id
cross apply sys.dm_exec_sql_text(t3.sql_handle) t
where t1.internal_objects_alloc_page_count>0 or
t1.user_objects_alloc_page_count >0 or
t1.internal_objects_dealloc_page_count>0 or
t1.user_objects_dealloc_page_count>0
--4.返回tempdb中页分配和释放活动，只有当任务正在运行时，sys.dm_db_task_space_usage才会返回值
--在请求完成时，这些值将按session聚合体现在SYS.dm_db_session_space_usage
select t.session_id,
t.request_id,
t.database_id,
t.user_objects_alloc_page_count,
t.internal_objects_dealloc_page_count,
t.internal_objects_alloc_page_count,
t.internal_objects_dealloc_page_count
from sys.dm_db_task_space_usage t
inner join sys.dm_exec_sessions e
on t.session_id = e.session_id
inner join sys.dm_exec_requests r
on t.session_id = r.session_id and
t.request_id = r.request_id
