June 25, 2009 at 5:22 am
Hi
I have a table R and a view AR been defined on it with a clustered index.
When I use the table to get some records from table R, the view is getting locked. its an issue which i came to know when a deak lock happaned. In the dead lock graph, it shows that the index on View AR is being locked and as per the proc, I am accessing the table R.
Why is that bevaviour. Also it seems, the Indexed view been cretaed not properly. the code is as follows:-
CREATE VIEW [dbo].[AR] WITH SCHEMABINDING
AS
SELECT
ID RID,SID,RTID,RD,CBy,PRID,CRStatID,
LMID, RSubTID, RMD
FROM dbo.R WHERE somefiled = 0;
There is no agggregate function being sued so then what is the use of having an indexed view?
the index def is as follows:-
CREATE UNIQUE CLUSTERED INDEX [IX_AR_RID] ON [dbo].[AR]
(
[RID] ASC
)
Please let me know why the same is happening?
best regards,
June 25, 2009 at 6:59 am
I'd have to see the execution plans of the queries to be sure, but I'll bet you the table and the index have the same key. If you're doing an update or delete, where a read can take advantage of either the view or the table, you're probably seeing the view being used in the read part of the write operation. It doesn't sound like the view was created to good purpose. This all speculation without seeing your structures & the code.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 25, 2009 at 8:04 am
CREATE UNIQUE CLUSTERED INDEX [IX_R] ON [dbo].[R]
(
[ID] ASC
)
is the index created on table R
June 25, 2009 at 8:30 am
OK. So they are the same then. What's the view doing for you? It's clustered key is the same as the underlying table.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply