August 4, 2016 at 1:16 pm
Hi guys, I've few tables in my OLTP environment which gets populated every 5-10 minutes with data load of anywhere between 500-1000 records depending on the nature of tables (no updates and/or deletes) and then these are used in a procedure with joins to fetch data for reporting. The user want to load the data and after few minutes want to see the data refreshed in the application. Now my question here is:
1. Is it good idea to have clustered index on these frequently inserted tables ?
2. I have non clustered index currently in all the tables. while checking the plan for procedure shows RID lookup on heap for one table and hovering over the tag it shows Object, Output list & seek predicated with a scalar operator.
Is there a way to enhance the performance ?
August 5, 2016 at 3:50 am
Your tables are probably highly fragmented.
Having a clustered index on a table is recommended. In this case you'll possibly have to extend your tables.
I would add an auto-increment (identity) primary clustered key and then recreate the nonclustered indexes.
Loading data into tables with identity clustered PK is fastest. You'll loose the fragmentation. This for sure will improve the performance on those tables.
RID lookups will be replaced with KEY lookups, which are more efficient.
How to add and Identity column to a heap table:
ALTER TABLE dbo.[YourTable]
ADD ID INT IDENTITY(1,1)
ALTER TABLE dbo.[YourTable]
ADD CONSTRAINT PK_YourTable
PRIMARY KEY(ID)
Igor Micev,My blog: www.igormicev.com
August 5, 2016 at 4:16 am
Yes, it is usually a good idea to have a clustered index on your tables. If you're doing a lot of inserts, make sure the key is monotonically increasing so that new rows get added at the top and you avoid page splits. A DateAdded column is often a good candidate. I would only add an identity column if no such candidate columns already exist, and even then, I'd check very carefully that it doesn't break any code (for example if you have a lot of "SELECT * FROM" statements).
You may find that you need non-clustered indexes as well, but they come with a cost when INSERTs and UPDATEs are done, so make sure you use the index usage DMVs to monitor their usefulness. If you don't have them already, set up index maintenance routines that regularly rebuild or reorganise your indexes according to how fragmented they are.
John
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply