July 9, 2009 at 1:29 pm
Hello. Our users are getting blocked or are timing out when trying to access a view thru a .Net web page. We are using the SQL 2005 out of the box Read Committed isolation level. The underlying table in my view here is rarely, if ever altered so I don't know why users can't get at it. It does read out thru a linked server to another busy machine if that matters.
I've read some of the upsides and downsides to the Read Committed Snapshot isolation level and was wondering if it makes sense to try it just to ease some of the bottlenecks our users are experiencing. Then again, it would have to be used in the referenced DB.
Ken
CREATE VIEW [dbo].[Callcenter]
As
With RollupsNeeded (CallcenterId, RollupNeeded) As
(Select CallcenterId, 1 From RollupHistory Where Status = 0 Group By CallcenterId)
Select c.CallCenterID,
Case r.RollupNeeded
When 1 Then CallCenterLabel + ' - rollup pending'
Else CallCenterLabel End As 'CallCenterLabel', CallCenterType, SiteCode, PeripheralID
From [CommonTables].Eccr.Dbo.CallCenter c Left Join RollupsNeeded r On c.CallcenterId = r.CallcenterId
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'callcenter'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=N'' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'callcenter'
July 9, 2009 at 3:37 pm
It will only help if blocking is the issue. Since you mentioned that the tables are rarely updated, are you sure that blocking is the problem? It could be that the view is inefficient, or the queries are not filtering correctly (thus returning more data than is needed to the page), or that the page itself has problems.
Assuming that the timeout you refer to is a SQL timeout then you should use profiler to look at what query is having the problem, check the execution plan, and work from there. If blocking is not the problem and you cannot optimise the query further then increasing the command timeout might be your best bet.
July 10, 2009 at 12:14 pm
Thanks Matt but it's staring me right in the face. It's The RollupHistory table that's referenced in the view. That table is being updated within a transaction in another proc.
Ken
July 10, 2009 at 12:42 pm
What are the indexes on the RollupHistory table and what does the update statement look like? Inadequate indexing is often a major cause of blocking in SQL.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 10, 2009 at 12:51 pm
In that case can you tune the other proc so that it doesn't hold the locks for as long? If not the alternatives that I can think of are:
1) increase the command timeout on queries that reference the view
2) use a nolock hint on that table (of course you need to take into account all the caveats that go along with that hint)
3) use read committed snapshot (but because this is a database-level setting you may have additional testing to do to ensure that the extra tempdb load doesn't hurt you elsewhere)
4) enable snapshot isolation level for the database and use it for queries that reference the view.
July 10, 2009 at 1:43 pm
Update statement is:
Update RollupHistory Set Status = 1, RollUpEndTime = GetDate()
Where CallCenterId = @CallCenterId And RollupSubmissionTime = @RollupSubmissionTime
Indexes are on Status and another on CallcenterId + RollUpSubmissionTime.
Seems like indexes with good selectivity but I can tell form my application logic that it's those very rows that are being updated here that are going to meet the criteria in the CTE in my Callcenter view. They both go after those rows in RollupHistory with Status = 0.
Thanks,
Ken
July 10, 2009 at 1:57 pm
Is the update statement alone inside a transaction, or does the transaction encompass other processing? Does it have to update a large number of rows? Is it being blocked by some other process? The statement and indexing appear to be OK, but something is making it hold locks for a long time.
July 10, 2009 at 2:02 pm
If the indexes are selective, the updates should be quick and the blocking short-lived, especially if, as you say, the underlying table is rarely if ever changed.
Can you check and ensure that both the update and the query against the view are using the indexes?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 10, 2009 at 2:38 pm
Yes, this little update is within a larger transaction that does a lot of updates elsewhere in the DB. But it need not be I think.
Begin Tran
Update StartTime
Do many updates elsewhere.....
Update EndTime
End Tran
I may get those 2 updates out of this transaction. That's gotta' help.
GilaMonster (7/10/2009)
If the indexes are selective, the updates should be quick and the blocking short-lived, especially if, as you say, the underlying table is rarely if ever changed.Can you check and ensure that both the update and the query against the view are using the indexes?
Yes, the update uses an index seek and curiously the select uses an index scan.
Thanks
And thanks to Nilav Baran Ghosh for his excellent article the other day on using indexes to reduce blocking to get me started in this direction.
Ken
July 10, 2009 at 3:05 pm
ken.trock (7/10/2009)
Yes, this little update is within a larger transaction that does a lot of updates elsewhere in the DB. But it need not be I think.
So the lock are held til the commit
Yes, the update uses an index seek and curiously the select uses an index scan.
Index scan or clustered index scan? Can you post the exec plan?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 13, 2009 at 9:45 am
Here is the Select statement execution plan attached.
Ken
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply