--所有數(shù)據(jù)庫(kù)的大小 
exec sp_helpdb 

 
--所有數(shù)據(jù)庫(kù)的狀態(tài) 
select name, 
       user_access_desc,           --用戶訪問模式 
       state_desc,                 --數(shù)據(jù)庫(kù)狀態(tài) 
       recovery_model_desc,        --恢復(fù)模式 
       page_verify_option_desc,    --頁檢測(cè)選項(xiàng) 
       log_reuse_wait_desc         --日志重用等待 
from sys.databases 

 
--某個(gè)數(shù)據(jù)庫(kù)的大小:按頁面計(jì)算空間，有性能影響，基本準(zhǔn)確，有時(shí)不準(zhǔn)確 
use test 
go 

exec sp_spaceused   
go 

 

--可以@updateusage = 'true',會(huì)運(yùn)行dbcc updateusage 
exec sp_spaceused  @updateusage = 'true' 

 
--對(duì)某個(gè)數(shù)據(jù)庫(kù),顯示目錄視圖中的頁數(shù)和行數(shù)錯(cuò)誤并更正 
DBCC UPDATEUSAGE('test') 

--查看某個(gè)數(shù)據(jù)庫(kù)中的所有文件及大小 
sp_helpfile  

 

--查看所有文件所在數(shù)據(jù)庫(kù)、路徑、狀態(tài)、大小 
select db_name(database_id) dbname, 
       type_desc,      --數(shù)據(jù)還是日志 
       name,           --文件的邏輯名稱 
       physical_name,  --文件的物理路徑 
       state_desc,     --文件狀態(tài) 
       size * 8.0/1024 as '文件大?。∕B）'         
from sys.master_files 

 

--按區(qū)extent計(jì)算空間，沒有性能影響，基本準(zhǔn)確,把TotalExtents*64/1024,單位為MB 
--同時(shí)也適用于計(jì)算tempdb的文件大小，但不包括日志文件 
dbcc showfilestats 

--查看日志文件所在數(shù)據(jù)庫(kù)、路徑、狀態(tài)、大小 
select db_name(database_id) dbname, 
       type_desc,      --數(shù)據(jù)還是日志 
       name,           --文件的邏輯名稱 
       physical_name,  --文件的物理路徑 
       state_desc,     --文件狀態(tài) 
       size * 8.0/1024 as '文件大?。∕B）'         
from sys.master_files 
where type_desc = 'LOG' 

 

--所有數(shù)據(jù)庫(kù)的日志的大小,空間使用率 
dbcc sqlperf(logspace) 

--數(shù)據(jù)和日志文件的I/O統(tǒng)計(jì)信息,包含文件大小 
    select database_id, 
           file_id, 
           file_handle,           --windows文件句柄 
           sample_ms,             --自從計(jì)算機(jī)啟動(dòng)以來的毫秒數(shù) 

           num_of_reads, 
           num_of_bytes_read, 
           io_stall_read_ms,      --等待讀取的時(shí)間 

           num_of_writes, 
           num_of_bytes_written, 
           io_stall_write_ms, 

           io_stall,              --用戶等待文件完成I/O操作所用的總時(shí)間 
           size_on_disk_bytes     --文件在磁盤上所占用的實(shí)際字節(jié)數(shù)         

    from sys.dm_io_virtual_file_stats(db_id('test'),   --數(shù)據(jù)庫(kù)id 
                                       1 )  --數(shù)據(jù)文件id                                        
    union all 

    select database_id, 
           file_id, 
           file_handle,           --windows文件句柄 
           sample_ms,             --自從計(jì)算機(jī)啟動(dòng)以來的毫秒數(shù) 

           num_of_reads, 
           num_of_bytes_read, 
           io_stall_read_ms,      --等待讀取的時(shí)間 

           num_of_writes, 
           num_of_bytes_written, 
           io_stall_write_ms, 

           io_stall,              --用戶等待文件完成I/O操作所用的總時(shí)間 
           size_on_disk_bytes     --文件在磁盤上所占用的實(shí)際字節(jié)數(shù)  
    from sys.dm_io_virtual_file_stats( db_id('test'),   --數(shù)據(jù)庫(kù)id 
                                       2 )  --日志文件id 

--不一定準(zhǔn)確:某個(gè)表的行數(shù),保留大小，數(shù)據(jù)大小，索引大小，未使用大小 
exec sp_spaceused @objname ='temp_lock' 

 
--準(zhǔn)確:但有性能影響 
exec sp_spaceused @objname ='temp_lock', 
                  @updateusage ='true' 

 

--按頁統(tǒng)計(jì)，沒有性能影響，有時(shí)不準(zhǔn)確 
/*====================================================== 
一次計(jì)算多個(gè)對(duì)象的空間使用情況 

sys.dm_db_partition_stats返回當(dāng)前數(shù)據(jù)庫(kù)中每個(gè)分區(qū)(表和索引)的頁和行計(jì)數(shù)信息 
========================================================*/    
select o.name, 
       sum(p.reserved_page_count) as reserved_page_count, --保留頁，包含表和索引 

       sum(p.used_page_count) as used_page_count,         --已使用頁，包含表和索引 

       sum(case when p.index_id 2  
                     then p.in_row_data_page_count +  
                          p.lob_used_page_count +  
                          p.row_overflow_used_page_count 
                else p.lob_used_page_count +  
                     p.row_overflow_used_page_count 
           end) as data_pages,  --數(shù)據(jù)頁,包含表中數(shù)據(jù)、索引中的lob數(shù)據(jù)、索引中的行溢出數(shù)據(jù) 

       sum(case when p.index_id 2  
                     then p.row_count 
                else 0 
           end) as row_counts   --數(shù)據(jù)行數(shù)，包含表中的數(shù)據(jù)行數(shù)，不包含索引中的數(shù)據(jù)條目數(shù) 

from sys.dm_db_partition_stats p 
inner join sys.objects o 
        on p.object_id = o.object_id  
where p.object_id= object_id('表名') 
group by o.name    

  

--按頁或區(qū)統(tǒng)計(jì)，有性能影響，準(zhǔn)確          
--顯示當(dāng)前數(shù)據(jù)庫(kù)中所有的表或視圖的數(shù)據(jù)和索引的空間信息 
--包含：邏輯碎片、區(qū)碎片(碎片率)、平均頁密度                
dbcc showcontig(temp_lock) 

 

--SQL Server推薦使用的動(dòng)態(tài)性能函數(shù)，準(zhǔn)確 
select * 
from sys.dm_db_index_physical_stats( 
        db_id('test'),                      --數(shù)據(jù)庫(kù)id 
        object_id('test.dbo.temp_lock'),    --對(duì)象id 
        null,                               --索引id 
        null,                               --分區(qū)號(hào) 

        'limited'   --default,null,'limited','sampled','detailed',默認(rèn)為'limited' 
                    --'limited'模式運(yùn)行最快，掃描的頁數(shù)最少,對(duì)于堆會(huì)掃描所有頁,對(duì)于索引只掃描葉級(jí)以上的父級(jí)頁 
                    --'sampled'模式會(huì)返回堆、索引中所有頁的1%樣本的統(tǒng)計(jì)信息，如果少于1000頁，那么用'detailed'代替'sampled' 
                    --'detailed'模式會(huì)掃描所有頁,返回所有統(tǒng)計(jì)信息 
    ) 

 

--查找哪些對(duì)象是需要重建的 
use test 
go 

if OBJECT_ID('extentinfo') is not null 
    drop table extentinfo 
go 

create table extentinfo  
(   [file_id] smallint,  
    page_id int,  
    pg_alloc int,                
    ext_size int,                 
    obj_id int,                   

    index_id int,                 
    partition_number int, 
    partition_id bigint, 
    iam_chain_type varchar(50),   
    pfs_bytes varbinary(10)  
)  
go  

 
/*==================================================================== 
查詢到的盤區(qū)信息是數(shù)據(jù)庫(kù)的數(shù)據(jù)文件的盤區(qū)信息，日志文件不以盤區(qū)為單位 

命令格式:  DBCC EXTENTINFO（dbname,tablename,indexid） 

DBCC EXTENTINFO('[test]','extentinfo',0) 
======================================================================*/ 
insert extentinfo  
exec('dbcc extentinfo(''test'') ') 
go 

 
--每一個(gè)區(qū)有一條數(shù)據(jù) 
select  file_id,  
        obj_id,               --對(duì)象ID 
        index_id,             --索引id 

        page_id,              --這個(gè)區(qū)是從哪個(gè)頁開始的,也就是這個(gè)區(qū)中的第一個(gè)頁面的頁面號(hào) 
        pg_alloc,             --這個(gè)盤區(qū)分配的頁面數(shù)量 

        ext_size,             --這個(gè)盤區(qū)包含了多少頁 

        partition_number, 
        partition_id, 
        iam_chain_type,       --IAM鏈類型:行內(nèi)數(shù)據(jù),行溢出數(shù)據(jù),大對(duì)象數(shù)據(jù) 
        pfs_bytes  
from extentinfo 
order by file_id, 
         OBJ_ID, 
         index_id, 
         partition_id, 
         ext_size 

 
/*===================================================================================================== 
數(shù)據(jù)庫(kù)的數(shù)據(jù)文件的盤區(qū)信息,通過計(jì)算每個(gè)對(duì)象理論上區(qū)的數(shù)量和實(shí)際數(shù)量,如果兩者相差很大, 
那就應(yīng)該重建對(duì)象. 

1.每一條記錄就是一個(gè)區(qū) 

2.如果pg_alloc比ext_size小，也就是實(shí)際每個(gè)區(qū)分配的頁數(shù)小于理論上這個(gè)區(qū)的頁數(shù)， 
  那么就會(huì)多一條記錄，把本應(yīng)該屬于這個(gè)區(qū)的頁放到多出來的這條記錄對(duì)應(yīng)的區(qū)中， 
  那么原來只有一條記錄(也就是一個(gè)區(qū))，現(xiàn)在就有2條記錄(也就是2個(gè)區(qū))， 
  導(dǎo)致實(shí)際的區(qū)數(shù)量2大于理論上的區(qū)數(shù)量1. 
========================================================================================================*/ 
select file_id, 
       obj_id,  
       index_id,  
       partition_id,  
       ext_size,  

       count(*) as '實(shí)際區(qū)的個(gè)數(shù)',  
       sum(pg_alloc) as '實(shí)際包含的頁數(shù)',  

       ceiling(sum(pg_alloc) * 1.0 / ext_size) as '理論上的區(qū)的個(gè)數(shù)',  
       ceiling(sum(pg_alloc) * 1.0 / ext_size) / count(*) * 100.00 as '理論上的區(qū)個(gè)數(shù) / 實(shí)際區(qū)的個(gè)數(shù)'  

from extentinfo  
group by file_id, 
         obj_id,  
         index_id, 
         partition_id,  
         ext_size  
having ceiling(sum(pg_alloc)*1.0/ext_size) count(*)    
--過濾: 理論上區(qū)的個(gè)數(shù) 實(shí)際區(qū)的個(gè)數(shù),也就是百分比小于100%的 
order by partition_id, obj_id, index_id, [file_id]      

--tempdb數(shù)據(jù)庫(kù)的空間使用 
/*====================================================== 
tempdb中包含的對(duì)象： 

用戶對(duì)象：是用戶顯式創(chuàng)建的，這些對(duì)象位于用戶會(huì)話的作用域， 
         可以位于創(chuàng)建對(duì)象的例程(存儲(chǔ)過程、觸發(fā)器、函數(shù))的作用域中。 
    1.用戶定義的表、索引 
    2.系統(tǒng)表、索引 
    3.全局臨時(shí)表、索引 
    4.局部臨時(shí)表、索引 
    5.表變量 
    6.表值函數(shù)中返回的表 

內(nèi)部對(duì)象：是根據(jù)需要由SQL Server數(shù)據(jù)庫(kù)引擎創(chuàng)建的，用于處理SQL Server語句， 
          內(nèi)部對(duì)象可以在語句作用域中創(chuàng)建、刪除。 
          每個(gè)內(nèi)部對(duì)象至少需要9個(gè)頁面，一個(gè)IAM頁，一個(gè)區(qū)包含了8個(gè)頁。 
    1.游標(biāo)、假脫機(jī)操作、臨時(shí)的大型對(duì)象(LOB)，存儲(chǔ)的工作表 
    2.哈希聯(lián)接、哈希聚合操作的工作文件 
    3.如果設(shè)置了sort_in_tempdb選項(xiàng)，那么創(chuàng)建、重新生成索引的重建排序結(jié)果存放在tempdb； 
      group by、order by、union操作的中間結(jié)果。 

版本存儲(chǔ)區(qū)：是數(shù)據(jù)頁的集合，包含了支持行版本控制功能的所需的數(shù)據(jù)，主要支持快照事務(wù)隔離級(jí)別， 
            以及一些其他的提高數(shù)據(jù)庫(kù)并發(fā)性能的新功能。 
    1.公用版本存儲(chǔ)區(qū)：在使用快照隔離級(jí)別、已提交讀隔離級(jí)別的數(shù)據(jù)庫(kù)中，由數(shù)據(jù)修改事務(wù)生成的行版本。 
    2.聯(lián)機(jī)索引生成版本存儲(chǔ)區(qū)：為了實(shí)現(xiàn)聯(lián)機(jī)索引操作而為數(shù)據(jù)修改事務(wù)生成的行版本， 
      多個(gè)活動(dòng)結(jié)果集，after觸發(fā)器生成的行版本。  

                    
上面也提到了，由于sys.allocation_units和sys.partitions視圖沒有記錄tempdb中的內(nèi)部對(duì)象、版本存儲(chǔ)區(qū) 
所以這2個(gè)視圖和sp_spaceused，不能準(zhǔn)確反應(yīng)出tempdb的空間使用。 

 
分析tempdb現(xiàn)有的工作負(fù)載: 
    1.設(shè)置tempdb的自動(dòng)增長(zhǎng) 
    2.通過模擬單獨(dú)的查詢、工作任務(wù)，監(jiān)控tempdb空間使用 
    3.通過模擬執(zhí)行一些系統(tǒng)維護(hù)操作(重新生成索引),監(jiān)控tempdb空間使用 
    4.根據(jù)2和3中tempdb的空間使用量,預(yù)測(cè)總工作負(fù)荷會(huì)使用的空間,并針對(duì)任務(wù)的并發(fā)度調(diào)整這個(gè)值. 
    5.根據(jù)4得到的值,設(shè)置生成環(huán)境中tempdb的初始大小,并開啟自動(dòng)增長(zhǎng). 
      另外,tempdb的文件個(gè)數(shù)和大小,不僅需要滿足實(shí)際使用需要,還要考慮性能優(yōu)化. 

 
監(jiān)控tempdb的空間使用方法: 
    1.可以通過SQL Trace來跟蹤,但是由于不能預(yù)期造成大量使用tempdb語句在什么時(shí)候運(yùn)行, 
      而且SQL Trance操作比較昂貴,如果一直開著會(huì)產(chǎn)生大量的跟蹤文件,對(duì)硬盤的負(fù)擔(dān)也比較重,一般不用. 

    2.輕量級(jí)的監(jiān)控是通過一定時(shí)間間隔運(yùn)行能夠監(jiān)控系統(tǒng)運(yùn)行的dbcc命令、動(dòng)態(tài)性能視圖-函數(shù)， 
      把結(jié)果記錄在文件中，這對(duì)于很繁忙的系統(tǒng)是不錯(cuò)的選擇。 

========================================================*/   

Select DB_NAME(database_id) as DB,  
       max(FILE_ID) as '文件id',          

       SUM (user_object_reserved_page_count) as '用戶對(duì)象保留的頁數(shù)',       ----包含已分配區(qū)中的未使用頁數(shù) 
       SUM (internal_object_reserved_page_count) as '內(nèi)部對(duì)象保留的頁數(shù)',   --包含已分配區(qū)中的未使用頁數(shù) 
       SUM (version_store_reserved_page_count)  as '版本存儲(chǔ)保留的頁數(shù)',      
       SUM (unallocated_extent_page_count) as '未分配的區(qū)中包含的頁數(shù)',     --不包含已分配區(qū)中的未使用頁數(shù)    

       SUM(mixed_extent_page_count) as '文件的已分配混合區(qū)中:已分配頁和未分配頁'  --包含IAM頁                          
From sys.dm_db_file_space_usage                                           
Where database_id = 2   
group by DB_NAME(database_id)    

                                                  
--能夠反映當(dāng)時(shí)tempdb空間的總體分配,申請(qǐng)空間的會(huì)話正在運(yùn)行的語句 
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, 
       t.text 
from sys.dm_db_session_space_usage  t1   --反映每個(gè)session的累計(jì)空間申請(qǐng)                                 
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     

     

--返回tempdb中頁分配和釋放活動(dòng)， 
--只有當(dāng)任務(wù)正在運(yùn)行時(shí)，sys.dm_db_task_space_usage才會(huì)返回值 
--在請(qǐng)求完成時(shí)，這些值將按session聚合體現(xiàn)在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