This sample describes an option to update statistics on SQL Server PolyBase external tables.
In the process of configuring a maintenance plan for a SQL Server database with external tables and external data sources for PolyBase queries, an error occurred during the update statistics task.
While attempting to update statistics on an external table, the following error message was encountered:
UPDATE STATISTICS [dbo].[ExternalTableName] WITH FULLSCAN, COLUMNS
Message 46519, level 16, state 22
The object Update Statistics isn't supported on External TableAbout this sample
Before you begin
Case history
Resolution steps
Further considerations
Outcome
Disclaimers
Related links
- Applies to: SQL Server 2017 (or higher)
- Key features: UPDATE STATISTICS
- Workload: No workload related to this sample
- Programming Language: T-SQL
- Authors: Sergio Govoni | Microsoft MVP Profile | Blog | GitHub | Twitter
To run this example, the following basic concepts are required.
SQL Server PolyBase enables your SQL Server instance to query data with T-SQL directly from SQL Server, Oracle, Teradata, MongoDB, Hadoop clusters, Cosmos DB, and S3-compatible object storage without separately installing client connection software. If you are new to PolyBase, you can find initial information in this article: Polybase for beginners.
Upon investigation, it became apparent that SQL Server does not support the direct updating of statistics on external tables, as documented in the CREATE STATISTICS documentation page.
Referring to the documentation page, it explicitly states, "Updating statistics is not supported on external tables. To update statistics on an external table, drop and re-create the statistics."
-
Option 1: Ignore External Tables:
- This option is not feasible for maintenance plans managed by SQL Server Management Studio, necessitating third-party solutions.
-
Option 2: Drop and Recreate Statistics:
- Employ a stored procedure, sp_drop_create_stats_external_table, to generate T-SQL statements for dropping and creating statistics on external tables. This procedure supports statistics on multiple columns.
- Execute
sp_drop_create_stats_external_tableto generate T-SQL statements - Execute DROP STATISTICS statements before the maintenance statistics task
- Execute CREATE STATISTICS statements after the maintenance statistics task
- Implement robust error handling during the maintenance plan execution
- Validate the presence of statistics on the external table upon completion
- When dealing with large external tables, consider the performance implications of using default sampling versus full scan options
- Note that external tables utilizing DELIMITEDTEXT, CSV, PARQUET, or DELTA as data types only support statistics for one column per CREATE STATISTICS command
By integrating the sp_drop_create_stats_external_table stored procedure into the maintenance plan, developers can effectively manage statistics on external tables within SQL Server databases, ensuring optimal performance and reliability.
From the maintenance plan prospective, CREATE and DROP STATISTICS statements can be stored on a temporary table or working table and executed separately. DROP STATISTICS statements can be executed before the maintenance statistics task and afterward the CREATE STATISTICS statements. Pay attention to the error handling during the maintenance plan because, at the end of the maintenance, statistics have to be in place on the external table. The output of the stored procedure cannot be missed.
This code sample is provided for demonstration and educational purposes only. It is recommended to use it with caution and fully understand its implications before applying it in a production environment. The provided code may not fully reflect the best development or security practices and may require adjustments to meet specific project requirements. The author disclaims any liability for any damages resulting from the use or interpretation of this material.
For more information, see these articles: