SQL Server DBA平常檢討經常使用SQL。本站提示廣大學習愛好者：（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
