Lesson 1

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.

There are several tools available in SQL Server 2019 to monitor your databases, offering different levels of functionality and complexity:
Built-in tools:

• SQL Server Management Studio (SSMS): This provides basic performance monitoring through the Activity Monitor and various DMV (Dynamic Management Views) queries. You can see CPU usage, memory usage, I/O operations, wait times, and more.
• Performance Dashboard: This dashboard offers an overview of key performance metrics across all your SQL Server instances. It's good for high-level monitoring but lacks depth.
• Extended Events: This powerful tool allows you to capture detailed performance data for troubleshooting and analysis. It requires more technical expertise to set up and use.
• SQL Server Profiler: Similar to Extended Events, Profiler captures query execution details and helps identify inefficient queries.

Third-party tools:

• Microsoft Azure Monitor: This cloud-based platform offers integrated monitoring for SQL Server, including performance metrics, alerts, and diagnostics.
• SolarWinds SQL Sentry: This popular tool provides comprehensive performance monitoring, query analysis, deadlock detection, and more.
• Datadog: This cloud-based platform offers real-time monitoring, alerting, and dashboards for SQL Server performance and other metrics.
• Dynatrace: This all-in-one platform provides application performance monitoring (APM) alongside database monitoring, offering insights into the overall health of your system.
• Redgate SQL Monitor: This tool focuses on performance optimization and troubleshooting, helping you identify and fix bottlenecks.

Choosing the right tool depends on your specific needs:

• For basic monitoring, SSMS and the Performance Dashboard might suffice.
• For deeper analysis and troubleshooting, consider Extended Events or a third-party tool.
• For cloud-based monitoring and integration with other services, Azure Monitor or Datadog could be good options.
• For performance optimization, Redgate SQL Monitor is a strong choice.**

Additional factors to consider:

• Cost: Microsoft tools are generally free, while third-party tools usually have paid plans.
• Technical expertise: Some tools require more technical knowledge to set up and use.
• Integration with other tools: Consider how the tool integrates with existing monitoring systems or applications.

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.

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

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.