SQL Server DBA 实用的 100 条命令（建议收藏）

SERVERPROPERTY('ProductVersion') AS Version,

SERVERPROPERTY('ProductLevel') AS Level,

SERVERPROPERTY('Edition') AS Edition,

SERVERPROPERTY('EngineEdition') AS EngineEdition;

FROM sys.dm_os_sys_info;

cpu_count,

physical_memory_kb/1024 AS memory_mb,

virtual_machine_type_desc

FROM sys.dm_os_sys_info;

name,

value_in_use

FROM sys.configurations

WHERE name='max server memory (MB)';

name,

state_desc,

recovery_model_desc,

compatibility_level

FROM sys.databases;

name,

create_date

FROM sys.databases;

DB_NAME(database_id) AS database_name,

name,

physical_name,

size*8/1024 AS size_mb

FROM sys.master_files;

DB_NAME(database_id) AS database_name,

name,

type_desc,

size*8/1024 AS size_mb

FROM sys.master_files;

DB_NAME(database_id) AS database_name,

SUM(size)*8/1024 AS size_mb

FROM sys.master_files

GROUP BY database_id

ORDER BY size_mb DESC;

DB_NAME(database_id),

name,

size*8/1024 AS log_mb

FROM sys.master_files

WHERE type_desc='LOG';

OBJECT_NAME(object_id) AS table_name,

SUM(reserved_page_count)*8/1024 AS size_mb

FROM sys.dm_db_partition_stats

GROUP BY object_id

ORDER BY size_mb DESC;

OBJECT_NAME(object_id),

SUM(rows)

FROM sys.partitions

WHERE index_id IN (0,1)

GROUP BY object_id;

name,

growth,

is_percent_growth

FROM sys.database_files;

name,

recovery_model_desc

FROM sys.databases;

FROM sys.dm_exec_sessions;

session_id,

status,

command,

cpu_time,

total_elapsed_time,

wait_type,

blocking_session_id

FROM sys.dm_exec_requests;

r.session_id,

t.text

FROM sys.dm_exec_requests r

CROSS APPLY sys.dm_exec_sql_text(r.sql_handle)t;

login_name,

COUNT(*)

FROM sys.dm_exec_sessions

GROUP BY login_name;

host_name,

program_name,

login_name,

COUNT(*)

FROM sys.dm_exec_sessions

GROUP BY

host_name,

program_name,

login_name;

session_id,

start_time,

total_elapsed_time/1000 AS seconds,

command

FROM sys.dm_exec_requests

ORDER BY total_elapsed_time DESC;

session_id,

cpu_time,

logical_reads

FROM sys.dm_exec_requests

ORDER BY cpu_time DESC;

session_id,

wait_type,

wait_time,

blocking_session_id

FROM sys.dm_exec_requests

WHERE wait_type IS NOT NULL;

session_id,

blocking_session_id,

wait_type

FROM sys.dm_exec_requests

WHERE blocking_session_id<>0;

blocking_session_id,

session_id,

wait_type,

wait_time

FROM sys.dm_exec_requests

WHERE blocking_session_id > 0;

session_id,

status,

last_request_start_time

FROM sys.dm_exec_sessions

WHERE status='sleeping';

name,

value_in_use

FROM sys.configurations

WHERE name='user connections';

wait_type,

waiting_tasks_count,

wait_time_ms

FROM sys.dm_os_wait_stats

ORDER BY wait_time_ms DESC;

FROM sys.dm_tran_locks;

FROM sys.dm_tran_active_transactions;

session_id,

transaction_id,

transaction_begin_time

FROM sys.dm_tran_session_transactions;

request_session_id,

resource_type,

request_mode,

request_status

FROM sys.dm_tran_locks

WHERE request_status='WAIT';

blocking_session_id,

session_id,

wait_type,

wait_time

FROM sys.dm_exec_requests

WHERE blocking_session_id<>0;

FROM system_health.session_targets;

COUNT(*)

FROM sys.dm_tran_active_transactions;

FROM sys.dm_tran_version_store_space_usage;

FROM sys.dm_db_file_space_usage;

COUNT(*)

FROM sys.dm_tran_locks;

wait_type,

resource_description

FROM sys.dm_os_waiting_tasks;

*

FROM sys.dm_xe_sessions;

qs.total_worker_time,

qt.text

FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt

ORDER BY qs.total_worker_time DESC;

execution_count,

text

FROM sys.dm_exec_query_stats

CROSS APPLY sys.dm_exec_sql_text(sql_handle)

ORDER BY execution_count DESC;

total_elapsed_time/execution_count,

text

FROM sys.dm_exec_query_stats

CROSS APPLY sys.dm_exec_sql_text(sql_handle)

ORDER BY 1 DESC;

FROM sys.dm_exec_cached_plans;

GO

SELECT *

FROM table_name;

GO

SET SHOWPLAN_XML OFF;

FROM sys.query_store_query;

*

FROM sys.query_store_runtime_stats

ORDER BY avg_duration DESC;

total_logical_reads,

text

FROM sys.dm_exec_query_stats

CROSS APPLY sys.dm_exec_sql_text(sql_handle)

ORDER BY total_logical_reads DESC;

total_physical_reads,

text

FROM sys.dm_exec_query_stats

CROSS APPLY sys.dm_exec_sql_text(sql_handle)

ORDER BY total_physical_reads DESC;

SUM(size_in_bytes)/1024/1024 AS MB

FROM sys.dm_exec_cached_plans;

FROM sys.indexes;

OBJECT_NAME(object_id),

avg_fragmentation_in_percent

FROM sys.dm_db_index_physical_stats

(

NULL,NULL,NULL,NULL,'LIMITED'

);

ON table_name

REBUILD;

ON table_name

REORGANIZE;

FROM sys.dm_db_missing_index_details;

FROM sys.dm_db_index_usage_stats;

FROM sys.dm_db_index_usage_stats

WHERE user_seeks=0

AND user_scans=0;

name,

STATS_DATE(object_id,index_id)

FROM sys.indexes;

ON table_name(column_name);

FROM sys.dm_hadr_availability_replica_states;

FROM sys.dm_hadr_database_replica_states;

database_id,

log_send_queue_size,

redo_queue_size

FROM sys.dm_hadr_database_replica_states;

FROM sys.availability_groups;

FROM sys.availability_group_listeners;

FROM sys.availability_replicas;

synchronization_health_desc

FROM sys.dm_hadr_availability_replica_states;

database_name,

backup_start_date,

backup_finish_date,

type

FROM msdb.dbo.backupset

ORDER BY backup_finish_date DESC;

TO DISK='D:\backup\db.bak';

TO DISK='D:\backup\db.trn';

FROM DISK='D:\backup\db.bak';

FROM msdb.dbo.backupset

ORDER BY backup_finish_date DESC;

FROM msdb.dbo.restorehistory;

FROM sys.server_principals;

FROM sys.database_principals;

FROM sys.database_permissions;

WITH PASSWORD='Password@123';

FOR LOGIN user1;

ADD MEMBER user1;

ADD MEMBER user1;

FROM msdb.dbo.sysjobs;

FROM msdb.dbo.sysjobhistory

WHERE run_status<>1;

FROM sys.dm_db_file_space_usage;

FROM sys.dm_os_memory_clerks;

FROM sys.dm_os_schedulers;

FROM sys.dm_io_virtual_file_stats(NULL,NULL);

wait_type,

wait_time_ms

FROM sys.dm_os_wait_stats

ORDER BY wait_time_ms DESC;