top of page

DMVs for SQL Database

SQL Database is the Azure version of SQL server. We can use the following DMVs to monitor SQL Database performance:

sys.database_connection_stats

Count successful and failed connections. The count of failed connections is the sum of login failures, terminated connections, and throttled connections.

sys.dm_db_resource_stats

Get the resource consumption percentages for CPU, data IO, and log IO. It returns one row for every 15 seconds, even when there is no activity in the database. For less granular data, you can use sys.resource_stats in the logical master database for your server.

sys.dm_exec_query_stats

In combination with sys.dm_exec_sql_text, find queries that use a lot of resources, such as CPU time or IO.

sys.dm_tran_locks

Discover blocked queries.

sys.event_log

Find issues such as deadlocking and throttling over the last 30 days. You must have permission to read the master database on the Azure server. As an example, you can search for specific types of events, such as deadlocks or throttle events, and when they occurred, as follows,

SELECT Event_Category, Event_Type, Event_Subtype_Desc, Event_Count, Description, Start_Time FROM sys.event_log WHERE

Event_Type = 'deadlock' OR Event_Type like 'throttling%'

 

Example:

Answer:


aboutME

I am John Fan Zhang, a data analyst and finance researcher. I hold a PhD in finance, CFA charter and full membership of CFA New Zealand Society. I have fifteen-year experience in corporate investment and eight-year experience in advanced data analysis. My research focuses on the effect of social psychology (culture) on financial decisions. Finance research involves heaps of data analyses that lead me to the data field. I am a Microsoft Certified Solutions Expert (MCSE): Data Management and Analytics (Excel, Power BI, and SQL). Aside from Excel, Power BI and SQL, I am also familiar with econometric tools such as Stata, Eviews, and MATLAB. I use OX and Python for programming. I am an active data community event participant, volunteer, speaker, moderator, program reviewer, including PASS Marathon 2020, Global AI BootCamp Auckland 2019, SQL Saturday Auckland (2017, 2018, 2019), and Definity Conference (2018, 2019, 2020, Auckland, New Zealand).

Auckland, New Zealand

  • Google Site
  • Twitter
  • LinkedIn

©2016 BY JOHN'S DATA STORY

bottom of page