Sometimes, as DBAs at times you might want to know what are the Active Sessions and CPU Utilization / Usage for the last 24 hours. It is very useful to keep a tab on the Usage of SQL CPU/ Active Sessions. Especially, in the Cloud Era, we should know the DTU ( Database Throughput Unit ) of the SQL Server that you are choosing to spin up in Cloud environment. Also, in the Portal, most of the service providers will show a rolling dashboard for a max of 3 months.
In case, if you want to understand the trend of what was the usage of SQL CPU and Active Sessions for a period of One Year in Azure that can help you decide on what pricing tier you should choose.
Here's what we can do :-
Use the below mentioned query and schedule a job from any of your VMs ( because SQL Agent is not available in SQL PAAS Or Create a Powershell cmd / Task Scheduler jobs to run the query ) and populate them in a History table on a daily basis.
Keep a tab on the Active Sessions and CPU Utilization of the SQL Instance / Server over a period of time to understand the usage/utilization to take better decisions on choosing appropriate SQL DTU / SKU that suits your needs which in turn can be cost effective as well.
USE MASTER
SELECT TOP 288 START_TIME, END_TIME, ACTIVE_SESSION_COUNT, ACTIVE_WORKER_COUNT, AVG_CPU_PERCENT,
AVG_PHYSICAL_DATA_READ_PERCENT,AVG_LOG_WRITE_PERCENT
FROM SYS.RESOURCE_STATS
WHERE DATABASE_NAME = '<DATABASENAME>'
ORDER BY START_TIME DESC
hope this helps :)
No comments:
Post a Comment