Tuesday, 29 September 2026

Table size and rows of tables in sql server

Below query will help us, to know tables size and now of rows in a table.

SELECT

SCHEMA_NAME(t.schema_id) AS SchemaName,

t.name AS TableName,

SUM(p.rows) AS RowCont,

CAST(

SUM(a.total_pages) * 8.0 / 1024 / 1024

AS DECIMAL(18,2)

) AS TotalSizeGB,

CAST(

SUM(a.used_pages) * 8.0 / 1024 / 1024

AS DECIMAL(18,2)

) AS UsedSizeGB,

CAST(

(SUM(a.total_pages) - SUM(a.used_pages)) * 8.0 / 1024 / 1024

AS DECIMAL(18,2)

) AS UnusedSizeGB

FROM sys.tables t

INNER JOIN sys.indexes i

ON t.object_id = i.object_id

INNER JOIN sys.partitions p

ON i.object_id = p.object_id

AND i.index_id = p.index_id

INNER JOIN sys.allocation_units a

ON p.partition_id = a.container_id

WHERE t.is_ms_shipped = 0

GROUP BY

t.schema_id,

t.name

ORDER BY

TotalSizeGB DESC;

Wednesday, 23 September 2026

SQL Server Locks, Blocking, and Deadlocks Made Easy for Beginners

Understand how SQL Server protects your data and why sessions sometimes wait for each other

A practical beginner-friendly guide to understanding concurrency, troubleshooting waits, and reducing production risk.

===================================================================

Introduction

Have you ever executed a query and noticed that it keeps running without completing? Or perhaps an application suddenly becomes slow even though server CPU usage looks normal.

In many cases, the cause is related to three important SQL Server concepts:

· Locks

· Blocking

· Deadlocks

Key idea These mechanisms are not automatically problems. SQL Server uses locking to protect data and maintain consistency when multiple sessions access the same resources.

1. What Is a Lock?

A lock is like a "Do Not Disturb" sign placed on data while it is being used. If one user updates a bank account balance, SQL Server can temporarily lock that data so another user cannot modify it at the same time.

Example

Session 1 starts a transaction and updates one employee:

BEGIN TRAN

UPDATE Employees
SET Salary = Salary + 1000
WHERE EmployeeID = 1;

The row remains locked until the transaction is committed or rolled back. If Session 2 tries to update the same row, it must wait:

UPDATE Employees
SET Salary = Salary + 500
WHERE EmployeeID = 1;

2. Common Lock Types

Shared Lock (S)

Used while reading data. Multiple sessions can normally hold compatible shared locks at the same time.

SELECT *
FROM Employees;

Exclusive Lock (X)

Used while modifying data. An exclusive lock prevents incompatible access to the locked resource.

UPDATE Employees
SET Salary = 50000
WHERE EmployeeID = 1;

Update Lock (U)

Used when SQL Server reads a resource with the intention of possibly changing it. Update locks help reduce certain conversion deadlocks.

3. What Is Blocking?

Blocking occurs when one session waits for another session to release an incompatible lock. Think of one person using an ATM while the next person waits for the transaction to finish.

Blocking Example

Session 1:

BEGIN TRAN

UPDATE Customers
SET City = 'London'
WHERE CustomerID = 10;

-- Leave the transaction open for demonstration

Session 2:

SELECT *
FROM Customers
WHERE CustomerID = 10;

Result Depending on the isolation level and database configuration, Session 2 may wait for Session 1. That wait is blocking.

How to Detect Blocking

EXEC sp_who2;

In the output, the BlkBy column can identify the blocking session. For a more focused view:

SELECT
    blocking_session_id,
    session_id,
    wait_type,
    wait_time
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0;

4. What Is a Deadlock?

A deadlock occurs when two or more sessions form a cycle in which each session is waiting for a resource held by another. SQL Server detects the cycle, selects one transaction as the deadlock victim, rolls it back, and allows the other transaction to continue.

Simplified Deadlock Scenario

Session A                         Session B
---------                         ---------
Locks Customer row                Locks Order row
Needs Order row                   Needs Customer row
        \                         /
         +------ deadlock -------+

A typical application receives error 1205, stating that the transaction was deadlocked and chosen as the deadlock victim. The application should handle the error appropriately, often by retrying the transaction after a short delay.

5. How to Capture Deadlocks

SQL Server Extended Events can capture a deadlock graph that shows the sessions, resources, statements, and victim involved. The built-in system_health session is commonly used as a starting point.

SELECT *
FROM sys.dm_xe_sessions
WHERE name = 'system_health';

Important The query above only confirms whether the session exists. To investigate a specific deadlock, review the captured xml_deadlock_report event and its graphical deadlock report.

6. Best Practices to Reduce Blocking

· Keep transactions short and commit or roll back promptly.

· Avoid unnecessary explicit transactions.

· Create appropriate indexes so queries touch fewer rows and pages.

· Avoid waiting for user input while a transaction remains open.

· Schedule large maintenance or data-change operations carefully.

· Evaluate row-versioning options such as Read Committed Snapshot Isolation only after application testing and capacity review.

7. Best Practices to Reduce Deadlocks

· Access tables and resources in a consistent order across code paths.

· Keep each transaction focused on the smallest practical unit of work.

· Use selective predicates and effective indexes.

· Avoid holding locks while performing unrelated processing.

· Capture and analyze the deadlock graph instead of guessing.

· Implement controlled retry logic for error 1205 where appropriate.

Consistent Access Order

Preferred pattern:

Customers -> Orders -> Payments

Riskier pattern:

Session A: Customers -> Orders
Session B: Orders -> Customers

Quick Summary

Concept	Meaning
Lock	Protects a resource while SQL Server processes an operation.
Blocking	One session waits for another session to release an incompatible lock.
Deadlock	Sessions form a circular dependency and cannot proceed.
Deadlock victim	The transaction SQL Server terminates and rolls back to break the cycle.
Primary response	Use short transactions, good indexing, consistent access order, monitoring, and tested retry logic.

Conclusion

Locks are essential for data consistency. Blocking is a normal consequence of incompatible locking, although prolonged blocking can affect performance. Deadlocks are circular waiting conditions that SQL Server resolves by terminating one transaction.

Understanding these concepts helps DBAs, developers, data engineers, and architects diagnose application slowdowns and design safer transaction patterns.

SQL Server Storage Architecture Explained: How SQL Server Stores Data Internally

Introduction

Have you ever wondered what happens when you insert a row into a SQL Server table?

Where does the data actually go?

How does SQL Server find free space?

How are indexes stored?

Why do DBAs talk about Pages, Extents, GAM, SGAM, PFS, and IAM?

Understanding SQL Server Storage Architecture is one of the most important skills for any DBA, Database Architect, or Performance Engineer because almost every performance issue eventually leads back to how data is stored internally.

In this article, we will simplify SQL Server's internal storage mechanism using practical examples.

Why Storage Architecture Matters

Understanding storage internals helps you:

✅ Troubleshoot performance issues

✅ Understand index fragmentation

✅ Optimize large tables

✅ Reduce disk I/O

✅ Improve backup and restore strategies

✅ Analyze space usage

✅ Perform advanced DBA troubleshooting

The Foundation: Data Pages

SQL Server stores all data in Pages.

A Page is the smallest unit of storage.

Page Size - 1 Page = 8 KB

Every database is built using thousands or millions of pages.

Imagine a book.

Book = Database
Chapters = Tables
Pages = SQL Server Data Pages

Data Page Structure

What the Header Stores

Page ID
Object ID
Free Space Information
Next Page Pointer

SQL Server uses these details to locate data quickly.

Extents: A Group of Pages

Managing individual pages would be inefficient.

Instead SQL Server groups pages together.

1 Extent = 8 Pages

Since each page is 8 KB: 8 × 8 KB = 64 KB

Hence 1 Extent = 64 KB

Visual representation of an extent:

Extent

├─ Page 1

├─ Page 2

├─ Page 3

├─ Page 4

├─ Page 5

├─ Page 6

├─ Page 7

└─ Page 8

Types of Extents

Uniform Extent

All 8 pages belong to the same object.

Table A

├─ Page1

├─ Page2

├─ Page3

...

└─ Page8

Page1 → TableA

How SQL Server Finds Free Space

Now comes the interesting part.

How does SQL Server know where free pages exist?

The answer is:

Allocation Maps

These are special pages maintained by SQL Server.

PFS Page (Page Free Space)

PFS keeps track of:

Free space on pages
Empty pages
Allocated pages

Think of it as: "Space Availability Register"

When new data arrives: INSERT INTO Customers

SQL Server first checks PFS.

GAM (Global Allocation Map)

GAM tracks extents that are completely free.

Free Extent ?

YES → Available

NO → Occupied

Think of GAM as: "Free Land Registry"

SGAM (Shared Global Allocation Map)

Tracks mixed extents that still have free pages.

Mixed Extent + Free Pages Available

Used mostly for small objects.

IAM (Index Allocation Map)

IAM connects tables and indexes to physical pages.

Without IAM SQL Server cannot locate table data.

Visualization:

Customer Table

│

▼

IAM Page

│

▼

Page 100

Page 101

Page 102

Page 103

Think of IAM as: Google Maps for SQL Server

How an Insert Really Works

Suppose:

INSERT INTO Employee VALUES (1001,'Ajit')

SQL Server performs:

Step 1

Check PFS - Any page with free space?

Step 2

If none available:

Check GAM - Any free extent available?

Step 3

Allocate new extent

Step 4

Update IAM

Step 5

Store Row

Step 6

Commit Transaction

All this happens in milliseconds.

Real DBA Scenario

Imagine a table containing: 500 Million Rows

If pages become fragmented:

More disk reads
More I/O
Slower queries

This is why DBAs run: ALTER INDEX REBUILD or ALTER INDEX REORGANIZE

Regular maintenance improves page organization and performance.

Key Interview Questions

Q1. What is a Page in SQL Server?

Smallest storage unit of size 8 KB.

Q2. What is an Extent?

Group of 8 Pages. (Size 64 KB)

Q3. What does GAM track?

Free extents.

Q4. What does SGAM track?

Mixed extents with available pages.

Q5. What does IAM do?

Maps database objects to physical pages.

Q6. What does PFS track?

Page allocation and free space.

Summary

SQL Server stores data in a highly organized structure:

Database

▼

File

▼

Extent (64 KB)

▼

Page (8 KB)

▼

Rows

SQL Server then uses:

PFS
GAM
SGAM
IAM

to manage storage efficiently.

Understanding these concepts will make performance tuning, capacity planning, and troubleshooting significantly easier.

Tuesday, 15 September 2026

DBAs: Check Out the New Features in SQL Server 2025 (17.x)

Microsoft SQL Server 2025 introduces several enhancements that are especially relevant for DBAs managing high-availability, performance, and mission-critical environments. [learn.microsoft.com], [learn.microsoft.com]

Key SQL Server 2025 Features

1. AI and Vector Support

New VECTOR data type
Built-in vector functions
Vector indexing capabilities
Designed for AI workloads, embeddings, and similarity searches
GitHub Copilot integration in SSMS

These features make it easier to build AI-powered applications directly on SQL Server. [learn.microsoft.com], [microsoft.com]

2. Optimized Locking

Reduces blocking and lock memory consumption
Helps minimize lock escalation
Improves concurrency in high-transaction environments

A major benefit for DBAs troubleshooting blocking chains and deadlocks. [microsoft.com], [learn.microsoft.com]

3. TempDB Improvements

New TempDB space governance capabilities
Prevents individual workloads from consuming excessive TempDB resources
Accelerated Database Recovery (ADR) support for TempDB

These enhancements can improve workload stability and simplify performance management. [learn.microsoft.com]

4. Better Always On Performance

Persisted statistics for readable secondary replicas
Query Store enabled by default on readable secondaries

These enhancements help optimize reporting and read-only workloads running on Availability Group replicas. [learn.microsoft.com]

5. Query Store and Intelligent Query Processing

Optional Parameter Plan Optimization (OPPO)
Cardinality Estimation Feedback for expressions
DOP Feedback enabled by default
New ABORT_QUERY_EXECUTION query hint

These features provide more automated performance tuning and protection against problematic queries. [learn.microsoft.com]

6. Change Event Streaming

Stream row-level INSERT, UPDATE, and DELETE changes
Integration with Azure Event Hubs and Microsoft Fabric Eventstream

This enables near real-time data integration and event-driven architectures. [learn.microsoft.com]

7. Security Enhancements

Support for TDS 8.0
TLS 1.3 encryption improvements
Important upgrade considerations for:
- Linked Servers
- Replication
- Log Shipping
- Availability Groups

Review security and connectivity requirements carefully before upgrading. [medhacloud.com], [learn.microsoft.com]

8. Standard Edition Improvements

Maximum buffer pool memory increased to 256 GB
Resource Governor now available in Standard Edition
Increased CPU capacity limits

A significant enhancement for organizations running Standard Edition workloads. [learn.microsoft.com], [mssqltips.com]

What Should DBAs Focus On?

If your day-to-day responsibilities include:

Always On Availability Groups
Replication
Log Shipping
Database Migrations
Performance Tuning
SQL Server Upgrades

Pay particular attention to:

✅ Optimized Locking
✅ TempDB Governance
✅ Query Store & Intelligent Query Processing
✅ Readable Secondary Statistics
✅ TDS 8.0 / TLS 1.3 Changes
✅ Replication and Log Shipping Upgrade Considerations

SQL Server 2025 Lifecycle

Microsoft states that SQL Server 2025 (17.x) reached General Availability on November 18, 2025. [medhacloud.com], [microsoft.com]

For DBAs planning future upgrades, reviewing these features early can help improve performance, scalability, and operational efficiency across enterprise environments. [learn.microsoft.com], [microsoft.com]

Which SQL Server 2025 feature are you most excited about: Vector Search, Optimized Locking, Query Store enhancements, or the Standard Edition improvements? 🚀

For a DBA, think of the new VECTOR data type in SQL Server 2025 as a way to store AI embeddings directly inside a table and perform similarity searches without moving data to a separate vector database. <SQL Server 2025> introduces native vector storage, vector functions, and vector indexes specifically for AI and semantic search scenarios. [learn.microsoft.com], [learn.microsoft.com]

What is a Vector?

A vector is simply an array of numbers representing the meaning of text, images, or documents.

Example:

"Oracle DBA"

[0.12, -0.45, 0.89, 0.34, ...]

"SQL Server DBA"

[0.15, -0.42, 0.87, 0.30, ...]

Even though the text is different, the vectors are mathematically close because they have similar meanings.

Example Table

CREATE TABLE KnowledgeBase

(

ArticleID INT PRIMARY KEY,

Title VARCHAR(200),

Content NVARCHAR(MAX),

Embedding VECTOR(1536)

);

Here:

Content stores the actual article.
Embedding stores the AI-generated vector.
1536 is the vector dimension used by many embedding models.

DBA Use Case #1: Knowledge Base Search

Suppose you have thousands of support documents.

Traditional search:

SELECT *

FROM KnowledgeBase

WHERE Content LIKE '%blocking%';

This finds only exact keywords.

Vector search can find:

Blocking
Deadlocks
Lock waits
Concurrency issues

because it searches by meaning rather than exact words. [microsoft.com], [microsoft.com]

DBA Use Case #2: Runbook Search

Imagine storing:

Document
AG Failover Steps
Log Shipping Troubleshooting
Replication Monitoring
TempDB Performance Issues

A DBA asks:

"How do I troubleshoot secondary replica latency?"

Even if those exact words don't exist, a vector search can return the AG troubleshooting document because the meanings are related.

Example Similarity Search

SELECT TOP (5)

Title

FROM KnowledgeBase

ORDER BY VECTOR_DISTANCE(

Embedding,

@QuestionVector

);

Returns the most relevant documents based on semantic similarity.

DBA Use Case #3: Incident Correlation

Store:

Error Messages

Support Tickets

Root Cause Analyses

Postmortems

When a new incident occurs:

AG synchronization delayed

Generate an embedding and search previous incidents.

SQL Server returns similar historical outages automatically.

DBA Use Case #4: ChatGPT/Copilot with Enterprise Data

A common Retrieval-Augmented Generation (RAG) architecture:

User Question

Generate Embedding

SQL Server Vector Search

Most Relevant Documents

LLM (OpenAI/Copilot)

Answer

This allows Copilot to answer questions using your organization's own DBA documentation, SOPs, and knowledge articles. [microsoft.com], [microsoft.com]

Vector Indexes

Without indexing:

SELECT ...

ORDER BY VECTOR_DISTANCE(...)

SQL Server scans every row.

With:

CREATE VECTOR INDEX IX_KB_Embedding

ON KnowledgeBase(Embedding);

SQL Server uses approximate nearest-neighbor algorithms to quickly find similar vectors, making searches practical on millions of rows. [learn.microsoft.com], [microsoft.com]

Real-World DBA Scenarios

Performance Tuning Assistant

Store:

Query plans
Wait statistics
Blocking incidents
Tuning recommendations

Ask:

"High PAGEIOLATCH waits"

Vector search retrieves similar cases and their resolutions.

Error Log Analysis

Store:

SQL Server error logs
AG alerts
Replication failures

Ask:

"The log reader agent is falling behind"

Vector search finds similar historical incidents.

Migration Knowledge Repository

Store:

Upgrade lessons learned
Cutover runbooks
Rollback procedures

Query using natural language instead of keywords.

Why This Matters to DBAs

Previously, AI search solutions required:

Pinecone
Weaviate
Milvus
Azure AI Search

Now vectors can be stored and searched directly in SQL Server, reducing architecture complexity and keeping operational data in the database engine. [learn.microsoft.com], [microsoft.com]

Bottom Line

For most DBAs, the first practical use of the VECTOR data type will be creating an internal DBA Knowledge Repository that allows engineers to ask questions such as:

"How did we fix AG latency last year?"

"Show previous TempDB growth incidents."

"Find production deadlock resolutions."

Instead of relying on keyword searches, SQL Server 2025 can return the most semantically relevant documents using vector search. [learn.microsoft.com], [learn.microsoft.com]

Thursday, 10 September 2026

Azure Database watcher

Azure Database watcher is a managed monitoring service designed to provide insights and diagnostics for Azure databases and Azure Managed Instance. It helps database administrators and developers track performance, detect anomalies and troubleshoot issues efficiently. By collecting telemetry data, logs and metrics, Azure Database watcher enables proactive monitoring and alerting, ensuring database health and optimal performance. Moreover, Database watcher is currently in preview.

Service components

Database watcher

The component responsible for collecting data using (for example) DMVs

Targets

The components to monitor (supported components) :

Azure SQL Database

Elastic pool

Azure SQL Managed Instance

Data store

The component that will store our data :

Azure Data Explorer
Real-Time Analytics in Microsoft Fabric

Supported Azure SQL targets

o Database watcher supports all service tiers, compute tiers, and service objectives in Azure SQL Database and Azure SQL Managed Instance. This includes vCore and DTU purchasing models, provisioned and serverless compute tiers, single databases and elastic pools, and Hyperscale.

o Database watcher can monitor all types of secondary readable replicas, including high availability replicas, geo-replicas, and Hyperscale named secondary replicas.

o For a given watcher, the SQL targets can be in any subscription within the same Microsoft Entra ID tenant.

· Database watcher price

Database watcher costs are incurred by its individual components, as follows:

Component	Price	Notes
Watchers	Free
Dashboards	Free
Azure Data Explorer cluster ¹	Pricing details	The optimal cluster SKU depends on the number of monitoring targets and the query workload running on the cluster. For cluster sizing considerations, see Manage Azure Data Explorer cluster.
Real-Time Analytics in Microsoft Fabric	Included in the Power BI Premium workspace consumption model. Billing per use.	Use either Azure Data Explorer or Real-Time Analytics. Only one of these offerings is required.
A vault in Azure Key Vault	Pricing details	Required only if the optional SQL authentication is used instead of the default Microsoft Entra authentication.
Azure network bandwidth	Pricing details	Cost is not incurred if a watcher, its targets, and its data store are deployed in the same Azure region.
Alerts	Pricing details	Database watcher uses Log Alerts. Monthly price is variable and depends on the number of alert rules you create, the number of SQL targets that have generated alerts during the month, and the evaluation frequency of each alert rule.

Limits

There is a limit on the number of SQL targets per watcher, and the number of watchers per subscription. Deployments exceeding these limits are not supported.

Parameter	Limit
SQL targets per watcher¹	100
Watchers per subscription	20

What does it look like in our demo ?

Deployment

We have an Azure SQL Database component at our disposal, which hosts a database called demo-sql-1. This database, along with our SQL server demo-sqlsrv-1, will be monitored.

The Database Watcher component connects to our instance using a system-assigned identity to collect the necessary information.

Database watcher creation :

The service is not yet available in the “Switzerland North” region.

The name of the system-assigned service principal is always the same as the name of the Azure resource it’s created for. In our case, we will need to grant some specfic privileges on the SQL database to the service principal (identified as demo-watcher-1).

If the Azure Data Explorer resource is not created beforehand, one is suggested (E2d v5) :

Azure Data Explorer cluster and database :

Add targets :

The resource is deployed :

Currently, the service is not accessible because it’s not started and we did not grant the necessary privileges.

We are going to grant the related privileges :

To collect monitoring data, a watcher requires specific, limited access to each monitoring target, as described in the following table. These role memberships and permissions give a watcher the necessary access to the system monitoring data, but not to any other data in your databases.

Azure SQL Database	Azure SQL Managed Instance
Membership in all of the following server roles: ##MS_ServerPerformanceStateReader## ##MS_DefinitionReader## ##MS_DatabaseConnector##	The following server permissions: CONNECT SQL CONNECT ANY DATABASE VIEW ANY DATABASE VIEW ANY DEFINITION VIEW SERVER PERFORMANCE STATE The SELECT permission on the following tables in the msdb database: dbo.backupmediafamily dbo.backupmediaset dbo.backupset dbo.suspect_pages dbo.syscategories dbo.sysjobactivity dbo.sysjobhistory dbo.sysjobs dbo.sysjobsteps dbo.sysoperators dbo.syssessions

Privileges to grant in the case of an Azure SQL Database component :

CREATE LOGIN [demo-dbwatcher-1] FROM EXTERNAL PROVIDER;

ALTER SERVER ROLE ##MS_ServerPerformanceStateReader## ADD MEMBER [demo-dbwatcher-1];

ALTER SERVER ROLE ##MS_DefinitionReader## ADD MEMBER [demo-dbwatcher-1];

ALTER SERVER ROLE ##MS_DatabaseConnector## ADD MEMBER [demo-dbwatcher-1];

The created login on SQL side :

As specified earlier the name of the system-assigned service principal is always the same as the name of the Azure resource it’s created for :

https://learn.microsoft.com/en-us/azure/azure-sql/database-watcher-manage?view=azuresql&tabs=sqldb

Permission issues ?

It is possible that after starting the Azure Database watcher component, it appears blank. In this case, you may encounter a permission issue with the Azure Data Explorer component (in our case).

We assign the right permissions on the Azure Data Explorer component :

Once is done you will need to start the service:

We select the Dashboards link :

Database watcher uses Azure Workbooks to provide monitoring dashboards at the estate level and at the resource level.

Here is an example of a database CPU utilization heatmap on the estate dashboard. Each hexagon represents a SQL target. There are two logical servers, one with six databases and one with three databases. The high availability secondary replicas are shown on the heatmap as separate targets. Select the image to see additional details, including data ingestion statistics.

:::image type="content" source="media/database-watcher-overview/database-watcher-sql-database-estate-dashboard-cropped.png" alt-text="Screenshot that shows an example of a CPU utilization heatmap on the database watcher estate dashboard." lightbox="media/database-watcher-overview/database-watcher-sql-database-estate-dashboard.png":::

Here is an example showing a partial view of the Performance tab of an Azure SQL database dashboard. Select the image to zoom into details.

:::image type="content" source="media/database-watcher-overview/database-watcher-sql-database-resource-dashboard-cropped.png" alt-text="Screenshot that shows an example of a database watcher dashboard for an Azure SQL database." lightbox="media/database-watcher-overview/database-watcher-sql-database-resource-dashboard.png":::

The following table describes the capabilities of database watcher dashboards in the Azure portal.

Capability	Description
Estate dashboards	Visualize high-level monitoring data for multiple monitored resources in a common view. Use heatmaps to find top resource consuming databases, elastic pools, or SQL managed instances. Use the top queries view to find top resource consuming queries across your Azure SQL estate, ranking queries by CPU, duration, execution count, etc. Use the subscription, resource group, and resource name filters to focus on subsets of your Azure SQL estate. Drill through to detailed dashboards for specific resources.
Resource dashboards	Visualize detailed monitoring data for a database, an elastic pool, or a SQL managed instance, including: - Active sessions - Backup history - Common performance counters - Connectivity probes - Database and instance properties and configuration - Geo-replication - Index metadata, usage statistics, warnings, and suggestions - Resource usage - Session and connection statistics - SQL Agent job state and history - Storage consumption and performance - Table metadata - Top queries - Wait statistics Use resource dropdowns to quickly switch from one resource to another. Use the estate link to zoom out to an estate dashboard.
Filter by time range	On each dashboard, set the time range to focus on the desired time interval. Use standard or custom time ranges. Narrow down the time range to an interval of interest by "brushing", or dragging the mouse cursor over a chart to select a shorter time range.
Historical data	Depending on the dataset, dashboards show either a summary for the selected time interval, or the latest sample collected in the time interval. Toggle between the latest and a historical view to look at data samples earlier in the selected time range. For example, instead of looking at the currently active sessions, review a previous sample of active sessions collected when a spike in resource usage occurred.
Secondary replicas	Monitor all types of replicas, including high-availability (HA) secondary replicas on estate dashboards. Toggle between viewing the primary replica and its HA secondary replica on resource dashboards.
Download data to Excel	Download data from charts and grids as csv files and open them in Excel for additional analysis.
Data refresh	Retrieve the latest data from the monitoring data store when you open a dashboard and as you switch from tab to tab. After a dashboard has been opened for some time, refresh it manually to see the latest data, or enable automatic dashboard refresh.
Ad hoc KQL query	Use a link on each dashboard to open the Azure Data Explorer web UI and query your monitoring data with KQL. For more information, see datasets and Use KQL to analyze monitoring data.
Descriptions	Toggle the Show descriptions parameter to see descriptions that help you interpret displayed data and include relevant documentation links.
Tooltips	Hover over a field to see more details and context for displayed data.
Ingestion statistics	Use the Ingestion statistics link to see data ingestion latency and other ingestion statistics per dataset.
Dark mode	Switch the Azure portal appearance to use the dark theme to have database watcher dashboards use dark mode.

Our database is being monitored. Initially, we accessed the heatmap:

However, by clicking on the database name “demo-sql-1”, we gain access to more information:

We also have easy access to waits. They are categorized by type :

We also have access to performance counters :

At the same time, we initiated a series of insertions on a sample table to generate activity. We can see that it has been identified and what type of waits it generates :

We also have access to sessions. The displayed view shows which sessions consume the most resources :

We were also able to easily retrieve the query that generated the most activity via the “Top queries” tab :

We also have some information about waits :

We have access the Storage section :

We also have access to the tables and indexes related to our database :

Finally, we can easily retrieve the properties of our database without having to query the tables and system views :

We can clearly see that we have a highly efficient and easy-to-use monitoring tool at our disposal.

Pricing

The “Database Watchers” and Dashboards components are free however the storage part via “Azure Data Explorer” or “Real-time analytics in Microsoft Fabric” is not free. Data transfer between different components is free as long as all components (target, watcher and data store) are in the same region. Unfortunately, this service is not yet available in Switzerland, but it is possible to deploy it in another region to monitor databases hosted in the “Switzerland North” region.

Reference Links:

https://www.dbi-services.com/blog/an-introduction-to-azure-database-watcher/

Pricing - Azure Monitor | Microsoft Azure – For Alerts Pricing

Monitor Azure SQL workloads with database watcher - Azure SQL Database & SQL Managed Instance | Microsoft Learn