Impacts of include index

Question

Hi,

I am using sql server 2008 and I have some complex queries which select 10 to 15 columns. Below is the sample query


select col1,col2,col3,col4,col5,col6,col7,col8,col9,col10 from TABLE_A a inner join TABLE_B b on a.id = b.id
inner join TABLE_C c on b.keyid = c.keyid inner join TABLE_D d on c.mid = d.mid
where a.name = 'abc' and a.status = '1'

Here TABLE_A is a huge table and has large number of records. Rest of the tables are small. The query is frequently hit.

if I run the query it takes a lot of time. Then I added the below index

create index ix_com on TABLE_A(name,status) INCLUDE(col1,col2,col3,col4,col5,col6,col7,col8,col9,col10)

Now it is working fine. Below are some of the questions that raised to me. As I may be opting for Include index in my future performance related issues.

what are the impacts of include index ?
Will this impact the DML operations?
If there are some other query in which the selected columns are col11,col12,col13 of TABLE_A should I again create an include index for col11,col12,col13 ?
Again if I keep on creating include index for all the frequently selected queries what will be the disadvantages and will this delay the DML operations?
what is the best approach for this?

Thanks,
Rakesh.

---

Please mark as helpful and propose as answer if you find this as correct!!! Thanks, Rakesh.

Answer 1

what are the impacts of include index ?

Largely depend on the datatype of columns. Columns are included according to your WHERE clause and sequence used in index and WHERE clause have also large Impact.
Will this impact the DML operations?

Again DATATYPE is the key, I have a table of over 10 billion records and have index on a encrypted column field it KILLS the bulk insert statement in that table.
If there are some other query in which the selected columns are col11,col12,col13 of TABLE_A should I again create an include index for col11,col12,col13 ? 

No you don't need to create another index. previously created index will be used.
Again if I keep on creating include index for all the frequently selected queries what will be the disadvantages and will this delay the DML operations? 

Yes it will delay the DML operation, it we create large no of indexes on table. also it will effect the storage.
what is the best approach for this?

Analyse all of your select statements and where clause, Best way is to create indexes on frequently used columns in where clause if your query is executing on large no of times.

Answer 2

1.what are the impacts of include index ?

--Select Operation will fast.There is little impact on DML.Use Performance Dashboard will inform missing indexes details.5-10 million records indexes took around 10-20 minutes.

2.Will this impact the DML operations?--as said in 1st

Also Add one column date time will explain how much time will take when insert in sec duration.

check execution plan and cost.

3.If there are some other query in which the selected columns are col11,col12,col13 of TABLE_A should I again create an include index for col11,col12,col13 ?

--Use Dashboard for index and check execution plan where full scan happen for particular query.

4.Again if I keep on creating include index for all the frequently selected queries what will be the disadvantages and will this delay the DML operations?
--In General people say it will affect but the impact on select statement more than DML slower.

---

-- Bhavesh Thakkar

Sr.Database Administrator
My Blog
Linked In Profile

Answer 3

The following article is on covering index:

http://www.sqlusa.com/bestpractices/coveringindex/

Most important considerations in indexing is the best support for business critical queryies and just ok support for the rest. A systematic way is preferred to determine which are the business critical queries. You need to design the best indexing for business critical queries with the least negative impact on other queries. An art.

---

Kalman Toth Database & OLAP Architect
Paperback / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

Answer 4

1.what are the impacts of include index ?

--Select Operation will fast.There is little impact on DML.

The impact is, that if any of these included columns is changed, that this index needs to be updated as well.

Basically all INSERTS and DELETES get slower when you add another index. Every UPDATE gets slower when you included the updated column in another index.

So it really depends on the priorities; if you assume that disk space is not an issue, then it is a tradeoff between SELECT performance and INSERT/UPDATE/DELETE performance. There is no such thing as a free ride.

As long as you satisfied with the DML performance, you can keep adding indexes until you hit the maximum number of indexes per table. If you are on SQL Server 2008, this is limited to 999. On SQL Server 2005 or earlier, this is 249... in addition to one clustered index.

To answer a few of your other questions: if the index is used, and it covers the query (meaning all required columns for the query can be found in the index) then no additional table access is needed. So it would speed up the query; it can be "reused".

---

Gert-Jan

Answer 5

You've essentially created a new table with the clustered key of (name,status) that has the columns of (col1,col2,col3,col4,col5,col6,col7,col8,col9,col10).

Every time the base table is inserted updated, it's like a trigger fires to update the new table (index).  

Each index you create is implicitly a new table, clustered by the index columns and populated with the data from the include columns and modified each time the base table is modified just like a trigger would.

The best approach is to make sure you have the best clustered index chosen.  One that will be impervious to fragmentation yet will facilitate the most queries so you don't have a ton of key lookups against the clustered index and instead the clustered index facilitates the most queries.

---

Derek SQLPlanet.com