dbcc-pdw-showmaterializedviewoverhead-transact-sql.md

title	DBCC PDW_SHOWMATERIALIZEDVIEWOVERHEAD (Transact-SQL) | Microsoft Docs
ms.custom
ms.date	07/03/2019
ms.prod	sql
ms.technology	data-warehouse
ms.prod_service	sql-data-warehouse, pdw
ms.reviewer
ms.topic	language-reference
dev_langs	TSQL
author	XiaoyuL-Preview
ms.author	xiaoyul
manager	craigg
monikerRange	= azure-sqldw-latest || = sqlallproducts-allversions

DBCC PDW_SHOWMATERIALIZEDVIEWOVERHEAD (Transact-SQL) (preview)

[!INCLUDEtsql-appliesto-xxxxxx-xxxx-asdw-xxx-md]

Displays the number of incremental changes in the base tables that are held for materialized views in [!INCLUDEssSDW]. The overhead ratio is calculated as TOTAL_ROWS / MAX (1, BASE_VIEW_ROWS).

Transact-SQL Syntax Conventions (Transact-SQL)

Syntax

DBCC PDW_SHOWMATERIALIZEDVIEWOVERHEAD ( " [ schema_name .] materialized_view_name  " )
[;]

Arguments

schema_name
Is the name of the schema to which the view belongs.

materialized_view_name
Is the name of the materialized view.

Remarks

As the underlying tables in the definition of a materialized view are modified, all incremental changes in the base tables are maintained for the materialized view.  Selecting from a materialized view includes scanning the clustered columnstore structure for the materialized view and applying these incremental changes.   If the number of maintained incremental changes is high, select performance will degrade.  Users can rebuild the materialized view to recreate the clustered columnstore structure and consolidate all the incremental changes in the base tables.

Permissions

Requires VIEW DATABASE STATE permission.

Example

This example returns the delta space used for a materialized view.

DBCC PDW_SHOWMATERIALIZEDVIEWOVERHEAD ( "dbo.MyIndexedView" )

Output:

OBJECT_ID	BASE_VIEW_ROWS	TOTAL_ROWS	OVERHEAD_RATIO
1234	1	3	3.0

OBJECT_ID	BASE_VIEW_ROWS	TOTAL_ROWS	OVERHEAD_RATIO
4567	0	0	0.0

OBJECT_ID	BASE_VIEW_ROWS	TOTAL_ROWS	OVERHEAD_RATIO
789	0	2	2.0

See also

CREATE MATERIALIZED VIEW AS SELECT (Transact-SQL)
ALTER MATERIALIZED VIEW (Transact-SQL)
EXPLAIN (Transact-SQL)
sys.pdw_materialized_view_column_distribution_properties (Transact-SQL)
sys.pdw_materialized_view_distribution_properties (Transact-SQL)
sys.pdw_materialized_view_mappings (Transact-SQL)
SQL Data Warehouse and Parallel Data Warehouse Catalog Views
System views supported in Azure SQL Data Warehouse
T-SQL statements supported in Azure SQL Data Warehouse