Post reply

clustered index impact.

  • October 23, 2008 at 12:44 am

    #214433

    Hi all,

    i have two tables each of them having abt 55laks of records. there is no clustered index present in the tables. coule of nonclustered indexes are present in two columns. now, wehn i use these two tables in a stored procedure, my plan shows table scan and index seek.

    what is preventing me from providing clustered index is, the two tables which i use wil lbe getting updated or inserted very frequently. it s involved in lot of OLTP operations. so if i implement clus indexes on these, it wil further increase the cost by additional sorting and index updations cost. now how can i improve the perf of my SP/?

    please help.

    thanks in advance

  • October 23, 2008 at 6:33 am

    #888563

    There are exceptions, but by and large, every table should have a clustered index. The nonclustered indexes have to use RID to track down the data, which is generally not as fast as key lookups within a clustered index.

    There are exceptions, but I would strongly suggest testing this. Choose the right clustered index and not only will reads speed up, but you will probably see an increase in speed on the inserts as well.

    Post the actual execution plans from your queries for more detailed suggestions.

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply