1、數(shù)據(jù)庫(kù)

代碼如下:

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

exec sp_helpdb

--所有數(shù)據(jù)庫(kù)的狀態(tài)

select name,

user_access_desc, --用戶訪問(wèn)模式

state_desc, --數(shù)據(jù)庫(kù)狀態(tài)

recovery_model_desc, --恢復(fù)模式

page_verify_option_desc, --頁(yè)檢測(cè)選項(xiàng)

log_reuse_wait_desc --日志重用等待

from sys.databases

--某個(gè)數(shù)據(jù)庫(kù)的大小:按頁(yè)面計(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ù),顯示目錄視圖中的頁(yè)數(shù)和行數(shù)錯(cuò)誤并更正

DBCC UPDATEUSAGE('test')

2、數(shù)據(jù)文件

代碼如下:

--查看某個(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ì)算空間，沒(méi)有性能影響，基本準(zhǔn)確,把TotalExtents*64/1024,單位為MB

--同時(shí)也適用于計(jì)算tempdb的文件大小，但不包括日志文件

dbcc showfilestats

3、日志文件

代碼如下:

--查看日志文件所在數(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 '文件大小（MB）'

from sys.master_files

where type_desc = 'LOG'

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

dbcc sqlperf(logspace)

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

代碼如下:

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

select database_id,

file_id,

file_handle, --windows文件句柄

sample_ms, --自從計(jì)算機(jī)啟動(dòng)以來(lái)的毫秒數(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)以來(lái)的毫秒數(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

5、對(duì)象，包括：表、索引、索引視圖等

代碼如下:

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

exec sp_spaceused @objname ='temp_lock'

--準(zhǔn)確:但有性能影響

exec sp_spaceused @objname ='temp_lock',

@updateusage ='true'

--按頁(yè)統(tǒng)計(jì)，沒(méi)有性能影響，有時(shí)不準(zhǔn)確

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

一次計(jì)算多個(gè)對(duì)象的空間使用情況

sys.dm_db_partition_stats返回當(dāng)前數(shù)據(jù)庫(kù)中每個(gè)分區(qū)(表和索引)的頁(yè)和行計(jì)數(shù)信息

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

select o.name,

sum(p.reserved_page_count) as reserved_page_count, --保留頁(yè)，包含表和索引

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

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ù)頁(yè),包含表中數(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

--按頁(yè)或區(qū)統(tǒng)計(jì)，有性能影響，準(zhǔn)確

--顯示當(dāng)前數(shù)據(jù)庫(kù)中所有的表或視圖的數(shù)據(jù)和索引的空間信息

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

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)行最快，掃描的頁(yè)數(shù)最少,對(duì)于堆會(huì)掃描所有頁(yè),對(duì)于索引只掃描葉級(jí)以上的父級(jí)頁(yè)

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

--'detailed'模式會(huì)掃描所有頁(yè),返回所有統(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è)頁(yè)開始的,也就是這個(gè)區(qū)中的第一個(gè)頁(yè)面的頁(yè)面號(hào)

pg_alloc, --這個(gè)盤區(qū)分配的頁(yè)面數(shù)量

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

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ū)信息,通過(guò)計(jì)算每個(gè)對(duì)象理論上區(qū)的數(shù)量和實(shí)際數(shù)量,如果兩者相差很大,

那就應(yīng)該重建對(duì)象.

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

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

那么就會(huì)多一條記錄，把本應(yīng)該屬于這個(gè)區(qū)的頁(yè)放到多出來(lái)的這條記錄對(duì)應(yīng)的區(qū)中，

那么原來(lái)只有一條記錄(也就是一個(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í)際包含的頁(yè)數(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(*)

--過(guò)濾: 理論上區(qū)的個(gè)數(shù) < 實(shí)際區(qū)的個(gè)數(shù),也就是百分比小于100%的

order by partition_id, obj_id, index_id, [file_id]

6、tempdb數(shù)據(jù)庫(kù)

代碼如下:

--tempdb數(shù)據(jù)庫(kù)的空間使用

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

tempdb中包含的對(duì)象：

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

可以位于創(chuàng)建對(duì)象的例程(存儲(chǔ)過(guò)程、觸發(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語(yǔ)句，

內(nèi)部對(duì)象可以在語(yǔ)句作用域中創(chuàng)建、刪除。

每個(gè)內(nèi)部對(duì)象至少需要9個(gè)頁(yè)面，一個(gè)IAM頁(yè)，一個(gè)區(qū)包含了8個(gè)頁(yè)。

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ù)頁(yè)的集合，包含了支持行版本控制功能的所需的數(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視圖沒(méi)有記錄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.通過(guò)模擬單獨(dú)的查詢、工作任務(wù)，監(jiān)控tempdb空間使用

3.通過(guò)模擬執(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.可以通過(guò)SQL Trace來(lái)跟蹤,但是由于不能預(yù)期造成大量使用tempdb語(yǔ)句在什么時(shí)候運(yùn)行,

而且SQL Trance操作比較昂貴,如果一直開著會(huì)產(chǎn)生大量的跟蹤文件,對(duì)硬盤的負(fù)擔(dān)也比較重,一般不用.

2.輕量級(jí)的監(jiān)控是通過(guò)一定時(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ì)象保留的頁(yè)數(shù)', ----包含已分配區(qū)中的未使用頁(yè)數(shù)

SUM (internal_object_reserved_page_count) as '內(nèi)部對(duì)象保留的頁(yè)數(shù)', --包含已分配區(qū)中的未使用頁(yè)數(shù)

SUM (version_store_reserved_page_count) as '版本存儲(chǔ)保留的頁(yè)數(shù)',

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

SUM(mixed_extent_page_count) as '文件的已分配混合區(qū)中:已分配頁(yè)和未分配頁(yè)' --包含IAM頁(yè)

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)行的語(yǔ)句

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中頁(yè)分配和釋放活動(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

更多信息請(qǐng)查看IT技術(shù)專欄