1、數據庫

代碼如下:

--所有數據庫的大小

exec sp_helpdb

--所有數據庫的狀態(tài)

select name,

user_access_desc, --用戶訪問模式

state_desc, --數據庫狀態(tài)

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

--查看所有文件所在數據庫、路徑、狀態(tài)、大小

select db_name(database_id) dbname,

type_desc, --數據還是日志

name, --文件的邏輯名稱

physical_name, --文件的物理路徑

state_desc, --文件狀態(tài)

size * 8.0/1024 as '文件大?。∕B）'

from sys.master_files

--按區(qū)extent計算空間，沒有性能影響，基本準確,把TotalExtents*64/1024,單位為MB

--同時也適用于計算tempdb的文件大小，但不包括日志文件

dbcc showfilestats

3、日志文件

代碼如下:

--查看日志文件所在數據庫、路徑、狀態(tài)、大小

select db_name(database_id) dbname,

type_desc, --數據還是日志

name, --文件的邏輯名稱

physical_name, --文件的物理路徑

state_desc, --文件狀態(tài)

size * 8.0/1024 as '文件大小（MB）'

from sys.master_files

where type_desc = 'LOG'

--所有數據庫的日志的大小,空間使用率

dbcc sqlperf(logspace)

4、數據文件、日志文件的I/O統(tǒng)計信息

代碼如下:

--數據和日志文件的I/O統(tǒng)計信息,包含文件大小

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 --文件在磁盤上所占用的實際字節(jié)數

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 --文件在磁盤上所占用的實際字節(jié)數

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'

--按頁統(tǒng)計，沒有性能影響，有時不準確

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

一次計算多個對象的空間使用情況

sys.dm_db_partition_stats返回當前數據庫中每個分區(qū)(表和索引)的頁和行計數信息

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

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

--按頁或區(qū)統(tǒng)計，有性能影響，準確

--顯示當前數據庫中所有的表或視圖的數據和索引的空間信息

--包含：邏輯碎片、區(qū)碎片(碎片率)、平均頁密度

dbcc showcontig(temp_lock)

--SQL Server推薦使用的動態(tài)性能函數，準確

select *

from sys.dm_db_index_physical_stats(

db_id('test'), --數據庫id

object_id('test.dbo.temp_lock'), --對象id

null, --索引id

null, --分區(qū)號

'limited' --default,null,'limited','sampled','detailed',默認為'limited'

--'limited'模式運行最快，掃描的頁數最少,對于堆會掃描所有頁,對于索引只掃描葉級以上的父級頁

--'sampled'模式會返回堆、索引中所有頁的1%樣本的統(tǒng)計信息，如果少于1000頁，那么用'detailed'代替'sampled'

--'detailed'模式會掃描所有頁,返回所有統(tǒng)計信息

)

--查找哪些對象是需要重建的

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ū)信息是數據庫的數據文件的盤區(qū)信息，日志文件不以盤區(qū)為單位

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

DBCC EXTENTINFO('[test]','extentinfo',0)

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

insert extentinfo

exec('dbcc extentinfo(''test'') ')

go

--每一個區(qū)有一條數據

select file_id,

obj_id, --對象ID

index_id, --索引id

page_id, --這個區(qū)是從哪個頁開始的,也就是這個區(qū)中的第一個頁面的頁面號

pg_alloc, --這個盤區(qū)分配的頁面數量

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

partition_number,

partition_id,

iam_chain_type, --IAM鏈類型:行內數據,行溢出數據,大對象數據

pfs_bytes

from extentinfo

order by file_id,

OBJ_ID,

index_id,

partition_id,

ext_size

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

數據庫的數據文件的盤區(qū)信息,通過計算每個對象理論上區(qū)的數量和實際數量,如果兩者相差很大,

那就應該重建對象.

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

2.如果pg_alloc比ext_size小，也就是實際每個區(qū)分配的頁數小于理論上這個區(qū)的頁數，

那么就會多一條記錄，把本應該屬于這個區(qū)的頁放到多出來的這條記錄對應的區(qū)中，

那么原來只有一條記錄(也就是一個區(qū))，現(xiàn)在就有2條記錄(也就是2個區(qū))，

導致實際的區(qū)數量2大于理論上的區(qū)數量1.

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

select file_id,

obj_id,

index_id,

partition_id,

ext_size,

count(*) as '實際區(qū)的個數',

sum(pg_alloc) as '實際包含的頁數',

ceiling(sum(pg_alloc) * 1.0 / ext_size) as '理論上的區(qū)的個數',

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

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ū)的個數 < 實際區(qū)的個數,也就是百分比小于100%的

order by partition_id, obj_id, index_id, [file_id]

6、tempdb數據庫

代碼如下:

--tempdb數據庫的空間使用

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

tempdb中包含的對象：

用戶對象：是用戶顯式創(chuàng)建的，這些對象位于用戶會話的作用域，

可以位于創(chuàng)建對象的例程(存儲過程、觸發(fā)器、函數)的作用域中。

1.用戶定義的表、索引

2.系統(tǒng)表、索引

3.全局臨時表、索引

4.局部臨時表、索引

5.表變量

6.表值函數中返回的表

內部對象：是根據需要由SQL Server數據庫引擎創(chuàng)建的，用于處理SQL Server語句，

內部對象可以在語句作用域中創(chuàng)建、刪除。

每個內部對象至少需要9個頁面，一個IAM頁，一個區(qū)包含了8個頁。

1.游標、假脫機操作、臨時的大型對象(LOB)，存儲的工作表

2.哈希聯(lián)接、哈希聚合操作的工作文件

3.如果設置了sort_in_tempdb選項，那么創(chuàng)建、重新生成索引的重建排序結果存放在tempdb；

group by、order by、union操作的中間結果。

版本存儲區(qū)：是數據頁的集合，包含了支持行版本控制功能的所需的數據，主要支持快照事務隔離級別，

以及一些其他的提高數據庫并發(fā)性能的新功能。

1.公用版本存儲區(qū)：在使用快照隔離級別、已提交讀隔離級別的數據庫中，由數據修改事務生成的行版本。

2.聯(lián)機索引生成版本存儲區(qū)：為了實現(xiàn)聯(lián)機索引操作而為數據修改事務生成的行版本，

多個活動結果集，after觸發(fā)器生成的行版本。

上面也提到了，由于sys.allocation_units和sys.partitions視圖沒有記錄tempdb中的內部對象、版本存儲區(qū)

所以這2個視圖和sp_spaceused，不能準確反應出tempdb的空間使用。

分析tempdb現(xiàn)有的工作負載:

1.設置tempdb的自動增長

2.通過模擬單獨的查詢、工作任務，監(jiān)控tempdb空間使用

3.通過模擬執(zhí)行一些系統(tǒng)維護操作(重新生成索引),監(jiān)控tempdb空間使用

4.根據2和3中tempdb的空間使用量,預測總工作負荷會使用的空間,并針對任務的并發(fā)度調整這個值.

5.根據4得到的值,設置生成環(huán)境中tempdb的初始大小,并開啟自動增長.

另外,tempdb的文件個數和大小,不僅需要滿足實際使用需要,還要考慮性能優(yōu)化.

監(jiān)控tempdb的空間使用方法:

1.可以通過SQL Trace來跟蹤,但是由于不能預期造成大量使用tempdb語句在什么時候運行,

而且SQL Trance操作比較昂貴,如果一直開著會產生大量的跟蹤文件,對硬盤的負擔也比較重,一般不用.

2.輕量級的監(jiān)控是通過一定時間間隔運行能夠監(jiān)控系統(tǒng)運行的dbcc命令、動態(tài)性能視圖-函數，

把結果記錄在文件中，這對于很繁忙的系統(tǒng)是不錯的選擇。

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

Select DB_NAME(database_id) as DB,

max(FILE_ID) as '文件id',

SUM (user_object_reserved_page_count) as '用戶對象保留的頁數', ----包含已分配區(qū)中的未使用頁數

SUM (internal_object_reserved_page_count) as '內部對象保留的頁數', --包含已分配區(qū)中的未使用頁數

SUM (version_store_reserved_page_count) as '版本存儲保留的頁數',

SUM (unallocated_extent_page_count) as '未分配的區(qū)中包含的頁數', --不包含已分配區(qū)中的未使用頁數

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)

--能夠反映當時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聚合體現(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

更多信息請查看IT技術專欄