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	XiaoyuMSFT
ms.author	xiaoyul
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

To keep materialized views refreshed with incremental changes in the base tables, the data warehouse engine adds tracking rows to each view to apply those changes. Selecting from a materialized view includes scanning the view's clustered columnstore structure and applying any incremental changes.  The tracking rows (TOTAL_ROWS - BASE_VIEW_ROWS) do not get eliminated until users REBUILD the materialized view.

The overhead_ratio is calculated as TOTAL_ROWS/MAX(1, BASE_VIEW_ROWS). If it's high, SELECT performance will degrade.  Users can rebuild the materialized view to reduce its overhead ratio.

Permissions

Requires VIEW DATABASE STATE permission.

Examples

A. 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

B. This example shows how the materialized view overhead increases as data changes in base tables

Create a table

CREATE TABLE t1 (c1 int NOT NULL, c2 int not null, c3 int not null)

Insert five rows to t1

INSERT INTO t1 VALUES (1, 1, 1)
INSERT INTO t1 VALUES (2, 2, 2) 
INSERT INTO t1 VALUES (3, 3, 3) 
INSERT INTO t1 VALUES (4, 4, 4) 
INSERT INTO t1 VALUES (5, 5, 5) 

Create materialized views MV1

CREATE materialized view MV1 
WITH (DISTRIBUTION = HASH(c1))  
AS
SELECT c1, count(*) total_number 
FROM dbo.t1 where c1 < 3
GROUP BY c1  

Selecting from the materialized view returns two rows.

c1	total_number
1	1
2	1

Check the materialized view overhead before any data changes in the base table.

DBCC PDW_SHOWMATERIALIZEDVIEWOVERHEAD ("dbo.mv1")

Output:

OBJECT_ID	BASE_VIEW_ROWS	TOTAL_ROWS	OVERHEAD_RATIO
587149137	2	2	1.00000000000000000

Update the base table. This query updates the same column and row 100 times to the same value. The materialized view data does not change.

DECLARE @p int
SELECT @p = 1
WHILE (@p < 101)
BEGIN
UPDATE t1 SET c1 = 1 WHERE c1 = 1
SELECT @p = @p+1
END  

Selecting from the materialized view returns the same result as before.

c1	total_number
1	1
2	1

Run DBCC PDW_SHOWMATERIALIZEDVIEWOVERHEAD ("dbo.mv1"). In the output below, 100 rows were added to the materialized view (total_row - base_view_rows) and the overhead_ratio increased.

OBJECT_ID	BASE_VIEW_ROWS	TOTAL_ROWS	OVERHEAD_RATIO
587149137	2	102	51.00000000000000000

After rebuilding the materialized view, the overhead ratio reduced.

ALTER MATERIALIZED VIEW dbo.MV1 REBUILD
go
DBCC PDW_SHOWMATERIALIZEDVIEWOVERHEAD ("dbo.mv1")

Output

OBJECT_ID	BASE_VIEW_ROWS	TOTAL_ROWS	OVERHEAD_RATIO
587149137	2	2	1.00000000000000000

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