SQLServerDBA日常检查常用SQL
SQL 常用 检查 日常
2023-06-13 09:15:19 时间
1、数据库
--所有数据库的大小
execsp_helpdb
--所有数据库的状态
selectname,
user_access_desc, --用户访问模式
state_desc, --数据库状态
recovery_model_desc, --恢复模式
page_verify_option_desc, --页检测选项
log_reuse_wait_desc --日志重用等待
fromsys.databases
--某个数据库的大小:按页面计算空间，有性能影响，基本准确，有时不准确
usetest
go
execsp_spaceused
go
--可以@updateusage="true",会运行dbccupdateusage
execsp_spaceused @updateusage="true"
--对某个数据库,显示目录视图中的页数和行数错误并更正
DBCCUPDATEUSAGE("test")
2、数据文件
--查看某个数据库中的所有文件及大小
sp_helpfile
--查看所有文件所在数据库、路径、状态、大小
selectdb_name(database_id)dbname,
type_desc, --数据还是日志
name, --文件的逻辑名称
physical_name, --文件的物理路径
state_desc, --文件状态
size*8.0/1024as"文件大小（MB）"
fromsys.master_files
--按区extent计算空间，没有性能影响，基本准确,把TotalExtents*64/1024,单位为MB
--同时也适用于计算tempdb的文件大小，但不包括日志文件
dbccshowfilestats
3、日志文件
--查看日志文件所在数据库、路径、状态、大小
selectdb_name(database_id)dbname,
type_desc, --数据还是日志
name, --文件的逻辑名称
physical_name, --文件的物理路径
state_desc, --文件状态
size*8.0/1024as"文件大小（MB）"
fromsys.master_files
wheretype_desc="LOG"
--所有数据库的日志的大小,空间使用率
dbccsqlperf(logspace)
4、数据文件、日志文件的I/O统计信息
--数据和日志文件的I/O统计信息,包含文件大小
selectdatabase_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 --文件在磁盘上所占用的实际字节数
fromsys.dm_io_virtual_file_stats(db_id("test"), --数据库id
1) --数据文件id
unionall
selectdatabase_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 --文件在磁盘上所占用的实际字节数
fromsys.dm_io_virtual_file_stats(db_id("test"), --数据库id
2) --日志文件id
5、对象，包括：表、索引、索引视图等
--不一定准确:某个表的行数,保留大小，数据大小，索引大小，未使用大小
execsp_spaceused@objname="temp_lock"
--准确:但有性能影响
execsp_spaceused@objname="temp_lock",
@updateusage="true"
--按页统计，没有性能影响，有时不准确
/*======================================================
一次计算多个对象的空间使用情况
sys.dm_db_partition_stats返回当前数据库中每个分区(表和索引)的页和行计数信息
========================================================*/
selecto.name,
sum(p.reserved_page_count)asreserved_page_count,--保留页，包含表和索引
sum(p.used_page_count)asused_page_count, --已使用页，包含表和索引
sum(casewhenp.index_id<2
thenp.in_row_data_page_count+
p.lob_used_page_count+
p.row_overflow_used_page_count
elsep.lob_used_page_count+
p.row_overflow_used_page_count
end)asdata_pages, --数据页,包含表中数据、索引中的lob数据、索引中的行溢出数据
sum(casewhenp.index_id<2
thenp.row_count
else0
end)asrow_counts --数据行数，包含表中的数据行数，不包含索引中的数据条目数
fromsys.dm_db_partition_statsp
innerjoinsys.objectso
onp.object_id=o.object_id
wherep.object_id=object_id("表名")
groupbyo.name
--按页或区统计，有性能影响，准确
--显示当前数据库中所有的表或视图的数据和索引的空间信息
--包含：逻辑碎片、区碎片(碎片率)、平均页密度
dbccshowcontig(temp_lock)
--SQLServer推荐使用的动态性能函数，准确
select*
fromsys.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"模式会扫描所有页,返回所有统计信息
)
--查找哪些对象是需要重建的
usetest
go
ifOBJECT_ID("extentinfo")isnotnull
droptableextentinfo
go
createtableextentinfo
( [file_id]smallint,
page_idint,
pg_allocint,
ext_sizeint,
obj_idint,
index_idint,
partition_numberint,
partition_idbigint,
iam_chain_typevarchar(50),
pfs_bytesvarbinary(10)
)
go
/*====================================================================
查询到的盘区信息是数据库的数据文件的盘区信息，日志文件不以盘区为单位
命令格式: DBCCEXTENTINFO（dbname,tablename,indexid）
DBCCEXTENTINFO("[test]","extentinfo",0)
======================================================================*/
insertextentinfo
exec("dbccextentinfo(""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
fromextentinfo
orderbyfile_id,
OBJ_ID,
index_id,
partition_id,
ext_size
/*=====================================================================================================
数据库的数据文件的盘区信息,通过计算每个对象理论上区的数量和实际数量,如果两者相差很大,
那就应该重建对象.
1.每一条记录就是一个区
2.如果pg_alloc比ext_size小，也就是实际每个区分配的页数小于理论上这个区的页数，
那么就会多一条记录，把本应该属于这个区的页放到多出来的这条记录对应的区中，
那么原来只有一条记录(也就是一个区)，现在就有2条记录(也就是2个区)，
导致实际的区数量2大于理论上的区数量1.
========================================================================================================*/
selectfile_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.00as"理论上的区个数/实际区的个数"
fromextentinfo
groupbyfile_id,
obj_id,
index_id,
partition_id,
ext_size
havingceiling(sum(pg_alloc)*1.0/ext_size)<count(*)
--过滤:理论上区的个数<实际区的个数,也就是百分比小于100%的
orderbypartition_id,obj_id,index_id,[file_id]
6、tempdb数据库
--tempdb数据库的空间使用
/*======================================================
tempdb中包含的对象：
用户对象：是用户显式创建的，这些对象位于用户会话的作用域，
可以位于创建对象的例程(存储过程、触发器、函数)的作用域中。
1.用户定义的表、索引
2.系统表、索引
3.全局临时表、索引
4.局部临时表、索引
5.表变量
6.表值函数中返回的表
内部对象：是根据需要由SQLServer数据库引擎创建的，用于处理SQLServer语句，
内部对象可以在语句作用域中创建、删除。
每个内部对象至少需要9个页面，一个IAM页，一个区包含了8个页。
1.游标、假脱机操作、临时的大型对象(LOB)，存储的工作表
2.哈希联接、哈希聚合操作的工作文件
3.如果设置了sort_in_tempdb选项，那么创建、重新生成索引的重建排序结果存放在tempdb；
groupby、orderby、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.可以通过SQLTrace来跟踪,但是由于不能预期造成大量使用tempdb语句在什么时候运行,
而且SQLTrance操作比较昂贵,如果一直开着会产生大量的跟踪文件,对硬盘的负担也比较重,一般不用.
2.轻量级的监控是通过一定时间间隔运行能够监控系统运行的dbcc命令、动态性能视图-函数，
把结果记录在文件中，这对于很繁忙的系统是不错的选择。
========================================================*/
SelectDB_NAME(database_id)asDB,
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页
Fromsys.dm_db_file_space_usage
Wheredatabase_id=2
groupbyDB_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
fromsys.dm_db_session_space_usage t1 --反映每个session的累计空间申请
innerjoinsys.dm_exec_sessionsast2
ont1.session_id=t2.session_id
innerjoinsys.dm_exec_requestst3
ont2.session_id=t3.session_id
crossapplysys.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>0or
t1.user_objects_dealloc_page_count>0
--返回tempdb中页分配和释放活动，
--只有当任务正在运行时，sys.dm_db_task_space_usage才会返回值
--在请求完成时，这些值将按session聚合体现在SYS.dm_db_session_space_usage
selectt.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
fromsys.dm_db_task_space_usaget
innerjoinsys.dm_exec_sessionse
ont.session_id=e.session_id
innerjoinsys.dm_exec_requests r
ont.session_id=r.session_idand
t.request_id=r.request_id
相关文章
- WEBGOAT.2.2 SQL Injection (advanced)
- mysql基本sql语句大全(基础用语篇)_mysql常用查询语句
- sql server 2012 报表开发(2) reporting service 中制作分组折叠式报表
- 什么是pl/sql语句_过程化SQL有三种循环结构
- SQL注入-报错注入[通俗易懂]
- SQL Server连接池：优化数据库性能的必备武器（sqlserver连接池）
- Oracle 视图 V$SQL_CS_SELECTIVITY 官方解释，作用，如何使用详细说明
- 语句MySQL中常用SQL语句实用指南（mysql常用sql）
- Oracle数据库删除数据的常用SQL语句（oracle删除sql）
- MySQL查看SQL日志：巧用技巧（mysql查看sql日志）
- pl/sql与Oracle PL/SQL集成的ASM技术。（asmoracle）
- 数据合法性使用SQL语句判断MSSQL数据合法性（sql判断mssql）
- Oracle SQL中外键的使用与重要性（oracle sql外键）
- MySQL SQL日志技术指南（mysql sql 日志）
- 脚本MSSQL自动生成SQL脚本：实现数据库快速保存（mssql生成sql）
- 如何用Oracle写出良好的SQL语句（oracle写sql提示）
- Oracle中SQL的分类与应用（oracle中sql分类）
- 运行oracle，解决SQL锁定的技巧（oracle sql锁）
- 使用Oracle SQL实现数据表查询（oracle sql查表）
- Oracle SQL字符串截断技术研究（oracle sql截断）
- mysql建表常用sql语句个人经验分享