May 28, 2009 at 6:35 am
I have a doubt over if Non Clustered Index makes Update/Inserts Slow in performance.
and if it does or does not ,can anybody tell why?
Thanks
Priyank
May 28, 2009 at 7:14 am
Yes, nonclustered indexes slow down data operations because those changes (whether it's insert, update or delete) has to be done to the table and all the NC indexes.
Test it out, it's not usually a huge thing.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 31, 2009 at 8:01 am
Insert/Upates can amount to huge page splits if your fill factor for that non clustered index is low. Huge page splits causes excessive disk I/O and contribute to slow performance. You can know about huge splits if you are having fragmented indexes. You can check on sys.dm_db_index_physical_stats DMF avg_fragmentation_in_percent value to check fragmentation of your indexes.
June 1, 2009 at 6:55 pm
Isa related ?. I want to only update a table's columns where the data has changed, but columns_updated is limited to tables with 32 columns or less.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply