SQL Server Monitoring: DMVs, Query Store, and Performance Baselines

event_data.value('(event/@name)[1]', 'VARCHAR(50)') AS event_name, event_data.value('(event/@timestamp)[1]', 'DATETIME2') AS event_timestamp, event_data.value('(event/data[@name="duration"]/value)[1]', 'BIGINT') / 1000 AS duration_ms, event_data.value('(event/data[@name="cpu_time"]/value)[1]', 'BIGINT') / 1000 AS cpu_time_ms, event_data.value('(event/data[@name="logical_reads"]/value)[1]', 'BIGINT') AS logical_reads, event_data.value('(event/data[@name="physical_reads"]/value)[1]', 'BIGINT') AS physical_reads, event_data.value('(event/data[@name="writes"]/value)[1]', 'BIGINT') AS writes, event_data.value('(event/action[@name="sql_text"]/value)[1]', 'NVARCHAR(MAX)') AS sql_text, event_data.value('(event/action[@name="database_name"]/value)[1]', 'NVARCHAR(128)') AS database_name, event_data.value('(event/action[@name="username"]/value)[1]', 'NVARCHAR(128)') AS username, event_data.value('(event/action[@name="client_hostname"]/value)[1]', 'NVARCHAR(128)') AS client_hostname``` FROM (

SELECT CAST(event_data AS XML) AS event_data
FROM sys.fn_xe_file_target_read_file('C:\ExtendedEvents\SlowQueries*.xel', NULL, NULL, NULL)```
) AS XEventData
ORDER BY event_timestamp DESC;

Deadlock Capture

-- Create deadlock capture session
CREATE EVENT SESSION DeadlockCapture ON SERVER
ADD EVENT sqlserver.xml_deadlock_report
ADD TARGET package0.event_file (
```text
SET filename = 'C:\ExtendedEvents\Deadlocks.xel'```
);

ALTER EVENT SESSION DeadlockCapture ON SERVER STATE = START;

-- Read deadlock graphs
SELECT 
```text
event_data.value('(event/@timestamp)[1]', 'DATETIME2') AS event_timestamp,
CAST(event_data.query('(event/data[@name="xml_report"]/value/deadlock)[1]') AS XML) AS deadlock_graph```
FROM (
```sql
SELECT CAST(event_data AS XML) AS event_data
FROM sys.fn_xe_file_target_read_file('C:\ExtendedEvents\Deadlocks*.xel', NULL, NULL, NULL)```
) AS XEventData
ORDER BY event_timestamp DESC;

Performance Baselines

Establishing Baseline

# PowerShell script to collect baseline metrics
$server = "localhost"
$database = "AdventureWorks"
$outputPath = "C:\Baselines"
$timestamp = Get-Date -Format "yyyyMMdd_HHmmss"

## Collect baseline data
$queries = @{
```sql
"WaitStats" = "SELECT * FROM sys.dm_os_wait_stats WHERE wait_time_ms > 0"
"PerfCounters" = @"
    SELECT 
        object_name,
        counter_name,
        instance_name,
        cntr_value
    FROM sys.dm_os_performance_counters
    WHERE counter_name IN (
        'Page life expectancy',
        'Buffer cache hit ratio',
        'Batch requests/sec',





        'SQL Compilations/sec',
        'SQL Re-Compilations/sec'
    )```
"@
```sql
"IndexUsage" = "SELECT * FROM sys.dm_db_index_usage_stats WHERE database_id = DB_ID()"
"QueryStats" = "SELECT TOP 100 * FROM sys.dm_exec_query_stats ORDER BY total_worker_time DESC"```
}

foreach ($key in $queries.Keys) {
```text
$query = $queries[$key]
$outputFile = "$outputPath\Baseline_${key}_${timestamp}.csv"

Invoke-Sqlcmd -ServerInstance $server -Database $database -Query $query |
    Export-Csv -Path $outputFile -NoTypeInformation

Write-Host "Exported $key baseline to $outputFile"```
}

Proactive Monitoring Script

Figure: Activity Monitor – resource usage graphs and expensive queries.





## Daily health check script
$server = "localhost"
$emailTo = "dba@contoso.com"
$emailFrom = "sqlalerts@contoso.com"
$smtpServer = "smtp.contoso.com"





$healthChecks = @()

## Check 1: Long-running queries
$longRunningQueries = Invoke-Sqlcmd -ServerInstance $server -Query @"
```sql
SELECT COUNT(*) AS QueryCount
FROM sys.dm_exec_requests
WHERE total_elapsed_time > 300000  -- 5 minutes
    AND session_id <> @@SPID```
"@





if ($longRunningQueries.QueryCount -gt 0) {

> **Architecture Overview:** $healthChecks = "⚠️ $($longRunningQueries.QueryCount) long running queries detected"```

SELECT COUNT(DISTINCT blocking_session_id) AS BlockingCount
FROM sys.dm_os_waiting_tasks
WHERE blocking_session_id <> 0```
"@





if ($blocking.BlockingCount -gt 0) {

> **Architecture Overview:** $healthChecks = "⚠️ $($blocking.BlockingCount) blocking sessions detected"```

SELECT COUNT(*) AS FailedJobCount
FROM msdb.dbo.sysjobhistory
WHERE run_status = 0  -- Failed
    AND run_date = CONVERT(INT, CONVERT(VARCHAR(8), GETDATE(), 112))```
"@





if ($failedJobs.FailedJobCount -gt 0) {
```text
$healthChecks += "❌ $($failedJobs.FailedJobCount) failed jobs today"```
}

## Send alert if issues found
if ($healthChecks.Count -gt 0) {
```text
$body = "SQL Server Health Check Alert`n`n" + ($healthChecks -join "`n")
Send-MailMessage -To $emailTo -From $emailFrom -Subject "SQL Server Health Alert" -Body $body -SmtpServer $smtpServer```
}

Architecture Decision and Tradeoffs

When designing data management solutions with SQL Server, consider these key architectural trade-offs:

Approach	Best For	Tradeoff
Managed / platform service	Rapid delivery, reduced ops burden	Less customisation, potential vendor lock-in
Custom / self-hosted	Full control, advanced tuning	Higher operational overhead and cost

Recommendation: Start with the managed approach for most workloads and move to custom only when specific requirements demand it.

Validation and Versioning

Last validated: April 2026
Validate examples against your tenant, region, and SKU constraints before production rollout.
Keep module, CLI, and SDK versions pinned in automation pipelines and review quarterly.

Security and Governance Considerations

Apply least-privilege access using RBAC roles and just-in-time elevation for admin tasks.
Store secrets in managed secret stores and avoid embedding credentials in scripts or source files.
Enable audit logging, data protection policies, and periodic access reviews for regulated workloads.

Cost and Performance Notes

Define budgets and alerts, then monitor usage and cost trends continuously after go-live.
Baseline performance with synthetic and real-user checks before and after major changes.
Scale resources with measured thresholds and revisit sizing after usage pattern changes.

Official Microsoft References

https://learn.microsoft.com/sql/
https://learn.microsoft.com/azure/azure-sql/
https://learn.microsoft.com/fabric/database/

Public Examples from Official Sources

These examples are sourced from official public Microsoft documentation and sample repositories.
Documentation examples: https://learn.microsoft.com/sql/
Sample repositories: https://github.com/microsoft/sql-server-samples
Prefer adapting these examples to your tenant, subscriptions, and governance requirements before production use.

Key Takeaways

Use DMVs for real-time query and performance monitoring
Enable Query Store for query performance history
Implement Extended Events for lightweight tracing
Establish performance baselines during normal operation
Monitor wait statistics to identify bottlenecks
Track index usage to optimize indexing strategy
Set up proactive monitoring with PowerShell automation
Configure SQL Server Agent alerts for critical conditions
Review deadlocks and blocking regularly
Analyze I/O latency for storage performance

Next Steps

Enable Query Store on production databases
Create Extended Events sessions for slow queries
Establish baseline metrics for comparison
Automate daily health check reports
Configure SQL Server Agent alerts

Additional Resources

Monitor. Analyze. Optimize. Prevent.