找出哪个查询在SQL Azure中增加DTU [英] Find out which query increasing DTU in SQL Azure

问题描述

我正在为我的应用程序使用SQL Azure SQL Server.我的应用程序所在的DB层一直运行良好，直到最近，平均DTU的使用受到控制.但是在高峰时段，DTU一直持续到100％达到峰值.升级到下一层是一个选项，但是首先我想弄清楚哪个查询对此负责.是否可以在Azure中找到哪个查询使DTU跃升到100％?

I am using SQL Azure SQL Server for my App. The DB tier, which my app was in was working perfectly till recently and the avg DTU usage was under control. But off late, during peak hours, the DTU spikes to touch 100% consistently. Upgrading to the Next tier is an option, but first i want to figure out which query is responsible for this. Is this possible to find in Azure, which query made the DTU jump to 100%?

推荐答案

简单地说，DTU是您将基于服务层获得的CPU，IO，内存的混合.如果有显示以下内容的列，那将非常好与总DTU相比，查询使用了多少DTU.

Simply put DTU is a blend of CPU,IO,Memory you will get based on your service tier..It would be really great if there is column which shows how much DTU the query used compared to total DTU..

我将分两步处理此问题.

I would deal this issue in two steps..

第1步:
找出哪个指标始终超过90％

Step1:
Find out Which metric is consistently more than 90%

-该DMV最多每14秒钟存储15天的DTU使用情况.

--This DMV stores DTU usage for every 15 seconds upto 14 days..

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; 

第二步:
如果您发现上述任何一项指标始终超过90％，则可以调整这些查询.

Step2:
If you see any of the above metric consistently more than 90%,you can tune those queries..

例如，如果cpu超过90％，则可以开始跟踪cpu使用率较高的查询并对其进行调整.

For Example,if cpu is more than 90% ,you can start tracking the queries which are having high cpu usage and tune them..

自20171701起更新:
SQLAzure引入了查询性能"洞察力，该洞察力显示了查询所使用的DTU.您必须启用查询存储才能使其正常工作.

Updated as of 20171701:
SQLAzure introduced Query Performance insight,which shows DTU used by a Query.You will have to enable Querystore for this to work..

从上面的屏幕截图中可以看到，您可以看到每个查询的确切DTU使用情况

As you can see from screenshot above,you can see exact DTU usage for each query

这篇关于找出哪个查询在SQL Azure中增加DTU的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！