July 15, 2009 at 2:46 pm
I have a table which get populated each time a user clicks on a page. This table is only used for running selects and has approx 74 million rows. Data is not partitioned nor archived. If i create a NC index on 3- columns which is used in reporting the exceution plan shows a Index seek and sql is comparitively less expensive. Will this index have a negative impact ? Any inputs will be greatly appreciated. TIA
July 15, 2009 at 4:47 pm
ishaan99 (7/15/2009)
I have a table which get populated each time a user clicks on a page. This table is only used for running selects and has approx 74 million rows. Data is not partitioned nor archived. If i create a NC index on 3- columns which is used in reporting the exceution plan shows a Index seek and sql is comparitively less expensive. Will this index have a negative impact ? Any inputs will be greatly appreciated. TIA
An index, like the one you just created, which contains all the columns needed to satisfy a query is know as a "covering" index in SQL. A covering index is the closest thing you can have in SQL to multiple clustered indexes. As you have seen, it provides better performance for any query which can be satisfied by the columns contained in the index, because it doesn't have to access the clustered index for any additional data. So, a covering index actually performs much like a clustered index as long as the query asks for no columns other than those in the covering index itself.
With any covering or noncovering index, the costs are (1) additional work for the server whenever a row in the primary table is inserted, updated, or deleted, and (2) additional disk space. This is often a price worth paying to lessen the load created by commonly used queries.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
July 16, 2009 at 4:02 am
If your reporting needs are not frequent, and as you say that you have frequent updates and inserts i will suggest you to closly monitor performance related impact as speeding up one thing might slow down the other.
July 16, 2009 at 6:12 am
Inserts will absolutely be slower. There's no way to get around the fact that you'll have to udpate the index as part of the insert. So, as previously stated, you need to monitor to see if the performance benefit outweighs the performance cost, because there will be a cost.
----------------------------------------------------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 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply