网站建设资讯

NEWS

网站建设资讯

查看tempdb

use tempdb                                                               


-- 每隔1秒钟运行一次,直到用户手工终止脚本运行


while 1=1                                                               


begin                                                                   


select getdate()                                                        


-- 从文件级看tempdb使用情况


dbcc showfilestats                                                      


-- Query 1


-- 返回所有做过空间申请的session信息


Select 'Tempdb' as DB, getdate() as Time,                                                        


    SUM (user_object_reserved_page_count)*8 as user_objects_kb,         


    SUM (internal_object_reserved_page_count)*8 as internal_objects_kb, 


    SUM (version_store_reserved_page_count)*8  as version_store_kb,     


    SUM (unallocated_extent_page_count)*8 as freespace_kb               


From sys.dm_db_file_space_usage                                         


Where database_id = 2                                                    


-- Query 2


-- 这个管理视图能够反映当时tempdb空间的总体分配


SELECT 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,


t3.*


from sys.dm_db_session_space_usage  t1 ,                               


-- 反映每个session累计空间申请


sys.dm_exec_sessions as t3


-- 每个session的信息


where


t1.session_id = t3.session_id


and (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)


-- Query 3


-- 返回正在运行并且做过空间申请的session正在运行的语句


SELECT t1.session_id,                                                    


st.text                                                        


from sys.dm_db_session_space_usage as t1,                               


sys.dm_exec_requests as t4                                              


CROSS APPLY sys.dm_exec_sql_text(t4.sql_handle) AS st                   


 where  t1.session_id = t4.session_id                                       


   and t1.session_id >50                                                


and (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)                                              


waitfor delay '0:0:1'                                                    


end          


点击(此处)折叠或打开

在分宜等地区,都构建了全面的区域性战略布局,加强发展的系统性、市场前瞻性、产品创新能力,以专注、极致的服务理念,为客户提供做网站、网站设计 网站设计制作按需网站开发,公司网站建设,企业网站建设,品牌网站设计,成都全网营销推广,外贸网站建设,分宜网站建设费用合理。

  1. select replace(a.hostname,' ','') as hostname ,''''+replace(program_name,' ','')+'''' as program_name
  2. , loginame, j.*
  3. from (
  4. SELECT t1.session_id, t1.internal_objects_alloc_page_count*8.0 internal_objects_alloc_KB,
  5. t1.user_objects_alloc_page_count*8.0 user_objects_alloc_KB,
  6. t1.internal_objects_dealloc_page_count*8.0 internal_objects_dealloc_KB,
  7. t1.user_objects_dealloc_page_count*8.0 user_objects_dealloc_KB,
  8. st.text
  9. from sys.dm_db_session_space_usage as t1,
  10. sys.dm_exec_requests as t4
  11. CROSS APPLY sys.dm_exec_sql_text(t4.sql_handle) AS st
  12. where t1.session_id = t4.session_id
  13.   and t1.session_id >50
  14.   and (t1.internal_objects_alloc_page_count>0
  15.     or t1.user_objects_alloc_page_count >0
  16.     or t1.internal_objects_dealloc_page_count>0
  17.     or t1.user_objects_dealloc_page_count>0) ) as j
  18. left join
  19.     sys.sysprocesses as a with(nolock) on a.spid=j.session_id
  20.     where isnull(a.loginame,'') <>''

点击(此处)折叠或打开

  1. select j.session_id,replace(s.hostname,' ','') as hostname ,''''+replace(s.program_name,' ','')+'''' as program_name
  2. , s.loginame, db_name(s.dbid) AS DBname,j.text from
  3.                                              
  4.  ( SELECT t1.session_id,st.text
  5. from sys.dm_db_session_space_usage as t1,
  6. sys.dm_exec_requests as t4
  7. CROSS APPLY sys.dm_exec_sql_text(t4.sql_handle) AS st
  8.                
  9.  where t1.session_id = t4.session_id
  10.     and t1.session_id >50
  11.     and (t1.internal_objects_alloc_page_count>0
  12.     or t1.user_objects_alloc_page_count >0
  13.     or t1.internal_objects_dealloc_page_count>0
  14.     or t1.user_objects_dealloc_page_count>0)) as j
  15.                                              
  16. left join
  17.     sys.sysprocesses as s with(nolock) on s.spid=j.session_id
  18.         where isnull(s.loginame,'') <>''


点击(此处)折叠或打开

  1. select sum(convert(float,size) * (8192/1024)/1024/1024) TMPFILE_GB, sum(convert(float,maxsize) * (8192/1024)/1024/1024) MAX_TMPFILE_GB,(sum(size)*1.0/sum(maxsize))*100 USED_PER from tempdb.dbo.sysfiles
  2. where name like 'tempdev%'
  3. select * from tempdb.dbo.sysfiles


               

点击(此处)折叠或打开

  1. use tempdb

  2. -- 每隔1秒钟运行一次,直到用户手工终止脚本运行

  3. while 1=1

  4. begin

  5. select getdate()

  6. -- 从文件级看tempdb使用情况

  7. dbcc showfilestats

  8. -- Query 1

  9. -- 返回所有做过空间申请的session信息

  10. Select 'Tempdb' as DB, getdate() as Time,

  11.     SUM (user_object_reserved_page_count)*8 as user_objects_kb,

  12.     SUM (internal_object_reserved_page_count)*8 as internal_objects_kb,

  13.     SUM (version_store_reserved_page_count)*8 as version_store_kb,

  14.     SUM (unallocated_extent_page_count)*8 as freespace_kb

  15. From sys.dm_db_file_space_usage

  16. Where database_id = 2

  17. -- Query 2

  18. -- 这个管理视图能够反映当时tempdb空间的总体分配

  19. SELECT t1.session_id,

  20. t1.internal_objects_alloc_page_count, t1.user_objects_alloc_page_count,

  21. t1.internal_objects_dealloc_page_count , t1.user_objects_dealloc_page_count,

  22. t3.*

  23. from sys.dm_db_session_space_usage t1 ,

  24. -- 反映每个session累计空间申请

  25. sys.dm_exec_sessions as t3

  26. -- 每个session的信息

  27. where

  28. t1.session_id = t3.session_id

  29. and (t1.internal_objects_alloc_page_count>0

  30. or t1.user_objects_alloc_page_count >0

  31. or t1.internal_objects_dealloc_page_count>0

  32. or t1.user_objects_dealloc_page_count>0)

  33. -- Query 3

  34. -- 返回正在运行并且做过空间申请的session正在运行的语句

  35. SELECT t1.session_id,

  36. st.text

  37. from sys.dm_db_session_space_usage as t1,

  38. sys.dm_exec_requests as t4

  39. CROSS APPLY sys.dm_exec_sql_text(t4.sql_handle) AS st

  40.  where t1.session_id = t4.session_id

  41.    and t1.session_id >50

  42. and (t1.internal_objects_alloc_page_count>0

  43. or t1.user_objects_alloc_page_count >0

  44. or t1.internal_objects_dealloc_page_count>0

  45. or t1.user_objects_dealloc_page_count>0)

  46. waitfor delay '0:0:1'

  47. end



分享文章:查看tempdb
链接地址:http://njwzjz.com/article/jiiccp.html