Slow Insert Response with Indexed Views
-
Hope someone has some ideas on what to do about a problem we keep running up against.
Scenario:
SQL Server 2K Sp2
Win 2K sp3 ( I think )
1.5gb DataMart
Dimension hierarchies created as normalised tables with indexed views for denormalised structure at each level of the heirarchy.
eg: we have 5 levels for Products
Product Level 1 Table ( approx 10 recs )
ProdID1, ProdDesc
Product Level 1 View
ProdID1, ProdID1Desc
Product Level 2 Table ( approx 30 recs )
ProdID2, ProdID2Desc, ProdID1
Product Level 2 View
ProdID1, ProdID2, ProdID1Desc, ProdID2Desc
Product Level 3 Table ( approx 70 recs )
ProdID3, ProdID3Desc, ProdID2
Product Level 3 View
ProdID1, ProdID2, ProdID3, ProdID1Desc, ProdID2Desc, ProdID3Desc
Product Level 4 Table ( approx 100 recs )
ProdID4, ProdID4Desc, ProdID3
Product Level 4 View
ProdID1, ProdID2, ProdID3, ProdID4, ProdID1Desc, ProdID2Desc, ProdID3Desc, ProdID4Desc
Product Level 5 Table ( approx 250 recs )
ProdID5, ProdID5Desc, ProdID4
Product Level 5 View
ProdID1, ProdID2, ProdID3, ProdID4, ProdID5, ProdID1Desc, ProdID2Desc, ProdID3Desc, ProdID4Desc, ProdID5Desc
The problem we are having is that when we insert records (anything from a few records to over 100) into the Product Level 5 Table it takes forever to run.
Watching the spid via Current Activity shows it is doing heaps of CPU activity with minimal I/O's. The sys admin's also tell us that the CPU hovers in the 90% usage range for the duration of the query.
We also discovered that the query has shared table locks on all the indexed views and tables listed above.
Can someone explain what is going on and possible methods of getting the inserts to execute quicker.
Thanks
Phill Carter
--------------------
Colt 45 - the original point and click interface
-
Have you verified execution plan?
put create table script for Product Level 5 Table and create view script for Product Level 5 View
This will help to diagnose...
Prakash
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply