SQL Server DBA日常检查常用SQL

SQL Server DBA日常检查常用SQL

作为SQL Server数据库管理员（DBA），日常检查是确保数据库运行稳定和高效的重要任务之一，以下是一些常用的SQL语句，可以帮助DBA进行日常检查和维护工作。

1、检查数据库空间使用情况

可以使用以下SQL语句来检查数据库的空间使用情况：

SELECT 
    DB_NAME(database_id) AS DatabaseName,
    CAST(SUM(size) * 8.0 / 1024 / 1024 AS DECIMAL(18, 2)) AS TotalSizeMB,
    CAST(SUM(FILEPROPERTY(name, 'SpaceUsed') * 8.0 / 1024 / 1024) AS DECIMAL(18, 2)) AS UsedSizeMB,
    CAST(SUM(FILEPROPERTY(name, 'SpaceFree') * 8.0 / 1024 / 1024) AS DECIMAL(18, 2)) AS FreeSizeMB,
    CAST((SUM(FILEPROPERTY(name, 'SpaceUsed')) * 100.0 / SUM(size)) AS DECIMAL(18, 2)) AS SpaceUsagePercentage
FROM 
    sys.master_files
GROUP BY 
    database_id;

这个查询将返回每个数据库的名称、总大小、已使用大小、剩余大小以及空间使用百分比，通过定期运行此查询，DBA可以及时发现数据库空间不足的问题，并采取相应的措施，如增加数据文件或优化表结构。

2、检查索引碎片情况

索引碎片是指索引中的页不再按照顺序排列，这会导致查询性能下降，可以使用以下SQL语句来检查索引碎片情况：

SELECT 
    OBJECT_NAME(object_id) AS TableName,
    index_id,
    partition_number,
    avg_page_space_used_in_percent,
    page_count,
    record_count,
    forwarded_record_count,
    minimum_record_size_in_bytes,
    maximum_record_size_in_bytes,
    avg_record_size_in_bytes,
    forwarded_record_count * 8.0 / page_count AS forwarded_records_pct,
    page_count * 8.0 / 1024 AS pages_mb,
    record_count * 8.0 / page_count AS records_per_page,
    record_count * 8.0 / (page_count * 1.0) AS records_mb,
    avg_page_space_used_in_percent * page_count * 8.0 / 1024 AS used_pages_mb,
    CASE WHEN indexproperty(object_id('tempdb..tmp'), 'IsClustered', 'IsSinglePart') = 1 THEN 'CLUSTERED' ELSE 'NONCLUSTERED' END AS indextype,
    avg_page_space_used_in_percent > 30 AS fragmented
FROM 
    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') AS iptrs;

这个查询将返回每个表的索引名称、分区号、平均页使用百分比、页数、记录数、前向记录数、最小记录大小、最大记录大小、平均记录大小、前向记录百分比、页数（MB）、每页记录数、每页记录（MB）、已使用的页数（MB）以及是否碎片，通过定期运行此查询，DBA可以发现索引碎片问题，并采取相应的措施，如重建索引或重新组织表。

3、检查锁和死锁情况

锁和死锁是数据库中常见的问题，可以使用以下SQL语句来检查锁和死锁情况：

-检查当前会话的锁和阻塞情况
SELECT 
    tl.request_session_id AS SPID,
    tl.resource_database_id AS DBID,
    tl.resource_type AS LockType,
    tl.resource_description AS LockDescription,
    tl.request_mode AS LockMode,
    tl.request_status AS LockStatus,
    tl.request_owner_type AS LockOwnerType,
    tl.request_owner_id AS LockOwnerID,
    tl.request_owner AS LockOwnerSID,
    tl.requesting_session_id AS BlockingSPID,
    tl.blocking_session_id AS BlockingSessionID,
    tl.waittime AS LockWaitTimeMS,
    tl.getdate() AS LockTimestamp,
    tl.objtype AS LockObjectType,
    tl.objname AS LockObjectName,
    tl.objid AS LockObjectID,
    tl.trancount AS LockTransactionCount,
    tl.isupdate as IsUpdateLock,
    tl.islock as IsExclusiveLock,
    tl.hasexpl as HasExplicitLocks,
    tlp.query as LockQueryText,
    qp.queryplan as LockQueryPlanText,
    qp.queryhash as LockQueryHashValue,
    qp.statementtext as LockStatementText,
    qp.statementoption as LockStatementOptionText,
    qp.statementcompatibility as LockStatementCompatibilityLevelText,
    qp.compileoption as LockCompileOptionText,
    qp.compiletype as LockCompileTypeText,
    qp.plangenerationnum as LockPlanGenerationNumText,
    qp.planhandle as LockPlanHandleText,
    qp.relop as LockRelopText,
    qp.operator as LockOperatorText,
    qp.predicate as LockPredicateText,
    qp.table as LockTableText,
    qp.function as LockFunctionText,
    qp.module as LockModuleText,
    qp.returns as LockReturnsText,
    qp.parameter as LockParameterText,
    qp.systemtype as LockSystemTypeText,
    qp.language as LockLanguageText,
    qp.unicodeasn as LockUnicodeAsnText,
    qp.collation as CollationText, -for SQL Server 2005 and later versions only!
    qp.referencingcolumnasn as RefColumnAsnText -for SQL Server 2005 and later versions only!
FROM sys.dm_tran_locks l INNER JOIN sys.dm_exec_sessions e ON l.requesting_session_id = e.session_id CROSS APPLY sys.dm_exec_sqltext(e.sql_handle) tlp INNER JOIN sys.dm_exec_queryplan qp ON l.plan_handle = qp.plan_handle; -for SQL Server 2005 and later versions only! -where l.requesting_session_id = @@SPID; -uncomment this line to filter by current session only! -order by l.requesting