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 :
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:
if once we create that index, and it perform even slower, can we just drop that columnstore index to reverse the change?
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
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
if once we create that index, and it perform even slower, can we just drop that columnstore index to reverse the change?
Last Comment
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 ?
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 ?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.
As a side effect of the view and indexes creation you may observe significant slow down when updating the table
why ?
ASKER CERTIFIED SOLUTION
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.
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?
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.
ASKER
tks and I might come back later.
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 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
TRUSTED BY
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.