Skip to content

Latest commit

 

History

History
168 lines (121 loc) · 6.04 KB

dbcc-pdw-showmaterializedviewoverhead-transact-sql.md

File metadata and controls

168 lines (121 loc) · 6.04 KB
titledescriptionauthorms.authorms.reviewerms.datems.servicems.subservicems.topicdev_langsmonikerRange
DBCC PDW_SHOWMATERIALIZEDVIEWOVERHEAD (Transact-SQL)
DBCC PDW_SHOWMATERIALIZEDVIEWOVERHEAD Displays the number of incremental changes in the base tables that are held for materialized views for Azure Synapse Analytics or Analytics Platform System (PDW).
XiaoyuMSFT
xiaoyul
randolphwest
12/05/2022
sql
data-warehouse
reference
TSQL
= azure-sqldw-latest

DBCC PDW_SHOWMATERIALIZEDVIEWOVERHEAD (Transact-SQL)

[!INCLUDE asa]

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

:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions

Syntax

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

Note

[!INCLUDEsynapse-analytics-od-unsupported-syntax]

Arguments

schema_name

The name of the schema to which the view belongs.

materialized_view_name

The name of the materialized view.

Remarks

To keep materialized views refreshed with data changes in base tables, data warehouse engine adds tracking rows to each affected view to reflect the changes. Selecting from a materialized view includes scanning the view's clustered columnstore index and applying any incremental changes.  The tracking rows (TOTAL_ROWS - BASE_VIEW_ROWS) don't 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 overhead ratio of a materialized view.

DBCC PDW_SHOWMATERIALIZEDVIEWOVERHEAD ("dbo.MyIndexedView");

Sample output:

OBJECT_IDBASE_VIEW_ROWSTOTAL_ROWSOVERHEAD_RATIO
1234133.0

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

Create a table:

CREATETABLEt1 (c1 INTNOT NULL, c2 INTNOT NULL, c3 INTNOT NULL);

Insert five rows into 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)) ASSELECT c1, COUNT(*) total_number FROMdbo.t1WHERE c1 <3GROUP BY c1;

Selecting from the materialized view returns two rows.

c1total_number
11
21

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

DBCC PDW_SHOWMATERIALIZEDVIEWOVERHEAD ("dbo.mv1")

Sample output:

OBJECT_IDBASE_VIEW_ROWSTOTAL_ROWSOVERHEAD_RATIO
587149137221.00000000000000000

Update the base table. This query updates the same column in the same row 100 times to the same value. The materialized view content doesn't change.

DECLARE @p INT; SELECT @p =1; WHILE (@p <101) BEGINUPDATE t1 SET c1 =1WHERE c1 =1; SELECT @p = @p +1; END;

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

c1total_number
11
21

Below is the output from DBCC PDW_SHOWMATERIALIZEDVIEWOVERHEAD ("dbo.mv1"). 100 rows are added to the materialized view (total_row - base_view_rows) and its overhead_ratio is increased.

OBJECT_IDBASE_VIEW_ROWSTOTAL_ROWSOVERHEAD_RATIO
587149137210251.00000000000000000

After rebuilding the materialized view, all tracking rows for incremental data changes are eliminated and the view overhead ratio is 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

close