您现在访问的是微软AZURE全球版技术文档网站,若需要访问由世纪互联运营的MICROSOFT AZURE中国区技术文档网站,请访问 https://docs.azure.cn.

在 Azure 突触中排除 SQL 分析的疑难解答Troubleshooting SQL Analytics in Azure Synapse

本文列出了常见的故障排除问题。This article lists common troubleshooting question.

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 用户尝试连接到主数据库,但主数据库中没有用户时,将发生此错误。This error occurs when an Azure AD user tries to connect to the master database, but does not have a user in master. 要更正此问题,请指定要在连接时连接到的 SQL 池,或者将用户添加到主数据库。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 用户尝试连接到主数据库,但主数据库中没有用户时,将发生此错误。This error occurs when an Azure AD user tries to connect to the master database, but does not have a user in master. 要更正此问题,请指定要在连接时连接到的 SQL 池,或者将用户添加到主数据库。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 服务器主数据库上创建登录名,但在 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. 本文介绍如何在主数据库上创建登录名和用户,以及如何在 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 SQL 池受防火墙保护,以确保只有已知的 IP 地址才能访问数据库。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、SSDT 用于可视化工作室,或sqlcmd来查询您的数据。 SSMSSynapse SQL pool recommends using SSMS, SSDT for Visual Studio, or sqlcmd to query your data. 有关驱动程序和连接到 Azure 突触的详细信息,请参阅 Azure突触的驱动程序连接到 Azure 突触的文章。For more information on drivers and connecting to Azure Synapse, see Drivers for Azure Synapse and Connect to Azure Synapse articles.

工具Tools

问题Issue 解决方法Resolution
可视化工作室对象资源管理器缺少 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 Synaps 的身份验证,了解有关将 Azure 活动目录与 Synapse SQL 池使用有关。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.

后续步骤Next steps

如需查找问题的解决方案的更多帮助,下面是可以尝试的一些其他资源。For more help in finding solution to your issue, here are some other resources you can try.