Lesson 4	Course resources
Objective	SQL Configuration and Monitoring resources.

SQL Configuration Monitoring Resources

Which resources are available for configuration and monitoring of SQL Server 2022?

In SQL Server 2022, Microsoft provides a rich set of tools, views, and utilities for configuration and monitoring, ranging from GUI-based interfaces to T-SQL-based diagnostics. Below is a categorized breakdown of key resources:

SQL Server Management Studio (SSMS)
- Purpose: Main GUI for configuring and managing SQL Server instances.
- Features:
  - Instance and database configuration
  - Index and statistics management
  - Security configuration
  - Query Editor
  - Activity Monitor
SQL Server Configuration Manager
- Purpose: Windows utility for managing SQL Server services, network protocols, and client configurations.
- Functions:
  - Start/stop SQL services
  - Enable/disable TCP/IP, Named Pipes
  - Manage service accounts
Transact-SQL (T-SQL)
- Purpose: Scripting interface for configuration tasks.
- Examples:
  - sp_configure – change advanced options
  - ALTER DATABASE – modify DB-level settings
  - CREATE LOGIN / CREATE USER – security configuration
SQL Server Data Tools (SSDT)
- Purpose: For schema design, SSIS/SSRS/SSAS development in Visual Studio.
PowerShell & SQL Server Module
- Use SqlServer module for scripting administrative tasks via PowerShell.

📊 2. Monitoring Tools

Dynamic Management Views (DMVs) and Functions
- Real-time performance metrics and health checks.
- Examples:
  - sys.dm_exec_requests, sys.dm_exec_sessions
  - sys.dm_db_index_usage_stats, sys.dm_os_wait_stats
  - sys.dm_db_log_space_usage
Activity Monitor (within SSMS)
- GUI to track:
  - CPU usage
  - Blocking sessions
  - I/O statistics
  - Active queries
Query Store
- Tracks performance of queries over time.
- Helps identify regressions and enforce query plan stability.
- Enable via:
```
   ALTER DATABASE [YourDB] SET QUERY_STORE = ON;
        
```
Extended Events (XE)
- Lightweight event-tracing framework.
- Replaces SQL Trace/Profiler.
- Use for:
  - Deadlocks
  - Blocking
  - Query execution anomalies
SQL Server Error Logs and Windows Event Viewer
- Helps track:
  - Startup messages
  - Login attempts
  - Backup events
  - Agent job failures
Performance Monitor (PerfMon)
- OS-level tool that can include SQL Server counters like:
  - Buffer cache hit ratio
  - Page life expectancy
  - Transactions/sec
SQL Server Agent
- Job scheduling and monitoring.
- Job success/failure tracking.

High Performance SQL-Server

🧠 3. Cloud and AI-Based Monitoring (Optional)

If you are using Azure-connected features:
📁 Reference Catalogs and Built-in Reports

SSMS includes Built-in Reports under the "Reports" context menu.
Examples:
- Disk usage
- Top resource-consuming queries
- Index usage

📌 Recommended Monitoring Practices

Area	Tool or View
CPU/Memory Usage	`sys.dm_os_sys_memory`, Activity Monitor
Query Performance	Query Store, `sys.dm_exec_query_stats`
Index Fragmentation	`sys.dm_db_index_physical_stats`
Log Space Usage	`sys.dm_db_log_space_usage`
Blocking/Deadlocks	Extended Events, Activity Monitor
Job Monitoring	SQL Server Agent

There are numerous resources offered with this course that will help you to complete it successfully. The greatest resource is the Sitemap which you should visit now if you have not already done so. There, you will learn about other courses taught at this site. In addition to the Orientation, there are several other resources available to you.

Glossary

Throughout this course, you'll have the opportunity to review definitions of key terms.These terms are defined in the course glossary. The terms appear as blue, italicized, and underlined in the lesson text. Clicking a glossary term opens a pop-up window with the definition.

Exercise Submissions

After completing some exercises, you will be required to cut and paste your source code into a text submission box.
In the next lesson, the virtual learning environment will be discussed.