May 12, 2011 at 8:02 am
To improve the performance of a critical proc, we had to finally zeroed in to use an indexed view. I am aware of all limitations indexed view impose on tables which are used in the view.
One of the limititation that concerns me is that the inserts/updates on the tables. that are present in view, might get slow.So if there is an insert/update, then it has to cascade to view as well. And if there are no proper indexes on view, then SQL server first has to find the record in view and then update. And it makes the process very slow.Now suppose my view has 15 columns in select clause and 3 columns in joins and 2 in where clause, then will it be a good idea to create 20 indexes, one for each column on this view? the reason is , I am not sure which columns might get updated from the application for this view.
What can be the downside of this approach?
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
May 12, 2011 at 8:09 am
I'd do this test in QA env.
Run a big insert or update (like 10-30% of the table).
Do it once with the current indexes on and once with all the indexes you want.
I'm pretty sure it'll be way slower with 30 indexes.
May 12, 2011 at 8:16 am
ya, thats true as now sql server has to maintiain index as well. but the fear is that if i miss an index and an update happens on that column, then it will be far far slower.So i might even need to check the trade off. Do i want to create few index and take the risk of unknown updates or shall I create lot of indexes and slow down each and every insert/update
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
May 12, 2011 at 8:19 am
I'm pretty sure that if anything in the row is updated, then the whole row gets the update. And I'm also pretty sure the server will use the PK to find that row. So that's the only path I'd be worried about.
May 12, 2011 at 8:43 am
But in the view, there is no PK. So when indexed view will be updated for data, then only way to find that data to be updated is thru indexes. so for view to be updated fast, proper indexes should be there.
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
May 12, 2011 at 8:47 am
You have no PK defined on that indexed view???? How do you even define that as an indexed view?
More the the point, I still have to see any proof in an actual execution plan to confirm that you have a problem. I might be wrong but I don't think you have a problem at this point, and adding 20 index would actually create a problem more than anything else.
May 12, 2011 at 9:02 am
Sorry, I confused with PK. I actually have a unique clustered index on view. So its PK in one way.
Now another question coming my mind:
My view has three tables(say a.b,c) in join. But the unique index in on PK column of one of the table,say table 'a'.
So if insert a record in table B, then sql server has no fast way to know where this record should be inserted.(eventually it might do a scan on view and then update it).
So does it mean that it will be a good idea to have just three indexes, each created on primary key of all thre tables?
This way, whatever table i insert/update, sql server will have a corresponding index to tell him that where to insert this record. And it will be faster.
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
May 12, 2011 at 9:07 am
Stop stressing for 2 seconds.
Setup a test environement, do inserts, updates, deletes and see the execution plan. You'll know the exact impact on your system and you'll know what to do next.
The 1 thing you need to do is optimize the query that defines the view. Everything else will be handled by the server.
May 13, 2011 at 6:47 am
I think adding 20 indexes is way too mich overkill. Have you tried running a simple trace for a couple of "normal" days to see what kind of activity is happening to the underlying tables? It would also show you where the updates are happening and what is being used in the where clause on the updates. How often do the underlying tables get updated? Through one specific application? How many records are in each of the 3 tables the comprise the view?
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply