Exam DP-300 All Questions

View all questions & answers for the DP-300 exam

Exam DP-300 topic 3 question 3 discussion

Actual exam question from Microsoft's DP-300

Question #: 3
Topic #: 3

HOTSPOT -
You have an Azure SQL database named db1.
You need to retrieve the resource usage of db1 from the last week.
How should you complete the statement? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:

Show Suggested Answer

Suggested Answer:

Box 1: sys.resource_stats -
sys.resource_stats returns CPU usage and storage data for an Azure SQL Database. It has database_name and start_time columns.

Box 2: DateAdd -
The following example returns all databases that are averaging at least 80% of compute utilization over the last one week.
DECLARE @s datetime;
DECLARE @e datetime;
SET @s= DateAdd(d,-7,GetUTCDate());
SET @e= GETUTCDATE();
SELECT database_name, AVG(avg_cpu_percent) AS Average_Compute_Utilization

FROM sys.resource_stats -
WHERE start_time BETWEEN @s AND @e

GROUP BY database_name -
HAVING AVG(avg_cpu_percent) >= 80
Incorrect Answers:
sys.dm_exec_requests:
sys.dm_exec_requests returns information about each request that is executing in SQL Server. It does not have a column named database_name. sys.dm_db_resource_stats: sys.dm_db_resource_stats does not have any start_time column.
Note: sys.dm_db_resource_stats returns CPU, I/O, and memory consumption for an Azure SQL Database database. One row exists for every 15 seconds, even if there is no activity in the database. Historical data is maintained for approximately one hour.
Sys.dm_user_db_resource_governance returns actual configuration and capacity settings used by resource governance mechanisms in the current database or elastic pool. It does not have any start_time column.
Reference:
https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-resource-stats-azure-sql-database

by JohnCrawford at Jan. 7, 2021, 2:22 a.m.

Comments

Submit Cancel

vicky007_87

Highly Voted 1 year, 10 months ago

sys.dm_db_resource_stats: This DMV records a snapshot of resource usage for the database every 15 seconds (kept for 1 hour). sys.resource_stats: This can be run in the context of the master database of the Azure SQL Database server to see resource usage for all Azure SQL Database databases associated with the server. This view is less granular and shows resource usage every 5 minutes (kept for 14 days). sys.resource_stats is the correct answer due to two reason 1. it holds data for 14 days while the other one holds data for 1 hour only. 2.. It has start_time column while the other one does not have it.

upvoted 34 times

...

JakubWBialystok

Highly Voted 1 year, 10 months ago

Answers are correct. 1. sys.dm_db_resource_stats can't be becuase only keep data by 1h. sys.resource_stats can keep by 14days. 2.dateadd is correct

upvoted 20 times

...

eric0718

Most Recent 7 months, 1 week ago

Answer is correct

upvoted 2 times

...

reachmymind

9 months ago

sys.resource_stats DATEADD For a less granular view of this data with longer retention period, use the sys.resource_stats catalog view in Azure SQL Database, or the sys.server_resource_stats catalog view in Azure SQL Managed Instance. This view captures data every 5 minutes and maintains historical data for 14 days. DECLARE @s datetime; DECLARE @e datetime; SET @s= DateAdd(d,-7,GetUTCDate()); SET @e= GETUTCDATE(); SELECT database_name, AVG(avg_cpu_percent) AS Average_Compute_Utilization FROM sys.resource_stats WHERE start_time BETWEEN @s AND @e GROUP BY database_name HAVING AVG(avg_cpu_percent) >= 80;

upvoted 1 times

...

Quarantino

1 year, 4 months ago

Answer is given correct https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-resource-stats-azure-sql-database?view=azuresqldb-current see the example section yo will understand

upvoted 3 times

...

eezzykel

1 year, 9 months ago

the first selection is correct. sys.resource_stats returns data for longer retention period: ys.dm_db_resource_stats returns within the last 60 minutes https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-resource-stats-azure-sql-database?view=azuresqldb-current

upvoted 1 times

...

bearmarsh

1 year, 10 months ago

If you query sys.dm_db_resource_stats the focus has to be in the DB. the query has DB filter applied suggestion you would be outside of DB focus and the Master focus would work fine on the query.

upvoted 1 times

...

Phund

1 year, 11 months ago

Sys.dm_db_resource_stats has not got 'start time' column answer is sys.resource_stats

upvoted 3 times

...

JohnCrawford

1 year, 11 months ago

To get results from sys.resource_stats the focus needs to be on the master database. It will give an error if run from a user database. Sys.dm_dm_resource_stats can be run in a user database and will give more granular information, but as noted in the given answer there is no start time column.

upvoted 2 times

...

JohnCrawford

1 year, 11 months ago

Disregard my previous comment. I don't know why I can' get data back from sys.resource_stats, but Books Online agrees with the given answer.

upvoted 1 times

...

JohnCrawford

1 year, 11 months ago

Answer given is wrong. Correct answer is sys.dm_db_resource_stats and DATEADD. There is no sys.resource_stats dmv.

upvoted 1 times

captainpike

1 year, 2 months ago

https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-resource-stats-azure-sql-database?view=azuresqldb-current

upvoted 1 times

...