what is difference between sys.dm_db_resource_stats & sys.resource_stats & sys.dm_os_performance_counters
sys.dm_db_resource_stats
The following example returns resource utilization data ordered by the most recent time for the currently connected database.
SELECT * FROM sys.dm_db_resource_stats ORDER BY end_time DESC;
The following example identifies the average DTU consumption in terms of a percentage of the maximum allowed DTU limit in the performance level for the user database over the past hour. Consider increasing the performance level as these percentages near 100% on a consistent basis.
SELECT end_time,
(SELECT Max(v)
FROM (VALUES (avg_cpu_percent), (avg_data_io_percent), (avg_log_write_percent)) AS
value(v)) AS [avg_DTU_percent]
FROM sys.dm_db_resource_stats;
The following example returns the average and maximum values for CPU percent, data and log I/O, and memory consumption over the last hour.
SELECT
AVG(avg_cpu_percent) AS 'Average CPU Utilization In Percent',
MAX(avg_cpu_percent) AS 'Maximum CPU Utilization In Percent',
AVG(avg_data_io_percent) AS 'Average Data IO In Percent',
MAX(avg_data_io_percent) AS 'Maximum Data IO In Percent',
AVG(avg_log_write_percent) AS 'Average Log Write Utilization In Percent',
MAX(avg_log_write_percent) AS 'Maximum Log Write Utilization In Percent',
AVG(avg_memory_usage_percent) AS 'Average Memory Usage In Percent',
MAX(avg_memory_usage_percent) AS 'Maximum Memory Usage In Percent'
FROM sys.dm_db_resource_stats;
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
sys.resource_stats
Returns CPU usage and storage data for an Azure SQL Database. The data is collected and aggregated within five-minute intervals. For each user database, there is one row for every five-minute reporting window in which there is change in resource consumption. The data returned includes CPU usage, storage size change or database SKU modification. Idle databases with no changes may not have rows for every five-minute interval. Historical data is retained for approximately 14 days.
The sys.resource_stats view has different definitions depending on the version of the Azure SQL Database Server that the database is associated with. Consider these differences and any modifications your application requires when upgrading to a new server version.
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
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
sys.dm_os_performance_counters
Returns a row per performance counter maintained by the server. For information about each performance counterIf the installation instance of SQL Server fails to display the performance counters of the Windows operating system, use the following Transact-SQL query to confirm that performance counters have been disabled.
SELECT COUNT(*) FROM sys.dm_os_performance_counters;
If the return value is 0 rows, this means that the performance counters have been disabled. You should then look at the setup log and search for error 3409, "Reinstall sqlctr.ini for this instance, aIf the installation instance of SQL Server fails to display the performance counters of the Windows operating system, use the following Transact-SQL query to confirm that performance counters have been disabled.
SELECT COUNT(*) FROM sys.dm_os_performance_counters;
If the return value is 0 rows, this means that the performance counters have been disabled. You should then look at the setup log and search for error 3409, "Reinstall sqlctr.ini for this instance, and ensure that the instance login account has correct registry permissions." This denotes that performance counters were not enabled. The errors immediately before the 3409 error should indicate the root cause for the failure of performance counter enabling. For more information about setup log files, see View and Read SQL Server Setup Log Files.
Permission
On SQL Server, requires VIEW SERVER STATE permission.
On SQL Database, requires the VIEW DATABASE STATE permission in the database.
Examples
The following example returns performance counter values.
SELECT object_name, counter_name, instance_name, cntr_value, cntr_type
FROM sys.dm_os_performance_counters;
and ensure that the instance login account has correct registry permissions." This denotes that performance counters were not enabled. The errors immediately before the 3409 error should indicate the root cause for the failure of performance counter enabling. For more information about setup log files, see View and Read SQL Server Setup Log Files.
Permission
On SQL Server, requires VIEW SERVER STATE permission.
On SQL Database, requires the VIEW DATABASE STATE permission in the database.
Examples
The following example returns performance counter values.
SELECT object_name, counter_name, instance_name, cntr_value, cntr_type
FROM sys.dm_os_performance_counters;