Azure SQL查询速度慢 [英] Azure SQL query slow

问题描述

我已经设置了Azure SQL数据库.我的目标是评估它以便在实际项目中使用.

I have setup an Azure SQL database. My goal to to evaluate it for use on a real project.

我有一个非常慢的简单查询.

I have a simple query that is very slow.

select *
from table1 T1 join table2 T2
        on T1 .T2_id = T2.id

在我的笔记本电脑SQL Server上，这是不到一秒的时间，但是天蓝色是11秒.

On my laptop SQL Server this is sub-second but in azure it's 11 seconds.

Table1 有6,643行，而table2有12行.

Table1 has 6,643 rows and table2 has 12 rows.

两个SQL Server上的执行计划是相同的.

The execution plan is identical on both SQL Servers.

如果我只返回一个 count(*)，则在两个地方都不足一秒.

If I just return a count(*) it's sub-second in both places.

执行计划包含对 Table1 的主键的聚集索引扫描，这是成本的90％(在两种环境中).使用 count(*)，此扫描不在计划中.

The execution plan contains a clustered index scan of Table1's primary key which is 90% of the cost (in both environments). With the count(*), this scan is not in the plan.

我正在使用基本的服务层"，但是只有很少的数据是我的唯一用户.这是一个测试，但是我无法将这种性能投入生产.

I am using the basic 'service tier' but there is very little data I am the only user. This is a test, but I can't put anything into production with this performance.

我升级到了标准层，其中"S2 50 DTUS's"是标准层中最高的.我仍然有问题.

I upgraded to Standard tier, with "S2 50 DTUS's" - the highest in standard tier. I am still having the problem.

推荐答案

使用数据库吞吐量单元(DTU)限制SQL Azure IO.我怀疑您已经达到了极限，尽管从表面上看，这听起来似乎并不像一个昂贵的查询.

SQL Azure IO is throttled using Database Throughput Units (DTUs). I suspect you are hitting your limit, although on the surface this certainly doesn't sound like an expensive query.

以下是一个类似问题，对类似问题进行了深入分析:

Here's a similiar question that goes into some in-depth analysis of a similiar problem: Simple select count(id) uses 100% of Azure SQL DTUs

最终，找出答案的快速方法是:尝试一个高端计划几分钟，然后看看会发生什么.

Ultimately, the quick way to find out is this: try a higher-end plan for a few minutes and see what happens.

这篇关于Azure SQL查询速度慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！