SQL 2012 :: Indexed View On Replicated Server Is Very Slow
Sep 10, 2014
So we got ServerA.dbA and ServerB.dbB and looks like nobody thought about that MVew. IT works thru linked server now, so surely enough it sucks..
Then even more: we got piece of DW on ServerRep with replcicated pieces of ServerA and ServerB, how it works I asked: "Don't worry man, we took care of everything, go directly to ServerRep for everything..."
And I see that our MView sucks even deeper, practically 0 performance, I think what going on it's still hit that linked server between ServerA.dbA and ServerB.dbB on background.I still can use it on ServerRep WITH NOEXPAND hint, which means it was properly defined...
Can we have that scenario at all? Or we can replicate all Tables on ServerRep and then define/redefine this view directly on replicated server ?
I am looking to create a constraint on a table that allows multiplenulls but all non-nulls must be unique.I found the following scripthttp://www.windowsitpro.com/Files/0.../Listing_01.txtthat works fine, but the following lineCREATE UNIQUE CLUSTERED INDEX idx1 ON v_multinulls(a)appears to use indexed views. I have run this on a version of SQLStandard edition and this line works fine. I was of the understandingthat you could only create indexed views on SQL Enterprise Edition?
I'm creating Indexed view by JOINING multiple tables and trying to create FULL TEXT search index. Unique column is generated by concatenating to ID columns from different table. I can successfully able to create unique index however when trying to create FULLTEXT INDEX getting below error.
"A full-text search key must be a unique, non-nullable, single-column index which is not offline, is not defined on a non-deterministic or imprecise nonpersisted computed column, does not have a filter, and has maximum size of 900 bytes. Choose another index for the full-text key."
The message clearly says the column should be single-column index, non-deterministic.
I am trying to use an indexed view to allow for aggregations to be generated more quickly in my test data warehouse. The Fact Table I am creating the indexed view on is a partitioned clustered columnstore index.
I have created a view with the following code:
ALTER view dbo.FactView with schemabinding as select local_date_key, meter_key, unit_key, read_type_key, sum(isnull(read_value,0)) as [s_read_value], sum(isnull(cost,0)) as [s_cost] , sum(isnull(easy_target_value,0)) as [s_easy_target_value], sum(isnull(hard_target_value,0)) as [s_hard_target_value] , sum(isnull(read_value,0)) as [a_read_value], sum(isnull(temperature,0)) as [a_temp], sum(isnull(co2,0)) as [s_co2] , sum(isnull(easy_target_co2,0)) as [s_easy_target_co2] , sum(isnull(hard_target_co2,0)) as [s_hard_target_co2], sum(isnull(temp1,0)) as [a_temp1], sum(isnull(temp2,0)) as [a_temp2] , sum(isnull(volume,0)) as [s_volume], count_big(*) as [freq] from dbo.FactConsumptionPart group by local_date_key, read_type_key, meter_key, unit_key
I then created an index on the view as follows:
create unique clustered index IDX_FV on factview (local_date_key, read_type_key, meter_key, unit_key)
I then followed this up by running some large calculations that required use of the aggregation functionality on the main fact table, grouping by the clustered index columns and only returning averages and sums that are available in the view, but it still uses the underlying table to perform the aggregations, rather than the view I have created. Running an equivalent query on the view, then it takes 75% less time to query the indexed view directly, to using the fact table. I think the expected behaviour was that in SQL Server Enterprise or Developer edition (I am using developer edition), then the fact table should have used the indexed view. what I might be missing, for the query not to be using the indexed view?
I am trying toe create an indexed view but cannot seem to get it right.
CREATE VIEW dbo.D_Policy_View with schemabinding AS SELECT Policy_ID, Environment_Code, CoB, Sub_CoB, Policy_No, Version_No FROM dbo.D_Policy WHERE (Policy_ID IN (SELECT MAX(Policy_ID) FROM dbo.d_Policy GROUP BY Environment_Code, COB, Policy_No, SUB_COB))
I have read on BoL that MAX is not allowed but don't know of any other way to get the latest record??
I have a problem trying to create an indexed view on SQL Server 2000. There are multiple databases, one of which stores system wide data. I would like to create an indexed view on the system wide data for each of the site databases.
Is it possible to create an indexed view on data in another database?
snehalata writes "i create view as follows CREATE VIEW Data WITH SCHEMABINDING AS SELECT A.PartitionID,FundID,ReportDate,ForeignTaxWithheld,DomDividendIncome,RGainShortTerm,RGainLongTerm,NewIssueRGainShortTerm, NewIssueRGainLongTerm,ChgUnrealizedGain,ReplaceTax,TotIncomeBefFee,TotalIncome,EndingNetCapital,EndingRedemptionUnits, BeginRedemptionAmount,EndingRedemptionAmount,EndingUnits,InterestOverseas,ExpenseOverseas,OrdIncome, ReallocationExpense,BeginRedemptionFee,EndingRedFee,BeginGrossCapital,EndingGrossCapital,GPFees,FixedExpense,MergerCost, SellingCommission,GrossRoR,NAV,GAV,GPMgmtFee,IMMgmtFee,GPIncentivefee,IMIncentivefee,NetRoR,MonthCounter, BegUnits,BegAddUnits,BegAddAmount,EndAddAmount,GrossRealizedGain,BrokerCommission, NetRealizedGain,OperatingExpense,OffsellExp,OrgExp,USObligationIncome,FixIncomeIntrIncome, CapitalGain,SellingFee,SellingMgmtFeeMidQtr,SyndicateCost,BeginNetCapital,DomesticDividendExp,FixedIncomeIntrExp FROMdbo.vPart1A LEFT JOIN dbo.vPart2B ON A.PartitionID= B.PartitionID
then if i creat index as follows
SET ANSI_NULLS ON SET ANSI_PADDING ON SET ANSI_WARNINGS ON SET ARITHABORT ON SET CONCAT_NULL_YIELDS_NULL ON SET QUOTED_IDENTIFIER ON SET NUMERIC_ROUNDABORT OFF
create unique clustered index ind1 on Data(FundID,ReportDate) it gives me following error Cannot index the view 'MonthliesTest2.4.dbo.Data'. It contains one or more disallowed constructs.
I have written this sample query to search a full-text indexed table and return the results. If the word occurs more than once I want it to return as a new record and the results show a short summary of the location. I was using 'like', but the full table scans were really slowing it down. Can performance be improved for the following (The results returned by my query are accurate)
I created an indexed view in SQL 2000, and I expected to see the index created on the view referenced in the execution plan when I query the view. Instead, I see the index for the base table referenced in the execution plan. Why?
There are 6,000,000+ records in the base table, and the view only references 256 of these rows.
Here is some of the DDL if you need it:
CREATE TABLE [alarm_t] ( [ct_dtm] [datetime] NOT NULL , [dst_flg] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [stn_nm] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [alarm_txt] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [utc_dtm] [datetime] NOT NULL , [create_utc_dtm] [datetime] NOT NULL ) ON [PRIMARY] GO
CREATE CLUSTERED INDEX [alarm_idx2] ON [dbo].[alarm_t]([ct_dtm], [stn_nm], [dst_flg]) ON [PRIMARY] GO
create view dbo.alarm_Mapbd_v with schemabinding as SELECT [ct_dtm], [dst_flg], [stn_nm], [alarm_txt], [utc_dtm], [create_utc_dtm] FROM [dbo].[alarm_t] WHERE[stn_nm]= 'Mapbd' GO
create unique clustered index alarm_Mapbd_idx1 on dbo.alarm_Mapbd_v ( stn_nm, ct_dtm, dst_flg ) go
update statistics alarm_t go update statistics alarm_Mapbd_v go
The following 2 queries have the exact same execution plan, both showing a cost of 50%. I expected to see the index created on the view referenced in the execution plan for the first query. Is the index created on the view being used?
selectstn_nm, ct_dtm, dst_flg fromalarm_Mapbd_v go SELECT [ct_dtm], [dst_flg], [stn_nm], [alarm_txt], [utc_dtm], [create_utc_dtm] FROM [dbo].[alarm_t] WHERE[stn_nm]= 'Mapbd' go
I have created a unique clustered index on a view. The view does a GROUP BY on 3 of the columns and uses the COUNT_BIG aggregate function. I used the following SET commands before creating the view and the index:
SET ARITHABORTON SET CONCAT_NULL_YIELDS_NULL ON SET QUOTED_IDENTIFIER ON SET ANSI_NULLSON SET ANSI_PADDINGON SET ANSI_WARNINGSON SET NUMERIC_ROUNDABORTOFF
I can insert and delete rows from the base table, and the indexed view is updated fine.
However, when a scheduled job does effectively the same thing (delete some rows, and insert some new rows) I get the following error:
Executed as user: NT AUTHORITYSYSTEM. DELETE failed because the following SET options have incorrect settings: 'ARITHABORT'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods. [SQLSTATE 42000] (Error 1934). The step failed.
Why am I getting this error?
The same SET commands above are in the Transact-SQL code for the job before the delete and before the insert statements.
I was looking to improve the performance of an ASP.NET application bycreating the an indexed view that could be used instead of some of theroot tables.What I didn't realize is that it would affect any future conenctionsto the root tables.This of course crashed the application on any type of insert, update,or delte from the root tables.Funny thing is when I removed the indexed view -- it didn't help.I tried flipping the offending options to the opposite of way theywere set on the database and the errors wouldn't go away.I ended up restoring the database from before my mess up to fix it.I was hoping that the SQL experts out there might shed some light onmy problem before I try again.
I am trying to create an indexed view, on a date from a date dimension table...I am new to SQL, and I am at a loss of ideas on this one. Any help would be greatly appreciated!
Here is the Error I am given
"Msg 4513, Level 16, State 2, Procedure VEW_F_MZT_ORDER_HEADER_DAY, Line 3
Cannot schema bind view 'JJWHSE.VEW_F_MZT_ORDER_HEADER_DAY'. 'JJWHSE.VEW_F_INVC_SHIP_TO' is not schema bound.
Msg 1939, Level 16, State 1, Line 1
Cannot create index on view 'VEW_F_MZT_ORDER_HEADER' because the view is not schema bound."
Here is my code..
CREATE VIEW [JJWHSE].[VEW_F_MZT_ORDER_HEADER_DAY] WITH SCHEMABINDING
AS
SELECT TEW_D_DT.DT_KEY AS DATE_KEY,
VEW_F_MZT_ORDER_HEADER.LOCATION_KEY AS LOC_KEY,
TEW_D_LOC.LOC_DESC AS LOC_DESC ,
TEW_D_LOC.RGN_DESC AS REGION_DESC,
TEW_D_LOC.DISTRICT_DESC AS DISTRICT_DESC,
ISNULL(SUM(VEW_F_INVC_PAY_EXT.PRORATED_NET_PRICE),0) AS CONCIERGE_FLASH,
I am trying to create an indexed view with the following code, however it fails with the following error :
An index cannot be created on the view 'TST_SCH' because the select list of the view contains a non-aggregate expression.
- When I remove the case statement it works fine, what can I do to make this work, this is just part of the calculation, I have many such case statements that I will need to use. The column is NON-NUllable in the database - I have verified all columnproperty values in the select statement they are all indexable, the view, when created appears non-indexable, I have been able to create the view WITH SCHEMABINDING, the error occurs when I try to create an index on Column1(CBSA_CODE). I am using SQL Server 2000 enterprise edition (SP4).
Please help.
Thanks,
select DIM_IND_VEH_REG_CUST.CBSA_CODE as CBSA_CODE, DIM_IND_VEH_REG_CUST.CBSA_NAME as CBSA_NAME, DIM_DLR.DLR_NBR AS DLR_NBR, DIM_DLR.PRIM_DLR_NAME as PRIM_DLR_NAME, --DIM_IND_VEH_REG_CUST.SALES_DISTRICT_NBR AS Sales_District_Nbr, DIM_IND_VEH_PROD.MKT_SHR_IND_PROD_GRP AS Mkt_Shr_Ind_Prod_Grp, DIM_IND_VEH_PROD.IDC_VEH_MFGR_DESC AS Veh_Manufacturer_Desc
,(CASE WHEN DIM_DATE.YR = 2006 and DIM_DATE.MTH_NBR = 8 THEN SUM(FACT_IND_VEH_RTL_TXN.UNIT_SOLD_CNT) ELSE 0 END) AS CYCP_ALL_Unit_Cnt
,count_big(*) COUNT_BI
from dbo.DIM_IND_VEH_REG_CUST(NOLOCK), dbo.DIM_IND_VEH_PROD (NOLOCK), dbo.FACT_IND_VEH_RTL_TXN (NOLOCK), dbo.DIM_DATE (NOLOCK), dbo.DIM_DLR(NOLOCK) where DIM_DATE.YR = 2006--(select YEAR(InfoWhseTxnDate) from dbo.audObject a, dbo.audLoadTxnCurr b where a.ObjectID = b.ObjectID and a.ObjectName = 'FACT_IND_VEH_RTL_TXN')--@YEAR OR DIM_DATE.YR = 2005--(select YEAR(InfoWhseTxnDate) from dbo.audObject a, dbo.audLoadTxnCurr b where a.ObjectID = b.ObjectID and a.ObjectName = 'FACT_IND_VEH_RTL_TXN') --(@YEAR-1)
-- and DIM_DATE.MTH_NBR = (select month(InfoWhseTxnDate) from (select InfoWhseTxnDate from audObject a, audLoadTxnCurr b where a.ObjectID = b.ObjectID and a.ObjectName = 'FACT_IND_VEH_RTL_TXN') txn_month) and DIM_IND_VEH_REG_CUST.CBSA_NAME <> '' and DIM_DLR.PRIM_DLR_NAME <> '' and DIM_IND_VEH_REG_CUST.CBSA_CODE not in ('0' ,'00000') AND DIM_IND_VEH_REG_CUST.CBSA_CODE IS NOT NULL AND IDC_VEH_MFGR_DESC = 'Kawasaki' and DIM_DLR.DLR_KEY = FACT_IND_VEH_RTL_TXN.DLR_KEY and DIM_IND_VEH_PROD.MKT_SHR_IND_PROD_GRP <> 'UTILITY VEHICLE' and DIM_IND_VEH_PROD.IND_VEH_PROD_KEY = FACT_IND_VEH_RTL_TXN.IND_VEH_PROD_KEY and DIM_IND_VEH_REG_CUST.IND_VEH_CUST_KEY = FACT_IND_VEH_RTL_TXN.IND_VEH_CUST_KEY and DIM_DATE.DATE_KEY = FACT_IND_VEH_RTL_TXN.DATE_KEY
group by DIM_IND_VEH_REG_CUST.CBSA_CODE, DIM_IND_VEH_REG_CUST.CBSA_NAME, DIM_DLR.DLR_NBR,DIM_DATE.YR,DIM_DATE.MTH_NBR, DIM_DLR.PRIM_DLR_NAME, DIM_IND_VEH_PROD.MKT_SHR_IND_PROD_GRP, DIM_IND_VEH_PROD.IDC_VEH_MFGR_DESC
I have merge replication set up between a sql 2005 server and a device that has sql ce database. My original publication had only tables and this has been working fine, as far as getting data from the server (publisher) to the device (subscriber). Recently, I had a view (joins between 3 tables) that I wanted to add to the publication. When I run the replication now (even on a brand new sql ce datbase) only the tables (and their data) get replicated, the view does not get replicated to the device/sql ce database.
Is there something else I need to do to get the view to replicate to the device?
I have a clustered index on an indexed view in sql server 2000. When I do a simple select in query anaylser from this view I can see from the execution plan in profiler that the clustered index was used to return the data, hence improving performance of the underlying select(this is why I am using the indexed view). However, if I run the query from an asp.net page using the sql provider I can see the call in profiler but the clustered index is not used, hence reducing the performance of the call considerably.If anyone has experienced this please let me know.Cheers
Just wonder how many of you are using or had used indexed view in SQL 2000? Please, share any details/info.
We are currently using in and having problems as we are getting Errors 644, 'Couldn't find index...' every time we try to update the underlying tables.
CREATE VIEW VW_Emplacamentos WITH SCHEMABINDING AS SELECTA.CD_Linha -- This is the unique clustered field A.DT_Emplacamento, A.NR_CNPJ, A.CD_Municipio, A.VL_Potencia, A.VL_CapacidadeCarga, A.NR_QtdPax, A.NR_AnoFabricacao, A.NR_Chassi, A.SG_UF, A.CD_MarcaModelo, A.CD_Procedencia, A.CD_Combustivel, B.NM_Municipio, C.NM_Combustivel, D.NM_MarcaModelo, D.CD_Segmento, D.CD_Fabricante, E.NM_Segmento, F.NM_SubSegmento, G.NM_Fabricante
FROM dbo.TB_Emplacamentos AS A INNER JOIN dbo.TB_Municipio AS B ON A.CD_Municipio = B.CD_Municipio INNER JOIN dbo.TB_Combustivel AS C ON A.CD_Combustivel = C.CD_Combustivel INNER JOIN dbo.TB_ModeloVeiculo AS D ON A.CD_MarcaModelo = D.CD_MarcaModelo INNER JOIN dbo.TB_SegmentoVeiculo AS E ON D.CD_Segmento= E.CD_Segmento INNER JOIN dbo.TB_SubSegmentoVeiculo AS F ON D.CD_SubSegmento = F.CD_SubSegmento INNER JOIN dbo.TB_Fabricante AS G ON D.CD_Fabricante = G.CD_Fabricante
The following query is not getting perfomance. I don't what's going on.
SELECT COUNT(*) FROM VW_Emplacamentos WHERE DT_Emplacamento BETWEEN '20040301' AND '20040316' AND NM_Fabricante = 'VW'
I've created an compost index on the following sequece DT_Emplacamento,NM_Fabricante,NM_Segmento
I have a view that takes 14 mins to return 3000 rows. Â At some points in the execution plan, there are over 4 million rows. Â The query for the View has a Distinct, a couple columns are populated by functions, there is a cross join, there is a join on a table with no keys or index, joins on non-key varchar columns and so forth. Â It looks a bit like this:
SELECT DISTINCT TOP 100 PERCENT N.Id, N.CountyName, N.OperationName, N_LO.Name, N_LO.LastName,N_LO.Address1, N_TO.Name, N_TO.LastName, dbo.fn_Sections(N.Shape, s.Points) AS Sections
[code]...
The DISTINCT is in there to get rid of the extra Bob rows, because Bob has many sizes. Â I need to keep the multiple sizes on Bob's row, but I don't want multiple rows of Bob. Â Is there a more efficient way to do that other than using DISTINCT?
Second, word on the street is that functions are best avoided if you are going for speed. Â I don't see a way to use "FOR XML PATH" outside the function, because I can't get it to work on just one column. Â So I am looking for a way to produce the multiple values on one row.
I have an indexed view with schema binding and text catalog. So far, so good. Now I have the need to know if the rows in this view have matching records in another table. Ouch.
The relation is 1 to n, where n can be 0 or more. For example:
When I am trying to create an indexed view using group and COUNT(*), it gave me the following error:
€œMsg 10136, Level 16, State 1, Line 2 Cannot create index on view "AdventureWorks.Sales.vOrders" because it uses the aggregate COUNT. Use COUNT_BIG instead.€?
When I refered the SQL Books Online, I found the following statement: €œIf GROUP BY is present, the VIEW definition must contain COUNT_BIG(*) and must not contain HAVING€?.
Though my question is basic, I am curious to know the difference between COUNT() and COUNT_BIG(). The only difference I knew is COUNT_BIG() returns bigint. If this is the only difference, why can€™t we use COUNT() in indexed view definition and why COUNT_BIG() is allowed?
Hello All, I have a series of Stored Procedure that has a query taking a join of 5 tables. These tables are quiet large with couple of them having around 10 million rows. As this is a DSS application having periodic data loads, I thought of creating Indexed View on top of these tables. Now the problem is that the Indexed View is not directly used by the optimizer. I need to change my queries and put a WITH (NOEXPAND) query hint to make sure the indexed views are used. This is inspite getting dramatic improvement in the query timings (from 64 secs down to 3 secs) after using the Indexed Views. I would like to know what can be the possible reason for the optimizer not using the Indexed View by itself. Is it because my Indexed View caters to multiple queries or I am missing out on something basic.
I recently added several indexed views to a high traffic table (high volume of both inserts and selects) because I needed to have some complicated unique constraints involving columns that allow NULL.
While I didn't notice performance problems at first, it appears to be that the CPU on the SQL Server is getting pegged when more than 10 or 15 simultaneous inserts are happening on the table. This is a quad proc 3Ghz Xeon, so the fact the CPU is hitting 90%+ while doing 15 inserts a second doesn't make sense to me.
Very quickly the sproc that is being repeatedly called by some middle tier components is taking 30+ seconds to execute, eventually causing timeouts. This sproc is very simple. It does a few quick select statements (that take well under 100ms), and then does an insert into the table in question. That's it.
The only thing I can think of is that the overhead of 3 separate indexed views on the table, each of which contains a significant subset of the total rows in the table (550,000+ rows), is causing SQL Server to get swamped trying to keep the indexes up to date.
Does this seem like a possibility? I'm planning on temporarily removing those indexed views to see how it performs without them, although this is dangerous because it creates the potential for invalid data.
select episode_key as episode_key, ee.object_key, ee.encounter_begin_dt , cp.procedure from dbo.encounters e
join (
select episode_key as minepisode, min(isnull(e.object_key,e.Object_key_History) ) as object_key, min(e.encounter_begin_dt)as encounter_begin_dt
from dbo.encounters e
join dbo.CD_Procedure CP on CP.Procedure_CD = e.procedure_cd where cp.procedure > 0
group by e.episode_key
)ee on e.object_key = ee.object_key
join dbo.cd_procedure cp on cp.procedure_cd = e.procedure_cd
CREATE UNIQUE CLUSTERED INDEX IDX_V1
ON vwLookAdmissionRecord (episode_key)
but i keep getting an error:
annot create index on view "dbo.vwLookAdmissionRecord" because it references derived table "ee" (defined by SELECT statement in FROM clause). Consider removing the reference to the derived table or not indexing the view.
I moved this from another forum because it seems more related to replication the longer I look into it! The following code often dies on a deadlock. I have also seen "Spid x blocked by Spid x", where x is the spid of this connection. The view selects from several tables and some other views as well, and many of the underlying tables are being updated by replication. I have seen cases where the replication Insert proc participated in the deadlock, and the table being inserted into is joined twice in the view, suggesting that somehow this causes the deadlock or makes a deadlock more likely?
SELECT * INTO dbo.tbl_stg_LoansMarketingFirstBorrower FROM view_loans_marketing_first_borrower OPTION (MAXDOP 1)
I do not understand how selecting from a view can cause a deadlock with a proc which does not reference tbl_stg_LoansMarketingFirstBorrower. Any suggestions on how to diagnose this? I used (1204) to identify the proc and underlying table.
