Lesson Learned #220:Hands-On-Labs: Activity Monitor in my Elastic Database Pool
Published Jul 09 2022 04:40 AM 2,216 Views

To be honest, this post is one of my favorites that I was looking to post due to many questions that we get from our customers about how to monitor my elastic database pool. Many customers have a dense elastic database pool and they need a clear picture of what is happening in their elastic database pool. I hope that you can enjoy like as much as I enjoyed during these tests. 

 

In this article and video we are going to monitor the elastic database pool using the monitor an we are going to share a query to obtain all the current processes that your elastic database pool is running. 

 

The first thing is to know the main characteristics of an elastic database pool. 

 

  • Databases running on a single SQL Instance.
  • Configuration per database

 

The second, is to know the options that we have to monitor an elastic database pool

 

  • Azure Portal, Azure Monitor, Log Analytics and SQL Auditing
  • Select * from sys.dm_db_resource_stats
  • Select * from sys.dm_exec_requests in combinations with other
  • Query Data Store
  • Use the queries provided in the demo

 

FInally, the best practices:

 

 

Demo

 

In this demo I have the following configuration:

 

  • Elastic Database Pool Name: Jmjuradotest
  • Elastic Database Pool Configuration:
    • General Purpose 2 vCores
    • Storage Size: 316 GB
    • Per Database Setting: Unlimited per Database.
  • Databases that are part of this Elastic Database Pool:
    • Jmjuradotestdb1
    • Jmjuradotestdb2
    • Jmjuradotestdb3

How to monitor queries that are running in my Elastic Database Pool.

 

This is the query that I used to monitor the activity

 

 

 

SELECT
 substring(REPLACE(REPLACE(SUBSTRING(ST.text, (req.statement_start_offset/2) + 1, (
(CASE statement_end_offset WHEN -1 THEN DATALENGTH(ST.text) ELSE req.statement_end_offset END
- req.statement_start_offset)/2) + 1) , CHAR(10), ' '), CHAR(13), ' '), 1, 512) AS statement_text
,dbs.name
,program_name
,req.session_id
, req.cpu_time 'cpu_time_ms'
, req.status
, wait_time
, wait_resource
, wait_type
, last_wait_type
, req.total_elapsed_time
, total_scheduled_time
, req.row_count as [Row Count]
, command
, scheduler_id
, memory_usage
, req.writes
, req.reads
, req.logical_reads
FROM sys.dm_exec_requests AS req
inner join sys.dm_exec_sessions as sess on sess.session_id = req.session_id
left join [dbo].[master_data] as dbs on dbs.database_id = sess.database_id
CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) as ST
where req.session_id <> @@SPID
order by dbs.name

 

 

 

If you run this query connected to any database that belongs to your elastic database pool you could find some useful information:

 

Jose_Manuel_Jurado_0-1657360726230.png

 

As you could see this query has a special table called master_data, basically it is an external table that is connecting to master database to obtain the name of the database. Unfortunately, in Azure SQL Database is not possible to connect to others databases once you are connected to another. If you don't want to create an external table, please, basically, remove the reference like I posted below.

 

 

 

SELECT
 substring(REPLACE(REPLACE(SUBSTRING(ST.text, (req.statement_start_offset/2) + 1, (
(CASE statement_end_offset WHEN -1 THEN DATALENGTH(ST.text) ELSE req.statement_end_offset END
- req.statement_start_offset)/2) + 1) , CHAR(10), ' '), CHAR(13), ' '), 1, 512) AS statement_text
--,dbs.name
,req.database_id
,program_name
,req.session_id
, req.cpu_time 'cpu_time_ms'
, req.status
, wait_time
, wait_resource
, wait_type
, last_wait_type
, req.total_elapsed_time
, total_scheduled_time
, req.row_count as [Row Count]
, command
, scheduler_id
, memory_usage
, req.writes
, req.reads
, req.logical_reads, blocking_session_id
FROM sys.dm_exec_requests AS req
inner join sys.dm_exec_sessions as sess on sess.session_id = req.session_id
--left join [dbo].[master_data] as dbs on dbs.database_id = sess.database_id
CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) as ST
where req.session_id <> @@SPID
--order by dbs.name

 

 

 

Definition of external table

 

 

 

CREATE DATABASE scoped CREDENTIAL CredentialJM WITH IDENTITY  ='username', SECREt = 'Password'

CREATE EXTERNAL DATA SOURCE [RemoteDataJM] WITH (TYPE = RDBMS, LOCATION = N'servername.database.windows.net', CREDENTIAL = [CredentialJM], DATABASE_NAME = N'master')
GO

CREATE external TABLE [dbo].[master_data](
name varchar(120), database_id bigint
)
WITH
(
  DATA_SOURCE = [RemoteDataJM],
  SCHEMA_NAME = 'sys', --schema name of remote table
  OBJECT_NAME = 'databases' --table name of remote table
);

 

 

 

 

In the following video you could see that giving a special workload (running queries that are taking high CPU, Bulk inserts and TempDB operations) how I monitor my elastic database pool, how I know that queries running and how I know which is the database that is taking more resources

 

 

Enjoy!

Version history
Last update:
‎Jul 09 2022 04:40 AM
Updated by: