SQL Server 运维命令 100 条

1SELECT @@VERSION;

1SELECT
2SERVERPROPERTY('ProductVersion') AS Version,
3SERVERPROPERTY('ProductLevel') AS Level,
4SERVERPROPERTY('Edition') AS Edition,
5SERVERPROPERTY('EngineEdition') AS EngineEdition;

1SELECT SERVERPROPERTY('ServerName');

1SELECT GETDATE();

1SELECT sqlserver_start_time
2FROM sys.dm_os_sys_info;

1SELECT
2cpu_count,
3physical_memory_kb/1024 AS memory_mb,
4virtual_machine_type_desc
5FROM sys.dm_os_sys_info;

1SELECT
2name,
3value_in_use
4FROM sys.configurations
5WHERE name='max server memory (MB)';

1SELECT DB_NAME();

1SELECT
2name,
3state_desc,
4recovery_model_desc,
5compatibility_level
6FROM sys.databases;

1SELECT
2name,
3create_date
4FROM sys.databases;

1SELECT
2DB_NAME(database_id) AS database_name,
3name,
4physical_name,
5size*8/1024 AS size_mb
6FROM sys.master_files;

1SELECT
2DB_NAME(database_id) AS database_name,
3name,
4type_desc,
5size*8/1024 AS size_mb
6FROM sys.master_files;

1SELECT
2DB_NAME(database_id) AS database_name,
3SUM(size)*8/1024 AS size_mb
4FROM sys.master_files
5GROUP BY database_id
6ORDER BY size_mb DESC;

1SELECT
2DB_NAME(database_id),
3name,
4size*8/1024 AS log_mb
5FROM sys.master_files
6WHERE type_desc='LOG';

1DBCC SQLPERF(LOGSPACE);

1EXEC sp_spaceused;

1SELECT TOP 20
2OBJECT_NAME(object_id) AS table_name,
3SUM(reserved_page_count)*8/1024 AS size_mb
4FROM sys.dm_db_partition_stats
5GROUP BY object_id
6ORDER BY size_mb DESC;

1SELECT
2OBJECT_NAME(object_id),
3SUM(rows)
4FROM sys.partitions
5WHERE index_id IN (0,1)
6GROUP BY object_id;

1SELECT
2name,
3growth,
4is_percent_growth
5FROM sys.database_files;

1SELECT
2name,
3recovery_model_desc
4FROM sys.databases;

1SELECT *
2FROM sys.dm_exec_sessions;

1SELECT
2session_id,
3status,
4command,
5cpu_time,
6total_elapsed_time,
7wait_type,
8blocking_session_id
9FROM sys.dm_exec_requests;

1SELECT
2r.session_id,
3t.text
4FROM sys.dm_exec_requests r
5CROSS APPLY sys.dm_exec_sql_text(r.sql_handle)t;

1SELECT
2login_name,
3COUNT(*)
4FROM sys.dm_exec_sessions
5GROUP BY login_name;

1SELECT
2host_name,
3program_name,
4login_name,
5COUNT(*)
6FROM sys.dm_exec_sessions
7GROUP BY
8host_name,
9program_name,
10login_name;

1SELECT
2session_id,
3start_time,
4total_elapsed_time/1000 AS seconds,
5command
6FROM sys.dm_exec_requests
7ORDER BY total_elapsed_time DESC;

1SELECT TOP 20
2session_id,
3cpu_time,
4logical_reads
5FROM sys.dm_exec_requests
6ORDER BY cpu_time DESC;

1SELECT
2session_id,
3wait_type,
4wait_time,
5blocking_session_id
6FROM sys.dm_exec_requests
7WHERE wait_type IS NOT NULL;

1SELECT
2session_id,
3blocking_session_id,
4wait_type
5FROM sys.dm_exec_requests
6WHERE blocking_session_id<>0;

1SELECT
2blocking_session_id,
3session_id,
4wait_type,
5wait_time
6FROM sys.dm_exec_requests
7WHERE blocking_session_id > 0;

1SELECT
2session_id,
3status,
4last_request_start_time
5FROM sys.dm_exec_sessions
6WHERE status='sleeping';

1KILL 57;

1SELECT
2name,
3value_in_use
4FROM sys.configurations
5WHERE name='user connections';

1EXEC xp_readerrorlog;

1SELECT TOP 20
2wait_type,
3waiting_tasks_count,
4wait_time_ms
5FROM sys.dm_os_wait_stats
6ORDER BY wait_time_ms DESC;

1SELECT *
2FROM sys.dm_tran_locks;

1DBCC OPENTRAN;

1SELECT *
2FROM sys.dm_tran_active_transactions;

1SELECT
2session_id,
3transaction_id,
4transaction_begin_time
5FROM sys.dm_tran_session_transactions;

1SELECT
2request_session_id,
3resource_type,
4request_mode,
5request_status
6FROM sys.dm_tran_locks
7WHERE request_status='WAIT';

1SELECT
2blocking_session_id,
3session_id,
4wait_type,
5wait_time
6FROM sys.dm_exec_requests
7WHERE blocking_session_id<>0;

1SELECT *
2FROM system_health.session_targets;

1DBCC USEROPTIONS;

1SELECT
2COUNT(*)
3FROM sys.dm_tran_active_transactions;

1SELECT *
2FROM sys.dm_tran_version_store_space_usage;

1SELECT *
2FROM sys.dm_db_file_space_usage;

1SELECT
2COUNT(*)
3FROM sys.dm_tran_locks;

1SELECT
2wait_type,
3resource_description
4FROM sys.dm_os_waiting_tasks;

1SELECT
2*
3FROM sys.dm_xe_sessions;

1KILL session_id;

1SELECT TOP 20
2qs.total_worker_time,
3qt.text
4FROM sys.dm_exec_query_stats qs
5CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
6ORDER BY qs.total_worker_time DESC;

1SELECT TOP 20
2execution_count,
3text
4FROM sys.dm_exec_query_stats
5CROSS APPLY sys.dm_exec_sql_text(sql_handle)
6ORDER BY execution_count DESC;

1SELECT TOP 20
2total_elapsed_time/execution_count,
3text
4FROM sys.dm_exec_query_stats
5CROSS APPLY sys.dm_exec_sql_text(sql_handle)
6ORDER BY 1 DESC;

1SELECT *
2FROM sys.dm_exec_cached_plans;

1SET SHOWPLAN_XML ON;
2GO
3
4SELECT *
5FROM table_name;
6
7GO
8SET SHOWPLAN_XML OFF;

1SELECT *
2FROM sys.query_store_query;

1SELECT TOP 20
2*
3FROM sys.query_store_runtime_stats
4ORDER BY avg_duration DESC;

1SELECT TOP 20
2total_logical_reads,
3text
4FROM sys.dm_exec_query_stats
5CROSS APPLY sys.dm_exec_sql_text(sql_handle)
6ORDER BY total_logical_reads DESC;

1SELECT TOP 20
2total_physical_reads,
3text
4FROM sys.dm_exec_query_stats
5CROSS APPLY sys.dm_exec_sql_text(sql_handle)
6ORDER BY total_physical_reads DESC;

1SELECT
2SUM(size_in_bytes)/1024/1024 AS MB
3FROM sys.dm_exec_cached_plans;

1SELECT *
2FROM sys.indexes;

1SELECT
2OBJECT_NAME(object_id),
3avg_fragmentation_in_percent
4FROM sys.dm_db_index_physical_stats
5(
6NULL,NULL,NULL,NULL,'LIMITED'
7);

1ALTER INDEX ALL
2ON table_name
3REBUILD;

1ALTER INDEX ALL
2ON table_name
3REORGANIZE;

1UPDATE STATISTICS table_name;

1SELECT *
2FROM sys.dm_db_missing_index_details;

1SELECT *
2FROM sys.dm_db_index_usage_stats;

1SELECT *
2FROM sys.dm_db_index_usage_stats
3WHERE user_seeks=0
4AND user_scans=0;

1SELECT
2name,
3STATS_DATE(object_id,index_id)
4FROM sys.indexes;

1CREATE INDEX idx_name
2ON table_name(column_name);

1SELECT *
2FROM sys.dm_hadr_availability_replica_states;

1SELECT *
2FROM sys.dm_hadr_database_replica_states;

1SELECT
2database_id,
3log_send_queue_size,
4redo_queue_size
5FROM sys.dm_hadr_database_replica_states;

1SELECT *
2FROM sys.availability_groups;

1SELECT *
2FROM sys.availability_group_listeners;

1SELECT *
2FROM sys.availability_replicas;

1SELECT
2synchronization_health_desc
3FROM sys.dm_hadr_availability_replica_states;

1SELECT
2database_name,
3backup_start_date,
4backup_finish_date,
5type
6FROM msdb.dbo.backupset
7ORDER BY backup_finish_date DESC;

1BACKUP DATABASE dbname
2TO DISK='D:\backup\db.bak';

1BACKUP LOG dbname
2TO DISK='D:\backup\db.trn';

1RESTORE DATABASE dbname
2FROM DISK='D:\backup\db.bak';

1SELECT TOP 10 *
2FROM msdb.dbo.backupset
3ORDER BY backup_finish_date DESC;

1SELECT *
2FROM msdb.dbo.restorehistory;

1SELECT *
2FROM sys.server_principals;

1SELECT *
2FROM sys.database_principals;

1SELECT *
2FROM sys.database_permissions;

1CREATE LOGIN user1
2WITH PASSWORD='Password@123';

1CREATE USER user1
2FOR LOGIN user1;

1ALTER ROLE db_datareader
2ADD MEMBER user1;

1ALTER ROLE db_datawriter
2ADD MEMBER user1;

1DROP USER user1;

1DROP LOGIN user1;

1SELECT *
2FROM msdb.dbo.sysjobs;

1SELECT *
2FROM msdb.dbo.sysjobhistory
3WHERE run_status<>1;

1EXEC xp_readerrorlog;

1SELECT *
2FROM sys.dm_db_file_space_usage;

1SELECT *
2FROM sys.dm_os_memory_clerks;

1SELECT *
2FROM sys.dm_os_schedulers;

1SELECT *
2FROM sys.dm_io_virtual_file_stats(NULL,NULL);

1SELECT TOP 20
2wait_type,
3wait_time_ms
4FROM sys.dm_os_wait_stats
5ORDER BY wait_time_ms DESC;