1、數(shù)據(jù)庫
代碼如下:
--所有數(shù)據(jù)庫的大小
exec sp_helpdb
--所有數(shù)據(jù)庫的狀態(tài)
select name,
user_access_desc, --用戶訪問模式
state_desc, --數(shù)據(jù)庫狀態(tài)
recovery_model_desc, --恢復(fù)模式
page_verify_option_desc, --頁檢測(cè)選項(xiàng)
log_reuse_wait_desc --日志重用等待
from sys.databases
--某個(gè)數(shù)據(jù)庫的大小:按頁面計(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ù)庫,顯示目錄視圖中的頁數(shù)和行數(shù)錯(cuò)誤并更正
DBCC UPDATEUSAGE('test')
2、數(shù)據(jù)文件
代碼如下:
--查看某個(gè)數(shù)據(jù)庫中的所有文件及大小
sp_helpfile
--查看所有文件所在數(shù)據(jù)庫、路徑、狀態(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
--按區(qū)extent計(jì)算空間，沒有性能影響，基本準(zhǔn)確,把TotalExtents*64/1024,單位為MB
--同時(shí)也適用于計(jì)算tempdb的文件大小，但不包括日志文件
dbcc showfilestats
3、日志文件
代碼如下:
--查看日志文件所在數(shù)據(jù)庫、路徑、狀態(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ù)庫的日志的大小,空間使用率
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)以來的毫秒數(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ù)庫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ù)庫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'
--按頁統(tǒng)計(jì)，沒有性能影響，有時(shí)不準(zhǔn)確
/*======================================================
一次計(jì)算多個(gè)對(duì)象的空間使用情況
sys.dm_db_partition_stats返回當(dāng)前數(shù)據(jù)庫中每個(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ù)庫中所有的表或視圖的數(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ù)庫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ù)庫的數(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ù)庫的數(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]
6、tempdb數(shù)據(jù)庫
代碼如下:
--tempdb數(shù)據(jù)庫的空間使用
/*======================================================
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ù)庫引擎創(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ù)庫并發(fā)性能的新功能。
1.公用版本存儲(chǔ)區(qū)：在使用快照隔離級(jí)別、已提交讀隔離級(jí)別的數(shù)據(jù)庫中，由數(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)增長
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)增長.
另外,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
更多信息請(qǐng)查看IT技術(shù)專欄
版權(quán)所有:易賢網(wǎng)
公眾號(hào)
事業(yè)單位
當(dāng)前位置：
公考類
招聘類
各類考試