March 12, 2010 at 11:08 pm
Hi friends
I have two indexes created on table , following is the structure
1.CREATE CLUSTERED INDEX [cdx_TradeHist_NestOrderNumber] ON [dbo].[TradeHist]
(
[mnmNestOrderNumber] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 50) ON [PRIMARY]
2.CREATE NONCLUSTERED INDEX [ndx_TradeHist_Fill_Mod] ON [dbo].[TradeHist]
(
[mnmNestOrderNumber] ASC,
[mnmFillId] ASC,
[mnmAccountId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 50) ON [PRIMARY]
problem is My inserts are getting slower.
also 30-40 inserts happens on every second
Now can any body tell me whether any of the above index causing problem or not?
Sanket Ahir
Don't run behind the success, Try to be eligible & success will run behind u......
March 12, 2010 at 11:26 pm
Index definitions alone are not enough. We also need to see the table definition (CREATE TABLE statement).
March 12, 2010 at 11:39 pm
How many records this table contains? Definately clusted index will effect the inserts. If there are not many records you can drop the indexs,insert the data and recreate the index.
March 13, 2010 at 12:46 am
Adding to it, Indexes are not good for DML statements, what method are you using to insert data? Is it SSIS or from application ?
March 13, 2010 at 4:08 am
If INSERTs do not normally occur in the logical order specified by the clustered index, you will eventually start to suffer from excessive page-splitting, which will slow INSERT operations significantly. When a page is split, SQL Server has to move half the rows on the old page to the new, and the operation is obviously fully logged.
I assume that INSERTs do not happen in the logical order of clustered index because you specified a FILLFACTOR of 50%. Several comments on that:
1. FILLFACTOR is only enforced when the index is created. SQL Server does not maintain it. As new rows are added to existing pages, the free space is used up.
2. You would generally rebuild the index in your maintenance window to re-establish the FILLFACTOR. The goal is to leave just enough free space on each index build to just avoid page-splitting before the next rebuild.
3. If you have another candidate key which does increase in the order of INSERTs, consider clustering on that instead.
4. If no such key exists, consider adding an IDENTITY column, and cluster on that just to avoid splits. FILLFACTOR should be 100 in that case.
If you do decide to stay with the FILLFACTOR = 50 and rebuild approach, consider adding PAD_INDEX = ON to your index rebuild statements. That will reserve free space in the same proportion at the higher index levels too. FILLFACTOR only applies to the leaf level by default. It does not make a huge difference, but you might want to consider it anyway.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 13, 2010 at 4:19 am
Adding to what Paul already said:
your nonclustered index is unlikely to be used since it has the same leading column as your clustered index.
Please explain what you think the indexes should be used for and we might help you to create better indexes. The best explanation would be the queries you'd like to support.
March 13, 2010 at 4:28 am
lmu92 (3/13/2010)
your nonclustered index is unlikely to be used since it has the same leading column as your clustered index.
The NC index can support seeks over the three keys it contains. The optimizer will use it if it considers it to be cost-effective.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply