对 Azure Synapse Analytics 中的专用 SQL 池(以前称为 SQL DW)进行故障排除Troubleshooting dedicated SQL pool (formerly SQL DW) in Azure Synapse Analytics

本文列出了 Azure Synapse Analytics 中的专用 SQL 池(以前称为 SQL DW)的常见故障排除问题。This article lists common troubleshooting issues in dedicated SQL pool (formerly SQL DW) in Azure Synapse Analytics.

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 池(以前称为 SQL DW),也可以将用户添加到 master 数据库。To correct this issue, either specify the dedicated SQL pool (formerly SQL DW) 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 池(以前称为 SQL DW),也可以将用户添加到 master 数据库。To correct this issue, either specify the dedicated SQL pool (formerly SQL DW) 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 数据库主数据库上创建的,而不是在特定的 SQL 数据库中创建时,可能会发生此错误。This error can occur when a login has been created on the SQL Database master database, but not in the specific SQL database. 如果遇到此错误,请参阅安全性概述一文。If you encounter this error, take a look at the Security overview article. 本文介绍如何在主数据库中创建登录名和用户,以及如何在 SQL 数据库中创建用户。This article explains how to create a login and user in the master database, and then how to create a user in a SQL database.
被防火墙阻止Blocked by Firewall 专用 SQL 池(以前称为 SQL DW)由防火墙提供保护,以确保只有已知的 IP 地址可以访问数据库。Dedicated SQL pool (formerly SQL DW) 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 专用 SQL 池(以前称为 SQL DW)建议使用 SSMSSSDT for Visual Studiosqlcmd 来查询数据。Dedicated SQL pool (formerly SQL DW) 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 Synapse 进行身份验证,详细了解如何将 Azure Active Directory 与专用 SQL 池(以前称为 SQL DW)配合使用。See Authentication to Azure Synapse to learn more about using Azure Active Directory with dedicated SQL pool (formerly SQL DW).
使用脚本向导进行手动脚本编写或通过 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 Synapse Analytics 版本”,且引擎类型为“Microsoft Azure SQL 数据库”。In scripting options, also make sure that the engine edition is set as "Microsoft Azure Synapse Analytics Edition" and engine type is "Microsoft Azure SQL Database".
在 SSMS 中生成脚本失败Generate scripts fails in SSMS 如果将“为依赖对象生成脚本”选项设置为“True”,则为专用 SQL 池(以前称为 SQL DW)生成脚本会失败。Generating a script for dedicated SQL pool (formerly SQL DW) 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

数据引入和准备Data ingestion and preparation

问题Issue 解决方法Resolution
使用 CETAS 导出空字符串会导致 Parquet 和 ORC 文件中出现 NULL 值。Exporting empty strings using CETAS will result in NULL values in Parquet and ORC files. 请注意,如果从具有 NOT NULL 约束的列中导出空字符串,CETAS 会导致记录被拒绝,并且导出可能会失败。Note if you are exporting empty strings from columns with NOT NULL constraints, CETAS will result in rejected records and the export can potentially fail. 删除 CETAS 的 SELECT 语句中的空字符串或有问题的列。Remove empty strings or the offending column in the SELECT statement of your CETAS.
不支持将 0-127 范围外的值加载到 Parquet 和 ORC 文件格式的 tinyint 列中。Loading a value outside the range of 0-127 into a tinyint column for Parquet and ORC file format is not supported. 为目标列指定较大的数据类型。Specify a larger data type for the target column.

性能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 池(以前称为 SQL DW)扩展到更高的 DWU 设置会分配更多的 TempDB 空间。Scaling your dedicated SQL pool (formerly SQL DW) 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 若要开始了解如何提高查询性能,最好是参阅专用 SQL 池(以前称为 SQL DW)最佳做法一文。The best place to start to learn ways to improve query performance is dedicated SQL pool (formerly SQL DW) best practices article.
如何通过缩放提高性能How to improve performance with scaling 有时,若要提高性能,只需通过缩放专用 SQL 池(以前称为 SQL DW)提高查询的计算能力即可。Sometimes the solution to improving performance is to simply add more compute power to your queries by Scaling your dedicated SQL pool (formerly SQL DW).
由于索引质量不佳导致查询性能不佳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.
存储过程限制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.
列的 sp_rename(预览版)不适用于 dbo 之外的架构sp_rename (preview) for columns does not work on schemas outside of dbo 这是列的 sp_rename(预览版)的当前限制。This is a current limitation of Synapse sp_rename (preview) for columns. 不属于 dbo 架构的对象中的列可以通过 CTAS 重命名为新表。Columns in objects that are not a part of dbo schema can renamed via a CTAS into a new table.