Essential SQL Server Queries: From Server Properties to Index Management

This guide compiles over 80 practical SQL Server queries and commands that address common administrative tasks and deep‑dive diagnostics.

Server‑level information

SELECT SERVERPROPERTY('propertyname')

– retrieve any server property. SELECT * FROM sys.configurations WHERE name='XX' – view a specific configuration. sp_configure 'XX','0'; RECONFIGURE WITH OVERRIDE; – change a configuration value and force it with WITH OVERRIDE when the value is outside the allowed range.

SELECT SERVERPROPERTY('machinename'), @@SERVERNAME, SERVERPROPERTY('edition'), @@VERSION

– get machine name, instance name, edition and version before upgrades.

System objects and views

SELECT * FROM sys.all_objects

– objects in the current database. SELECT * FROM sys.sysobjects – legacy view for current database objects. SELECT * FROM sys.databases – list all databases with online status. SELECT * FROM sys.master_files – files across all databases (global view).

Database‑specific file views: sys.database_files (per‑database) and sys.master_files (system‑wide).

MSDB‑only system tables: dbo.backupset, dbo.log_shipping_secondary, dbo.restorehistory, dbo.sysjobs, dbo.sysjobhistory.

Lock and session monitoring

sp_lock

, sp_who, sp_who2, sp_helptext – basic lock and session info.

Extended session query examples using sys.dm_exec_requests, sys.dm_os_waiting_tasks, and sys.dm_tran_locks to list blocked sessions, resource types, and wait times.

Identify which tables are locked and by which SPID:

SELECT request_session_id spid, OBJECT_NAME(resource_associated_entity_id) tableName FROM sys.dm_tran_locks WHERE resource_type='OBJECT' ORDER BY request_session_id;

Backup and restore monitoring

Backup progress:

SELECT DB_NAME(database_id), percent_complete, ... FROM SYS.DM_EXEC_REQUESTS WHERE command LIKE 'Backup%';

Restore progress: same query with LIKE 'RESTORE%'.

Recent backup info:

SELECT database_name, type, MAX(backup_finish_date) FROM msdb.dbo.backupset GROUP BY database_name, type;

Generate restore statements for recent backups using msdb.dbo.backupset and msdb.dbo.backupmediafamily.

Log backup queries and automatic restore‑log script generation.

Always On and Availability Groups

Check replica health:

SELECT dc.database_name, d.synchronization_health_desc, d.synchronization_state_desc, d.database_state_desc FROM sys.dm_hadr_database_replica_states d JOIN sys.availability_databases_cluster dc ON d.group_database_id=dc.group_database_id AND d.is_local=1;

Mirror status:

SELECT db_name(database_id), mirroring_state_desc, mirroring_role_desc, mirroring_partner_name, mirroring_partner_instance FROM sys.database_mirroring;

Cluster members, node map, WSFC info, listeners, and network details via sys.dm_hadr_cluster_members, sys.dm_hadr_instance_node_map, sys.dm_hadr_cluster, sys.availability_group_listeners, sys.dm_hadr_cluster_networks.

Log send queue size and delay for secondary replicas:

SELECT db_name(database_id), log_send_queue_size/1024 AS delay_M FROM sys.dm_hadr_database_replica_states WHERE is_primary_replica=0;

Job and Agent management

Check if SQL Agent is running:

IF EXISTS (SELECT TOP 1 1 FROM sys.sysprocesses WHERE program_name='SQLAgent - Generic Refresher') SELECT 'Running' ELSE SELECT 'Not Running';

List all jobs and their run status using msdb.dbo.sysjobs and msdb.dbo.sysjobactivity.

Start a job: EXECUTE msdb.dbo.sp_start_job N'job_name'; Find the last successful run time of a job:

SELECT TOP 1 CONVERT(DATETIME, RTRIM(run_date)) + ((run_time/10000*3600)+(run_time%10000)/100*60+(run_time%10000)%100)/86399.9964 FROM msdb.dbo.sysjobhistory WHERE job_id=... AND step_id=0 AND run_status=1 ORDER BY 1 DESC;

Identify which jobs call a specific stored procedure via msdb.dbo.sysjobs and msdb.dbo.sysjobsteps.

Index inspection and maintenance

List indexes for a table:

SELECT a.name index_name, c.name table_name, d.name column_name FROM sysindexes a JOIN sysindexkeys b ON a.id=b.id AND a.indid=b.indid JOIN sysobjects c ON b.id=c.id JOIN syscolumns d ON b.id=d.id AND b.colid=d.colid WHERE a.indid NOT IN(0,255) AND c.name='XX';

Index size and usage statistics via sys.dm_db_index_usage_stats and sys.dm_db_partition_stats.

Rebuild all indexes online: ALTER INDEX ALL ON table_name REBUILD WITH (ONLINE=ON); Reorganize indexes: ALTER INDEX ALL ON table_name REORGANIZE; Check index fragmentation and space with sys.dm_db_index_physical_stats and sys.dm_db_index_usage_stats.

File and space management

Show total, used and available space per file:

SELECT name, size*8/1024 AS TotalSize_MB, CAST(FILEPROPERTY(name,'SpaceUsed') AS int)*8/1024 AS UsedSize_MB, size*8/1024 - CAST(FILEPROPERTY(name,'SpaceUsed') AS int)*8/1024 AS AvailableSize_MB FROM sys.master_files WHERE database_id=DB_ID(N'DBNAME');

Calculate database size per file, per directory, and identify databases using >8 GB:

SELECT db_name(database_id), name, size*8/1024 AS Size_MB FROM sys.master_files WHERE physical_name LIKE 'G:\DEFAULT.DATA%';

List top‑10 tables by data size using sp_spaceused in a cursor or a set‑based query joining sys.tables, sys.indexes, and sys.allocation_units.

DDL change tracking and tracing

Find trace files: SELECT * FROM sys.traces; Open the trace file with SQL Server Profiler to view DDL events.

Miscellaneous utilities

Show execution plan without running the query: SET SHOWPLAN_ALL ON; GO SELECT XXX; GO SET SHOWPLAN_ALL OFF; (or SHOWPLAN_XML).

Get current user: SELECT SYSTEM_USER; Identify identity column name, seed, increment, current value, and reseed using INFORMATION_SCHEMA.COLUMNS and functions IDENT_SEED, IDENT_INCR, IDENT_CURRENT.

Check Filestream share name: SELECT SERVERPROPERTY('FilestreamShareName'); Query SSRS subscription jobs, replication publications, articles, and snapshot history.

These snippets provide a ready‑to‑use toolbox for SQL Server DBAs to diagnose, monitor, and maintain SQL Server instances efficiently.