sql-server - Azure SQL 数据库 - 查询速度明显慢于 Azure VM 上的 SQL 数据库

Question

我们将 SQL Server 从 Azure VM 迁移到 Azure SQL 数据库。 Azure VM 为 DS2_V2、2 核、7GB RAM、最大 6400 IOPS Azure SQL 数据库为标准 S3、100 DTU。在 Azure VM 上运行 Azure DTU 计算器工具 24 小时后，我选择了此层 - 它为我建议了此层。

问题是，与 Azure VM 上的查询相比，现在的查询(主要是 SELECT 和 UPDATE)速度慢得令人痛苦。我注意到的一件事是，在运行查询时，我转到 Azure 门户中“监视”下的“资源利用率”图表，在运行任何查询的整个过程中 ping 为 100%。 这是否意味着我的其实等级太低了？我希望不会，因为下一层的成本会大幅上涨。

仅供引用，Azure SQL 数据库的架构和数据与 Azure VM 数据库相同，并且我在迁移后重建了所有索引(包括全文索引)。

到目前为止，在我的研究中，我已经阅读了所有内容，从确保我的 Azure SQL DB 位于 Azure 上的正确区域(确实如此)到导致问题的网络延迟(Azure VM 上不存在)。

Answer 1

此系统现在作为 Azure SQL Server 数据库运行了多长时间？大概如果它已经存在超过几个小时了(即一些“生产”查询已经命中它)并且它生成了一些有用的统计信息。

分析这一点并确定问题的根源将是一个多管齐下的策略。

服务等级检查

尝试以下查询，以确定您是否处于正确的服务级别:

-----------------------
---- SERVICE TIER CHECK
-----------------------
-- The following query outputs the fit percentage per resource dimension, based on a threshold of 20%.
-- IF the query below returns values greater than 99.9 for all three resource dimensions, your workload is very likely to fit into the lower performance level.
SELECT 
    (COUNT(end_time) - SUM(CASE WHEN avg_cpu_percent >= 20 THEN 1 ELSE 0 END) * 1.0) / COUNT(end_time) AS 'CPU Fit Percent'
    ,(COUNT(end_time) - SUM(CASE WHEN avg_log_write_percent >= 20 THEN 1 ELSE 0 END) * 1.0) / COUNT(end_time) AS 'Log Write Fit Percent'
    ,(COUNT(end_time) - SUM(CASE WHEN avg_data_io_percent >= 20 THEN 1 ELSE 0 END) * 1.0) / COUNT(end_time) AS 'Physical Data Read Fit Percent'
FROM sys.dm_db_resource_stats

-- Look at how many times your workload reaches 100% and compare it to your database workload SLO.
-- IF the query below returns a value less than 99.9 for any of the three resource dimensions, you should consider either moving to the next higher performance level or use application tuning techniques to reduce the load on the Azure SQL Database.
SELECT 
(COUNT(end_time) - SUM(CASE WHEN avg_cpu_percent >= 100 THEN 1 ELSE 0 END) * 1.0) / COUNT(end_time) AS 'CPU Fit Percent'
,(COUNT(end_time) - SUM(CASE WHEN avg_log_write_percent >= 100 THEN 1 ELSE 0 END) * 1.0) / COUNT(end_time) AS 'Log Write Fit Percent'
,(COUNT(end_time) - SUM(CASE WHEN avg_data_io_percent >= 100 THEN 1 ELSE 0 END) * 1.0) / COUNT(end_time) AS 'Physical Data Read Fit Percent'
FROM sys.dm_db_resource_stats

资源消耗水平

检查资源消耗也很有用，您可以使用以下查询来执行此操作。这将报告 DTU 消耗和 IO 等信息。

-----------------
-- Resource Usage
-----------------
select *
from sys.dm_db_resource_stats 
order by end_time desc

索引

还值得快速检查是否缺少索引或者某些现有索引是否妨碍。

The missing index query is a doozy, but should be taken with a grain of salt. I generally see it as an advisement on how the db is being used and I make my own judgement on which indexes to add, and how. For example, as a general rule of thumb, all foreign keys should have non-clustered indexes to facilitate the inevitable JOIN's they're involved in.

--------------------
-- Find poor indexes
--------------------
DECLARE @dbid int
SELECT @dbid = db_id()

SELECT 'Table Name' = object_name(s.object_id), 'Index Name' =i.name, i.index_id,
        'Total Writes' =  user_updates, 'Total Reads' = user_seeks + user_scans + user_lookups,
        'Difference' = user_updates - (user_seeks + user_scans + user_lookups)
FROM sys.dm_db_index_usage_stats AS s 
INNER JOIN sys.indexes AS i
ON s.object_id = i.object_id
AND i.index_id = s.index_id
WHERE objectproperty(s.object_id,'IsUserTable') = 1
AND s.database_id = @dbid
AND user_updates > (user_seeks + user_scans + user_lookups)
ORDER BY 'Difference' DESC, 'Total Writes' DESC, 'Total Reads' ASC;

------------------
-- Missing Indexes
------------------
declare @improvementMeasure int = 100

SELECT
CONVERT (decimal (28,1), 
migs.avg_total_user_cost * 
migs.avg_user_impact * 
(migs.user_seeks + migs.user_scans)) 
AS improvement_measure, 
OBJECT_NAME(mid.object_id, mid.database_id) as table_name,
  mid.equality_columns as index_column,
  mid.inequality_columns,
  mid.included_columns as include_columns, 
'CREATE INDEX IX_' + 
OBJECT_NAME(mid.object_id, mid.database_id) + 
'_' + 
REPLACE(REPLACE(mid.equality_columns, '[', ''), ']', '') + 
' ON ' + 
mid.statement + 
' (' + ISNULL (mid.equality_columns,'') + 
CASE WHEN mid.equality_columns IS NOT NULL 
AND mid.inequality_columns IS NOT NULL 
THEN ',' 
ELSE '' 
END + ISNULL (mid.inequality_columns, '') + 
')' + 
ISNULL (' INCLUDE (' + mid.included_columns + ')',
'') AS create_index_statement, 
migs.user_seeks,
migs.unique_compiles,
migs.avg_user_impact,
migs.avg_total_user_cost

FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs 
ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid 
ON mig.index_handle = mid.index_handle
WHERE CONVERT (decimal (28,1), 
migs.avg_total_user_cost * 
migs.avg_user_impact * 
(migs.user_seeks + migs.user_scans)) > @improvementMeasure
ORDER BY migs.avg_total_user_cost * 
migs.avg_user_impact * 
(migs.user_seeks + migs.user_scans) DESC

维护

还应该制定维护计划，以便定期重建索引和统计信息。遗憾的是，Azure SQL 环境中没有 SQL 代理。但是 Powershell 和 Azure function或Azure WebJob可以帮助您安排和执行此操作。对于我们的本地服务器和 azure 服务器，我们每周都会这样做。

Note that WebJob's would only help if you have a pre-existing App Service to run it within.

有关帮助您维护索引和统计信息的脚本，请查看 Ola Hallengren's脚本提供。