程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> 其他數據庫知識 >> 更多數據庫知識 >> SQL Server DBA日常檢查常用SQL

SQL Server DBA日常檢查常用SQL

編輯:更多數據庫知識

1、數據庫

復制代碼 代碼如下:
--所有數據庫的大小 
exec sp_helpdb 

 
--所有數據庫的狀態 
select name, 
       user_access_desc,           --用戶訪問模式 
       state_desc,                 --數據庫狀態 
       recovery_model_desc,        --恢復模式 
       page_verify_option_desc,    --頁檢測選項 
       log_reuse_wait_desc         --日志重用等待 
from sys.databases 

 
--某個數據庫的大小:按頁面計算空間,有性能影響,基本准確,有時不准確 
use test 
go 

exec sp_spaceused   
go 

 

--可以@updateusage = 'true',會運行dbcc updateusage 
exec sp_spaceused  @updateusage = 'true' 

 
--對某個數據庫,顯示目錄視圖中的頁數和行數錯誤並更正 
DBCC UPDATEUSAGE('test') 

2、數據文件
復制代碼 代碼如下:
--查看某個數據庫中的所有文件及大小 
sp_helpfile  

 

--查看所有文件所在數據庫、路徑、狀態、大小 
select db_name(database_id) dbname, 
       type_desc,      --數據還是日志 
       name,           --文件的邏輯名稱 
       physical_name,  --文件的物理路徑 
       state_desc,     --文件狀態 
       size * 8.0/1024 as '文件大小(MB)'         
from sys.master_files 

 

--按區extent計算空間,沒有性能影響,基本准確,把TotalExtents*64/1024,單位為MB 
--同時也適用於計算tempdb的文件大小,但不包括日志文件 
dbcc showfilestats 

3、日志文件
復制代碼 代碼如下:
--查看日志文件所在數據庫、路徑、狀態、大小 
select db_name(database_id) dbname, 
       type_desc,      --數據還是日志 
       name,           --文件的邏輯名稱 
       physical_name,  --文件的物理路徑 
       state_desc,     --文件狀態 
       size * 8.0/1024 as '文件大小(MB)'         
from sys.master_files 
where type_desc = 'LOG' 

 

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

4、數據文件、日志文件的I/O統計信息
復制代碼 代碼如下:
--數據和日志文件的I/O統計信息,包含文件大小 
    select database_id, 
           file_id, 
           file_handle,           --windows文件句柄 
           sample_ms,             --自從計算機啟動以來的毫秒數 

           num_of_reads, 
           num_of_bytes_read, 
           io_stall_read_ms,      --等待讀取的時間 

           num_of_writes, 
           num_of_bytes_written, 
           io_stall_write_ms, 

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

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

    select database_id, 
           file_id, 
           file_handle,           --windows文件句柄 
           sample_ms,             --自從計算機啟動以來的毫秒數 

           num_of_reads, 
           num_of_bytes_read, 
           io_stall_read_ms,      --等待讀取的時間 

           num_of_writes, 
           num_of_bytes_written, 
           io_stall_write_ms, 

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

5、對象,包括:表、索引、索引視圖等
復制代碼 代碼如下:
--不一定准確:某個表的行數,保留大小,數據大小,索引大小,未使用大小 
exec sp_spaceused @objname ='temp_lock' 

 
--准確:但有性能影響 
exec sp_spaceused @objname ='temp_lock', 
                  @updateusage ='true' 

 

--按頁統計,沒有性能影響,有時不准確 
/*====================================================== 
一次計算多個對象的空間使用情況 

sys.dm_db_partition_stats返回當前數據庫中每個分區(表和索引)的頁和行計數信息 
========================================================*/    
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,  --數據頁,包含表中數據、索引中的lob數據、索引中的行溢出數據 

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

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    

  

--按頁或區統計,有性能影響,准確          
--顯示當前數據庫中所有的表或視圖的數據和索引的空間信息 
--包含:邏輯碎片、區碎片(碎片率)、平均頁密度                
dbcc showcontig(temp_lock) 

 

--SQL Server推薦使用的動態性能函數,准確 
select * 
from sys.dm_db_index_physical_stats( 
        db_id('test'),                      --數據庫id 
        object_id('test.dbo.temp_lock'),    --對象id 
        null,                               --索引id 
        null,                               --分區號 

        'limited'   --default,null,'limited','sampled','detailed',默認為'limited' 
                    --'limited'模式運行最快,掃描的頁數最少,對於堆會掃描所有頁,對於索引只掃描葉級以上的父級頁 
                    --'sampled'模式會返回堆、索引中所有頁的1%樣本的統計信息,如果少於1000頁,那麼用'detailed'代替'sampled' 
                    --'detailed'模式會掃描所有頁,返回所有統計信息 
    ) 

 

--查找哪些對象是需要重建的 
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  

 
/*==================================================================== 
查詢到的盤區信息是數據庫的數據文件的盤區信息,日志文件不以盤區為單位 

命令格式:  DBCC EXTENTINFO(dbname,tablename,indexid) 

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

 
--每一個區有一條數據 
select  file_id,  
        obj_id,               --對象ID 
        index_id,             --索引id 

        page_id,              --這個區是從哪個頁開始的,也就是這個區中的第一個頁面的頁面號 
        pg_alloc,             --這個盤區分配的頁面數量 

        ext_size,             --這個盤區包含了多少頁 

        partition_number, 
        partition_id, 
        iam_chain_type,       --IAM鏈類型:行內數據,行溢出數據,大對象數據 
        pfs_bytes  
from extentinfo 
order by file_id, 
         OBJ_ID, 
         index_id, 
         partition_id, 
         ext_size 

 
/*===================================================================================================== 
數據庫的數據文件的盤區信息,通過計算每個對象理論上區的數量和實際數量,如果兩者相差很大, 
那就應該重建對象. 

1.每一條記錄就是一個區 

2.如果pg_alloc比ext_size小,也就是實際每個區分配的頁數小於理論上這個區的頁數, 
  那麼就會多一條記錄,把本應該屬於這個區的頁放到多出來的這條記錄對應的區中, 
  那麼原來只有一條記錄(也就是一個區),現在就有2條記錄(也就是2個區), 
  導致實際的區數量2大於理論上的區數量1. 
========================================================================================================*/ 
select file_id, 
       obj_id,  
       index_id,  
       partition_id,  
       ext_size,  

       count(*) as '實際區的個數',  
       sum(pg_alloc) as '實際包含的頁數',  

       ceiling(sum(pg_alloc) * 1.0 / ext_size) as '理論上的區的個數',  
       ceiling(sum(pg_alloc) * 1.0 / ext_size) / count(*) * 100.00 as '理論上的區個數 / 實際區的個數'  

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


6、tempdb數據庫
復制代碼 代碼如下:
--tempdb數據庫的空間使用 
/*====================================================== 
tempdb中包含的對象: 

用戶對象:是用戶顯式創建的,這些對象位於用戶會話的作用域, 
         可以位於創建對象的例程(存儲過程、觸發器、函數)的作用域中。 
    1.用戶定義的表、索引 
    2.系統表、索引 
    3.全局臨時表、索引 
    4.局部臨時表、索引 
    5.表變量 
    6.表值函數中返回的表 

內部對象:是根據需要由SQL Server數據庫引擎創建的,用於處理SQL Server語句, 
          內部對象可以在語句作用域中創建、刪除。 
          每個內部對象至少需要9個頁面,一個IAM頁,一個區包含了8個頁。 
    1.游標、假脫機操作、臨時的大型對象(LOB),存儲的工作表 
    2.哈希聯接、哈希聚合操作的工作文件 
    3.如果設置了sort_in_tempdb選項,那麼創建、重新生成索引的重建排序結果存放在tempdb; 
      group by、order by、union操作的中間結果。 

版本存儲區:是數據頁的集合,包含了支持行版本控制功能的所需的數據,主要支持快照事務隔離級別, 
            以及一些其他的提高數據庫並發性能的新功能。 
    1.公用版本存儲區:在使用快照隔離級別、已提交讀隔離級別的數據庫中,由數據修改事務生成的行版本。 
    2.聯機索引生成版本存儲區:為了實現聯機索引操作而為數據修改事務生成的行版本, 
      多個活動結果集,after觸發器生成的行版本。  

                    
上面也提到了,由於sys.allocation_units和sys.partitions視圖沒有記錄tempdb中的內部對象、版本存儲區 
所以這2個視圖和sp_spaceused,不能准確反應出tempdb的空間使用。 

 
分析tempdb現有的工作負載: 
    1.設置tempdb的自動增長 
    2.通過模擬單獨的查詢、工作任務,監控tempdb空間使用 
    3.通過模擬執行一些系統維護操作(重新生成索引),監控tempdb空間使用 
    4.根據2和3中tempdb的空間使用量,預測總工作負荷會使用的空間,並針對任務的並發度調整這個值. 
    5.根據4得到的值,設置生成環境中tempdb的初始大小,並開啟自動增長. 
      另外,tempdb的文件個數和大小,不僅需要滿足實際使用需要,還要考慮性能優化. 

 
監控tempdb的空間使用方法: 
    1.可以通過SQL Trace來跟蹤,但是由於不能預期造成大量使用tempdb語句在什麼時候運行, 
      而且SQL Trance操作比較昂貴,如果一直開著會產生大量的跟蹤文件,對硬盤的負擔也比較重,一般不用. 

    2.輕量級的監控是通過一定時間間隔運行能夠監控系統運行的dbcc命令、動態性能視圖-函數, 
      把結果記錄在文件中,這對於很繁忙的系統是不錯的選擇。 

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

Select DB_NAME(database_id) 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)  as '版本存儲保留的頁數',      
       SUM (unallocated_extent_page_count) as '未分配的區中包含的頁數',     --不包含已分配區中的未使用頁數    

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

                                                  
--能夠反映當時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, 
       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     

     

--返回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 
       

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved