Monitoring Databases  «Prev  Next»

Lesson 1

Monitoring and Maintaining SQL-Server Databases

As a SQL Server system administrator you need to monitor your databases. You will need to do this to either
  1. check the database’s size,
  2. the database’s performance, and
  3. to make sure that the database is healthy and does not become corrupt.

SQL Server database systems are more than ever before being chosen as the preferred backend database solution for large business's critical systems and SQL Server richly deserves this status. As a result of this, more users than ever before are blocked in their daily activity when the database is not available, or in a bad shape.
It is your responsibility as a DBA to know the health of your SQL Server so you can take proactive action to minimize chances anything bad happens to your system. But do not panic. If you have the right tools in place, your system will tell you how it feels and will warn you well before it reaches the alarm phase. Through its performance behavior, you can spot potential problems not yet visible to the end-user. If, for example, for no reason response times of a certain query slow down from 100 to 500 ms, the end-user will not be alarmed, but you should.

Tools available in SQL-Server 2022 to monitor Databases

SQL Server 2022 offers a robust set of monitoring tools—both graphical and command-line—that database administrators can use to track performance, troubleshoot issues, and ensure system health. Here's a categorized breakdown of the primary tools available for monitoring SQL Server 2022:

  1. SQL Server Management Studio (SSMS) Tools

    These are built into the SSMS GUI:

    • a. Activity Monitor

      • Real-time overview of SQL Server performance.
      • Monitors:
        • Active user sessions
        • Expensive queries
        • Blocking transactions
        • CPU usage
        • I/O statistics

    • b. SQL Server Logs

      • Found under Management > SQL Server Logs
      • Displays informational messages, warnings, and errors.

    • c. Object Explorer Reports

      • Right-click a database > Reports > Standard Reports
      • Includes:
        • Disk Usage
        • Top Transactions by Volume
        • Index Usage Statistics

  2. Transact-SQL (T-SQL) Monitoring Queries

    • a. Dynamic Management Views (DMVs)

      • Examples:
        • sys.dm_exec_requests: Active queries
        • sys.dm_os_wait_stats: Wait types and bottlenecks
        • sys.dm_exec_query_stats: Query performance history
        • sys.dm_db_index_usage_stats: Index efficiency

    • b. System Stored Procedures

      • sp_who2: Active sessions with CPU, I/O
      • sp_lock: Locking info
      • sp_monitor: General statistics

  3. Performance Monitor (PerfMon)

    • Windows tool used to track SQL Server counters.
    • Key counters:
      • SQLServer\Buffer Manager
      • SQLServer\SQL Statistics
      • SQLServer\Locks
      • SQLServer\General Statistics

  4. Query Store

    • Persistently captures query performance over time.
    • Enabled per-database: 
      ALTER DATABASE [YourDB] SET QUERY_STORE = ON;
    • Tracks:
      • Execution plans
      • Runtime statistics
      • Regressed queries

  5. Extended Events (XE)

    • Lightweight tracing tool replacing SQL Profiler.
    • Monitors:
      • Deadlocks
      • Blocking
      • Errors and waits
    • Can be configured from SSMS or via T-SQL.

  6. Data Collector (Legacy but still available)

    • Aggregates performance data into a management data warehouse.
    • Includes collection sets for:
      • Server activity
      • Query stats
      • Disk I/O

  7. SQL Server Agent Alerts and Operators

    • Can be configured to:
      • Respond to performance thresholds
      • Send notifications
      • Log issues

  8. Azure Integration and Microsoft Defender for SQL

    • SQL Server 2022 supports Azure Arc and cloud integration for monitoring hybrid environments.
    • Defender for SQL adds threat detection and vulnerability assessments.

  9. Third-Party Monitoring Tools (Common with SQL Server)

    Though not built-in, often used:

    • Redgate SQL Monitor
    • SolarWinds Database Performance Analyzer
    • Idera SQL Diagnostic Manager
    • Quest Foglight for SQL Server

Monitoring Database Maintenance

It is not enough to simply schedule tasks; they must be monitored as well. In larger installations with dozens of SQL Servers spread around the globe, monitoring the health of SQL Server and the databases is itself a full-time job. Table 4-1 provides a sample DBA daily checklist that can be used as a starting point for developing a database monitoring plan.

Table 4-1: Sample DBA daily checklist
Item S M T W T F S
System Databases Backup
Production User Databases Backup
SQL Agent, SQL Maint, & DTC Running
Database Size, Growth, Disk Free Space
Batch Jobs Execute OK
DBCC Jobs Execute OK
SQL Log Errors
Replication Log Agent Running
Replication Distribution Cleanup Job Execute OK
SQL Server Last Reboot

Depending on the number of servers and the complexity, the DBA daily checklist can be maintained manually with an Excel spreadsheet or tracked in a SQL Server table.

Three Reasons to monitor and maintain Databases

Another task of a SQL Server administrator is to monitor and maintain databases. You monitor and maintain databases for three reasons:
  1. Keep track of a database’s size
  2. Make sure that the database is healthy
  3. Keep performance consistent

  • Learning Objectives
    By the time you complete this module, you should be able to:
    1. Monitor the size of a database
    2. Monitor the size of a database with SQL-EM
    3. Use dbcc to monitor databases
    4. Monitor the consistency of individual tables
    5. Perform the miscellaneous database monitoring with dbcc
    6. Monitor the transaction log
    7. Maintain statistics
    8. View statistics information
    9. Run sqlmaint.exe
    10. Use the Database Maintenance plan Wizard
    The next lesson will cover how databases free space.

SEMrush Software TargetSEMrush Software Banner