Essential SQL Queries for Database Administrators (DBAs)"

select * from sys.dm_db_session_space_usage: 

This query can be used to monitor the space usage of each session in the database, helping you identify any sessions that are consuming excessive space.

dbcc sqlperf(logspace): 

By executing this command, you can obtain information about the transaction log space usage for each database. It helps you identify if any transaction logs are growing too large and may require management.

xp_fixeddrives: 

This query provides information about the fixed drives (hard drives) on the SQL Server machine. As a DBA, you can use this to ensure sufficient disk space is available for the database files and logs.

dbcc showcontig:

 Use this command to analyze the fragmentation level of tables and indexes. It helps you identify fragmented objects that might impact performance and allows you to plan for defragmentation or index maintenance.

select * from sys.dm_db_index_physical_stats: 

This query returns detailed physical statistics about indexes in a specified table or view. It helps you analyze index fragmentation, identify unused indexes, and optimize the indexing strategy.

select * from sys.dm_exec_requests order by cpu_time: 

By running this query, you can monitor the CPU time consumed by different SQL requests, helping you identify resource-intensive queries that may require optimization.

select * from sys.dm_exec_sessions:

 This query provides information about active sessions on the SQL Server instance. It allows you to monitor the current connections, identify long-running sessions, and troubleshoot blocking issues.

select * from sys.dm_os_waiting_tasks where session_id > 0:

 Use this query to identify the waiting tasks on the SQL Server instance for user sessions. It helps you investigate and resolve blocking and contention issues.

select * from sys.dm_os_wait_stats: 

This query retrieves wait statistics at the system level, allowing you to identify the most common waits and potential bottlenecks in the system.

select * from sys.dm_io_virtual_file_stats: 

By executing this query, you can monitor the I/O statistics for database files, helping you identify any performance issues related to disk I/O.

select * from sys.dm_os_performance_counters: 

This query retrieves performance counter information for the SQL Server instance. It enables you to monitor various server performance metrics such as CPU usage, memory utilization, and disk activity.