SQL Server DBA日常点検常用SQL
13976 ワード
1、データベース
2、データファイル
3、ログファイル
4、データファイル、ログファイルのI/O統計
5、オブジェクト、例えば:テーブル、インデックス、インデックスビューなど
6、tempdbデータベース
--
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