Azure Synapse Analytics 中的 Synapse SQL 故障排除Troubleshooting Synapse SQL in Azure Synapse Analytics

本文列出了 Synapse SQL 中的常见故障排除问题。This article lists common troubleshooting issues in Synapse SQL.

ConnectingConnecting

问题Issue 解决方法Resolution
用户 “NT AUTHORITY\ANONYMOUS LOGON” 登录失败。Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. (Microsoft SQL Server,错误:18456)(Microsoft SQL Server, Error: 18456) 当 Azure AD 用户尝试连接到 master 数据库,但 master 中没有用户时,会发生此错误。This error occurs when an Azure AD user tries to connect to the master database, but does not have a user in master. 若要纠正此问题,可以在连接时指定要连接到的 SQL 池,也可以将用户添加到 master 数据库。To correct this issue, either specify the SQL pool you wish to connect to at connection time or add the user to the master database. 有关更多详细信息,请参阅 Security overview(安全性概述)一文。See Security overview article for more details.
服务器主体“MyUserName”无法在当前的安全上下文下访问数据库“master”。The server principal "MyUserName" is not able to access the database "master" under the current security context. 无法打开用户默认数据库。Cannot open user default database. 登录失败。Login failed. 用户“MyUserName”的登录失败。Login failed for user 'MyUserName'. (Microsoft SQL Server,错误:916)(Microsoft SQL Server, Error: 916) 当 Azure AD 用户尝试连接到 master 数据库,但 master 中没有用户时,会发生此错误。This error occurs when an Azure AD user tries to connect to the master database, but does not have a user in master. 若要纠正此问题,可以在连接时指定要连接到的 SQL 池,也可以将用户添加到 master 数据库。To correct this issue, either specify the SQL pool you wish to connect to at connection time or add the user to the master database. 有关更多详细信息,请参阅 Security overview(安全性概述)一文。See Security overview article for more details.
CTAIP 错误CTAIP error 当登录名已在 SQL Server master 数据库中创建,但未在 SQL 数据库中时,可能会出现此错误。This error can occur when a login has been created on the SQL server master database, but not in the SQL database. 如果遇到此错误,请参阅安全性概述一文。If you encounter this error, take a look at the Security overview article. 本文介绍如何在 master 中创建登录名和用户,以及如何在 SQL 数据库中创建用户。This article explains how to create a login and user on master, and then how to create a user in the SQL database.
被防火墙阻止Blocked by Firewall 为了确保只有已知的 IP 地址可以访问数据库,SQL 池受到防火墙保护。SQL pools are protected by firewalls to ensure only known IP addresses have access to a database. 默认情况下,防火墙是安全的,这意味着,需要显式启用单个 IP 地址或地址范围才能进行连接。The firewalls are secure by default, which means that you must explicitly enable and IP address or range of addresses before you can connect. 若要配置防火墙的访问权限,请遵循预配说明中的为客户端 IP 配置服务器防火墙访问权限中所述的步骤。To configure your firewall for access, follow the steps in Configure server firewall access for your client IP in the Provisioning instructions.
无法使用工具或驱动程序进行连接Cannot connect with tool or driver Synapse SQL 池建议使用 SSMS用于 Visual Studio 的 SSDTsqlcmd 来查询数据。Synapse SQL pool recommends using SSMS, SSDT for Visual Studio, or sqlcmd to query your data. 有关驱动程序以及如何连接到 Azure Synapse 的详细信息,请参阅 Azure Synapse 驱动程序连接到 Azure Synapse这两篇文章。For more information on drivers and connecting to Azure Synapse, see Drivers for Azure Synapse and Connect to Azure Synapse articles.

工具Tools

问题Issue 解决方法Resolution
Visual Studio 对象资源管理器缺少 Azure AD 用户Visual Studio object explorer is missing Azure AD users 这是一个已知问题。This is a known issue. 解决方法是在 sys.database_principals 中查看这些用户。As a workaround, view the users in sys.database_principals. 若要详细了解如何将 Azure Active Directory 用于 Synapse SQL 池,请参阅向 Azure Synapse 进行身份验证See Authentication to Azure Synapse to learn more about using Azure Active Directory with Synapse SQL pool.
使用脚本向导进行手动脚本编写或通过 SSMS 进行连接时出现缓慢、未响应或产生错误的情况Manual scripting, using the scripting wizard, or connecting via SSMS is slow, not responding, or producing errors 请确保已在 master 数据库中创建用户。Ensure that users have been created in the master database. 在脚本选项中,同时需确保引擎版本设置为“Microsoft Azure SQL 数据仓库版本”,且引擎类型为“Microsoft Azure SQL 数据库”。In scripting options, also make sure that the engine edition is set as "Microsoft Azure SQL Data Warehouse Edition" and engine type is "Microsoft Azure SQL Database".
在 SSMS 中生成脚本失败Generate scripts fails in SSMS 如果将“为从属对象生成脚本”选项设置为“True”,则为 Synapse SQL 池生成脚本失败。Generating a script for Synapse SQL pool fails if the option "Generate script for dependent objects" option is set to "True." 解决方法是,用户必须手动转到“工具”->“选项”->“SQL Server 对象资源管理器”->“为从属选项生成脚本”并设置为 falseAs a workaround, users must manually go to Tools -> Options ->SQL Server Object Explorer -> Generate script for dependent options and set to false

性能Performance

问题Issue 解决方法Resolution
查询性能故障排除Query performance troubleshooting 如果要尝试对特定查询进行故障排除,请从 Learning how to monitor your queries(学习如何监视查询)开始。If you are trying to troubleshoot a particular query, start with Learning how to monitor your queries.
TempDB 空间问题TempDB space issues 监视 TempDB 空间使用情况。Monitor TempDB space usage. 用尽 TempDB 空间的常见原因包括:Common causes for running out of TempDB space are:
- 分配给查询的资源不足,从而导致数据溢出到 TempDB。- Not enough resources allocated to the query causing data to spill to TempDB. 请参阅工作负荷管理See Workload management
- 统计信息缺失或过期,从而导致数据移动过多。- Statistics are missing or out of date causing excessive data movement. 有关如何创建统计信息的详细信息,请参阅维护表的统计信息See Maintaining table statistics for details on how to create statistics
- TempDB 空间按服务级别进行分配。- TempDB space is allocated per service level. 将 SQL 池扩展到更高 DWU 设置会分配更多 TempDB 空间。Scaling your SQL pool to a higher DWU setting allocates more TempDB space.
查询性能和计划不佳通常是由于缺少统计信息Poor query performance and plans often is a result of missing statistics 性能不佳的最常见原因是缺少数据表的统计信息。The most common cause of poor performance is lack of statistics on your tables. 有关如何创建统计信息以及统计信息为何对性能至关重要的详细信息,请参阅维护表的统计信息See Maintaining table statistics for details on how to create statistics and why they are critical to your performance.
低并发性/查询排队Low concurrency / queries queued 若要了解如何利用并发性平衡内存分配,了解工作负荷管理很重要。Understanding Workload management is important in order to understand how to balance memory allocation with concurrency.
如何实施最佳做法How to implement best practices 开始了解如何提高查询性能的最好地方是 Synapse SQL 池最佳做法一文。The best place to start to learn ways to improve query performance is Synapse SQL pool best practices article.
如何通过缩放提高性能How to improve performance with scaling 有时,改进性能的解决方案是只需通过缩放 SQL 池来提升查询的计算能力。Sometimes the solution to improving performance is to simply add more compute power to your queries by Scaling your SQL pool.
由于索引质量不佳导致查询性能不佳Poor query performance as a result of poor index quality 有时,由于列存储索引质量不佳,查询速度可能会减慢。Some times queries can slow down because of Poor columnstore index quality. 有关详细信息以及如何重建索引以提高段质量,请参阅本文。See this article for more information and how to Rebuild indexes to improve segment quality.

系统管理System management

问题Issue 解决方法Resolution
消息 40847:无法执行操作,因为服务器将超过 45000 这一允许的数据库事务单元配额。Msg 40847: Could not perform the operation because server would exceed the allowed Database Transaction Unit quota of 45000. 请减少要尝试创建的数据库的 DWU,或者请求增加配额Either reduce the DWU of the database you are trying to create or request a quota increase.
调查空间使用率Investigating space utilization 请参阅表大小,了解系统的空间使用率。See Table sizes to understand the space utilization of your system.
管理表的帮助Help with managing tables 有关管理表的帮助,请参阅表概述一文。See the Table overview article for help with managing your tables. 本文还包含指向更详细主题的链接,如表数据类型分布表为表编制索引将表分区维护表统计信息临时表This article also includes links into more detailed topics like Table data types, Distributing a table, Indexing a table, Partitioning a table, Maintaining table statistics and Temporary tables.
在 Azure 门户中,透明数据加密 (TDE) 进度栏不更新Transparent data encryption (TDE) progress bar is not updating in the Azure portal 可以通过 powershell 查看 TDE 的状态。You can view the state of TDE via powershell.

与 SQL 数据库的差异Differences from SQL Database

问题Issue 解决方法Resolution
不支持的 SQL 数据库功能Unsupported SQL Database features 请参阅不支持的表功能See Unsupported table features.
不支持的 SQL 数据库数据类型Unsupported SQL Database data types 请参阅不支持的数据类型See Unsupported data types.
DELETE 和 UPDATE 限制DELETE and UPDATE limitations 请参阅 UPDATE 解决方法DELETE 解决方法使用 CTAS 解决不支持的 UPDATE 和 DELETE 语法See UPDATE workarounds, DELETE workarounds and Using CTAS to work around unsupported UPDATE and DELETE syntax.
不支持 MERGE 语句MERGE statement is not supported 请参阅 MERGE 解决方法See MERGE workarounds.
存储过程限制Stored procedure limitations 请参阅存储过程限制,了解存储过程的一些限制。See Stored procedure limitations to understand some of the limitations of stored procedures.
UDF 不支持 SELECT 语句UDFs do not support SELECT statements 这是 UDF 的当前一项限制。This is a current limitation of our UDFs. 有关我们支持的语法,请参阅 CREATE FUNCTIONSee CREATE FUNCTION for the syntax we support.