查询非常慢 [英] Queries are extremely slow
问题描述
您好,
我在Azure中有一个SQL服务器数据库,所有查询都需要很长时间,以分钟为单位。即使是表中的简单选择ID也需要几分钟。我已经尝试了重建索引,并尝试创建新索引,其中没有一个
帮助。 DTU一直在最大化。 CPU利用率一直很低。我有标准的S4:200 DTU,分配的DB大小为100 GB,其中占用35 GB。请建议。谢谢!
I have a SQL server database in Azure and all the queries are taking really a long time, in multiples of minutes. Even a simple select id from table takes few minutes. I have tried the rebuilding indexes and also tried creating new indexes, none of them helped. DTU is maxing out all the time. CPU utilization is low all the time. I have standard S4 : 200 DTUs and the allocated DB size is 100 GB out of which 35 GB is occupied. Please suggest. Thanks!
推荐答案
你好,b
请启用Intelligent Insights以自动监控数据库pe自动诊断和性能问题的根本原因分析。
您还应启用自动调整 以自动调整数据库索引和查询执行计划。
You should also enable Automatic Tuning for automatic tuning of database indexes and query execution plans.
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-automatic-tuning?wt.mc_id=MVP
您提到的不是CPU问题,请使用以下查询来确定哪个资源很高。
SELECT
(COUNT(end_time) - SUM(例如avg_cpu_percent> 80那么1 ELSE 0 END)* 1.0)/ COUNT(end_time) AS'CPU适合百分比'
(COUNT(end_time) - SUM(CASE WHEN avg_cpu_percent > 80 THEN 1 ELSE 0 END) * 1.0) / COUNT(end_time) AS 'CPU Fit Percent'
,(COUNT(end_time) - SUM(例如avg_log_write_percent> 80那么1 ELSE 0 END)* 1.0)/ COUNT(end_time )AS'Log Write Fit Percent'
,(COUNT(end_time) - SUM(CASE WHEN avg_log_write_percent > 80 THEN 1 ELSE 0 END) * 1.0) / COUNT(end_time) AS 'Log Write Fit Percent'
,(COUNT(end_time) - SUM(例如avg_data_io_percent> 80那么1 ELSE 0 END)* 1.0)/ COUNT(end_time )AS'物理数据读取适合百分比'
,(COUNT(end_time) - SUM(CASE WHEN avg_data_io_percent > 80 THEN 1 ELSE 0 END) * 1.0) / COUNT(end_time) AS 'Physical Data Read Fit Percent'
FROM sys.dm_db_resource_stats
运行以下查询以确定内存压力:
SELECT object_name as ObjectName,counter_name as CounterName,CAST(cntr_value / 1024. as DECIMAL(9,2))as MaxMemoryInMB
FROM sys.dm_os_performance_counters
FROM sys.dm_os_performance_counters
WHERE counter_name IN('服务器总内存(KB)','目标服务器内存(KB)');
WHERE counter_name IN ('Total Server Memory (KB)', 'Target Server Memory (KB)');
使用查询商店。
http:// www .sqlcoffee.com / Azure_0010.htm
希望这会有所帮助。
问候,
Alberto Morillo
>
这篇关于查询非常慢的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!