Avatar of marrowyung
marrowyung

asked on 

DTAed result shows it suggest to CREATE VIEW xx WITH SCHEMABINDING

hi,

I DTA a MS SQL Server database using query store and I see the output is :


SET QUOTED_IDENTIFIER ON
go

SET ARITHABORT ON
go

SET CONCAT_NULL_YIELDS_NULL ON
go

SET ANSI_NULLS ON
go

SET ANSI_PADDING ON
go

SET ANSI_WARNINGS ON
go

SET NUMERIC_ROUNDABORT OFF
go

CREATE VIEW [dbo].[mv_1_6497] WITH SCHEMABINDING
 AS 
SELECT  [dbo].[attachment].[case_id] as _col_1,  [dbo].[attachment].[bPurged] as _col_2,  count_big(*) as _col_3 FROM  [dbo].[attachment]   GROUP BY  [dbo].[attachment].[case_id],  [dbo].[attachment].[bPurged]  

go

SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET NUMERIC_ROUNDABORT OFF

go

CREATE UNIQUE CLUSTERED INDEX [IXmv_1_6497_c_31_308208894__K2_K1] ON [dbo].[mv_1_6497]
(
   [_col_2] ASC,
   [_col_1] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
go

Open in new window


I knew we need to CREATE VIEW xxxx WITH SCHEMABINDING so that the view can be indexed, but I don't see any reason any code in our DB use this view (but it suggest),

any reason for it?

and it also suggest column store index:

CREATE NONCLUSTERED COLUMNSTORE INDEX

Open in new window


if once we create that index, and it perform even slower, can we just drop that columnstore index to reverse the change?


* SQL Server 2012 EnterpriseSQLMicrosoft SQL Server

Avatar of undefined
Last Comment
marrowyung
Avatar of Pavel Celba
Pavel Celba
Flag of Czechia image

Of course, if the view creation represents just a suggestion then you cannot expect the view is used somewhere already.

You have to look how the underlying table is queried. If there are queries used which are equal or similar to the view definition then the view creation makes sense. If SQL optimizer recognizes the schema bound view is equal to the query done on the table then it may use the view instead of grouping and calculating the count again and again. This should be visible in query plan.

The unique clustered index helps a lot to GROUP BY clause and the columnstore index speeds the COUNT_BIG function up significantly.

I also expect the number of rows in the table is very large otherwise the contribution of the indexed view would be marginal.

As a side effect of the view and indexes creation you may observe significant slow down when updating the table. This is acceptable when the number of queries using the view significantly exceeds the number of table updates.

Anyway, it should be possible to drop the indexes and the view when you decide so. This you may test easily on a small testing table.
Avatar of marrowyung
marrowyung

ASKER

Of course, if the view creation represents just a suggestion then you cannot expect the view is used somewhere already.

But this also means ALL The script DTA suggested ALSO do not use that view, I am surprised!

If SQL optimizer recognizes the schema bound view is equal to the query done on the table then it may use the view instead of grouping and calculating the count again and again. This should be visible in query plan.

So this means if no T-SQL referencing that view, but if SQL optimizer STILL think they need the view, then SQL server will use the view automatically ? seems.. 

columnstore index speeds the COUNT_BIG function up significantly.

columnstore optimize any aggregated function ?
Usually what columnstore mostly helps on ?

Anyway, it should be possible to drop the indexes and the view when you decide so. This you may test easily on a small testing table.
So if I found the index make query slow, I can ALSO simply go that dB, and then choose the table, go to index session, right click on the column store index and click delete ?

As a side effect of the view and indexes creation you may observe significant slow down when updating the table

why ?


ASKER CERTIFIED SOLUTION
Avatar of Pavel Celba
Pavel Celba
Flag of Czechia image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of marrowyung
marrowyung

ASKER

This means an update at two places which is surely slower than update at one place only.

yes, but this is only for columnstore index or all types of index?

Avatar of Pavel Celba
Pavel Celba
Flag of Czechia image

This is valid for all index types. Any index means additional data and their update consumes time. Of course, if you are updating data which are not included in the index then the only extra operation is to check the fact that index does not need update.
Avatar of marrowyung
marrowyung

ASKER

tks and I might come back later.
Avatar of marrowyung
marrowyung

ASKER

sorry we removed all MV for the deployment as it found to be stopping the table from writing.
Microsoft SQL Server
Microsoft SQL Server

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo