Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Teun Spaans
Starting Member
1 Post |
 Posted - 2011-11-04 : 06:27:34
|
| One DB table has several Mio of records, and currently one clustered index. Inserts are frequent, though I dont have exact data yet. If 3 indexes are added, can anything be said about the extra time these 3 indexes will require for insert statements?Are there any measures to mitigate these effects? |
|
|
vikki.seth
Yak Posting Veteran
66 Posts |
Posted - 2011-11-04 : 12:02:02
|
| Indexes do aid in faster retrieval but this doesn't come without any "side-effects". A new entry will be made in all indexes for every insert in the base table. That's why we should limit the number of indexes on a table. |
 |
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2011-11-12 : 06:01:11
|
| Find the balance for OLTP v DSS type databases. If large amounts of INSERTS exist with Indexes , on OLTP performance may sufferJack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-11-12 : 08:28:48
|
| "Inserts are frequent"What about SELECTs ?If SELECTs are a lot more common than INSERTs then extra indexes should be beneficial, overall.We have some tables which log data and are, pretty much, 100% insert-only. We just have a Clustered PK on them, and when we query it we live with the slow run time of the query.However, slow runtime of the query also means that SQL brings a lot of pages into memory - and that in turn can mean that other pages are pushed out of cache, so it might be that NOT having an index for the Query may have side effects other than the cost of the extra CPU / DISK on INSERTs |
|
|
|
|
|
|
Effect off new indexes on insert