SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Indexes with Include


Indexes with Include

Author
Message
yoelhalb
yoelhalb
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 56
Grant Fritchey (6/28/2009)
Paul White (6/28/2009)
Grant Fritchey (6/28/2009)
Every time I ran the query, it picked the widest set of includes, regardless of the order in which the indexes were created. I didn't check reads or timings.

I ran some tests on 2005 and 2008 with AdventureWorks and about the only constant was that if an exactly-covering index existed, it was chosen. (These were additional indexes I created - not the shipped ones).

Other than that, even with twenty indexes to choose from, with varying numbers of INCLUDEd columns, in different orders, no clear pattern emerged. I thought for a moment that indexes with the required column *last* in the creation order were preferred; but no.

Fascinating question, but I ran out of time with it.

Paul


Huh. Odd. I did the same thing. I had about five different indexes, all with the same key, but varying include columns, but duplicated, like the OP. Every single time, it chose the index with the most columns. Couldn't tell you why. I just didn't dig much past that yet.


I think it might make sense if we consider the fact that the index that covers the maximum columns is in fact the most reusable.
This is even more true with test data which are sometimes just a few rows only, since sql server anyway deals with pages and as such must anyway bring in entire page.
To test this farther we might want to use the following:
1) Have tables with lots of data
2) Test with indexes that radically differ in the number of included columns
3) Create a situation that is contradicting to caching, say by executing a query and after the results are already cached add a more covering index and then force recompilation of the query without clearing the cache (say by using option recompile) and see if it will use the cached index or the more covering one.

While I have not done so far any tests yet, the argument that I presented is assuming that the cost of using the different indexes are the same, however of this is not the case then we need to farther investigate.
( The cost can be determined by forcing the use of the indexes using index hints and then look at the execution plan to determine the cost)
SpringTownDBA
SpringTownDBA
Mr or Mrs. 500
Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)

Group: General Forum Members
Points: 582 Visits: 1499
I'm pretty sure that it depends on the way the index is accessed.

If it is accessed by an index seek (nested loop), then it shouldn't matter how many extra columns are included as they won't increase the cost of an individual seek. (Ok, if you had enough included columns to increase the depth of the index, then *maybe* it would prefer a narrower index, I would be suprised. Gail -- care to test this?)

If the index is accessed in a scan, then it should select the narrowest covering index (less IO). This is easy to see when you do " select clus_key from t " and it chooses to scan a non-clustered index.
Ninja's_RGR'us
Ninja's_RGR'us
One Orange Chip
One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)

Group: General Forum Members
Points: 29225 Visits: 9671
TheSQLGuru (6/30/2009)
>> negative faith

New term - I think I may like it! Although perhaps antifaith could be better. :-)


Or simply DTA = ANTItuning
nadersam
nadersam
Mr or Mrs. 500
Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)

Group: General Forum Members
Points: 530 Visits: 456
Dear All,

I have a question with regards to that please.
Ex: Index Idx1 on col1 with include on col2 and col3
The question is if i run and update statment as follows

Update Tbl1 set col2=anyvalue

So here i am updating the included column in the index will that update involve an update to the index as well?

Thanks

Nader
Jack Corbett
  Jack Corbett
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18724 Visits: 14899
nadersam (3/27/2013)
Dear All,

I have a question with regards to that please.
Ex: Index Idx1 on col1 with include on col2 and col3
The question is if i run and update statment as follows

Update Tbl1 set col2=anyvalue

So here i am updating the included column in the index will that update involve an update to the index as well?

Thanks

Nader


Yes, but unlike when you update a key column, only the leaf level of the index needs to updated.

If you run this code:


CREATE TABLE Test
(
col1 INT NOT NULL,
col2 CHAR(1),
col3 TINYINT
);
GO

CREATE NONCLUSTERED INDEX IX_test ON Test(col1) INCLUDE(col2, col3);
GO

INSERT INTO Test
SELECT DISTINCT
AC.object_id,
LEFT(OBJECT_NAME(AC.object_id), 1),
ABS(AC.object_id % 9)
FROM
master.sys.all_columns AS AC;
GO

UPDATE
Test
SET
col2 = 'c'
WHERE
col2 IS NULL;

GO

DROP TABLE TEST;



and show the actual execution plan, while you don't see it in the graphical execution plan if you look at the plan XML you'll see something like this:

<Update DMLRequestSort="false">
<Object Database="[tempdb]" Schema="[dbo]" Table="[Test]" IndexKind="Heap" />
<Object Database="[tempdb]" Schema="[dbo]" Table="[Test]" Index="[IX_test]" IndexKind="NonClustered" />



So you can see both table and index are updated. A tool like SQLSentry's Plan Explorer will show you details in a more readable format.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
nadersam
nadersam
Mr or Mrs. 500
Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)

Group: General Forum Members
Points: 530 Visits: 456
Dear Jack,

Thanks fro your reply, what i understand from your reply is that it has an impact but not that serious, is that right?., specially for tables with large number of records and update statements updating multiple records.


Jack Corbett (3/27/2013)
nadersam (3/27/2013)
Dear All,

I have a question with regards to that please.
Ex: Index Idx1 on col1 with include on col2 and col3
The question is if i run and update statment as follows

Update Tbl1 set col2=anyvalue

So here i am updating the included column in the index will that update involve an update to the index as well?

Thanks

Nader


Yes, but unlike when you update a key column, only the leaf level of the index needs to updated.

If you run this code:


CREATE TABLE Test
(
col1 INT NOT NULL,
col2 CHAR(1),
col3 TINYINT
);
GO

CREATE NONCLUSTERED INDEX IX_test ON Test(col1) INCLUDE(col2, col3);
GO

INSERT INTO Test
SELECT DISTINCT
AC.object_id,
LEFT(OBJECT_NAME(AC.object_id), 1),
ABS(AC.object_id % 9)
FROM
master.sys.all_columns AS AC;
GO

UPDATE
Test
SET
col2 = 'c'
WHERE
col2 IS NULL;

GO

DROP TABLE TEST;



and show the actual execution plan, while you don't see it in the graphical execution plan if you look at the plan XML you'll see something like this:

<Update DMLRequestSort="false">
<Object Database="[tempdb]" Schema="[dbo]" Table="[Test]" IndexKind="Heap" />
<Object Database="[tempdb]" Schema="[dbo]" Table="[Test]" Index="[IX_test]" IndexKind="NonClustered" />



So you can see both table and index are updated. A tool like SQLSentry's Plan Explorer will show you details in a more readable format.

Jack Corbett
  Jack Corbett
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18724 Visits: 14899
nadersam (3/27/2013)
Dear Jack,

Thanks fro your reply, what i understand from your reply is that it has an impact but not that serious, is that right?., specially for tables with large number of records and update statements updating multiple records.



Yes it has an impact, but you have to determine if that impact is significant for your workload. Will there be a lot of updates to the included column? Are there enough queries that return the included column to negate the maintenance impact of the updates?

If you run this script:

CREATE TABLE Test
(
col1 INT NOT NULL,
col2 CHAR(1),
col3 TINYINT
);
GO

INSERT INTO Test
SELECT DISTINCT
AC.object_id,
LEFT(OBJECT_NAME(AC.object_id), 1),
ABS(AC.object_id % 9)
FROM
master.sys.all_columns AS AC;
GO

/* See what happens in IO which is usually the most expensive part of the plan */
SET STATISTICS IO ON;

GO
RAISERROR('Point Update without an index', 10, 1) WITH nowait;
UPDATE
Test
SET
col2 = 'z'
WHERE
col1 = -1061705188;

GO
RAISERROR('Range Update without an index', 10, 1) WITH nowait;
UPDATE
Test
SET
col2 = 'c'
WHERE
col3 = 7;

GO

SET STATISTICS IO OFF;

GO

CREATE NONCLUSTERED INDEX IX_test ON Test(col1) INCLUDE(col2, col3);

GO

SET STATISTICS IO ON;

GO

RAISERROR('Point Update with an index with the column included ', 10, 1) WITH nowait;
UPDATE
Test
SET
col2 = 'y'
WHERE
col1 = -1061705188;

GO
RAISERROR('Range Update with an index with the column included ', 10, 1) WITH nowait;
UPDATE
Test
SET
col2 = 'd'
WHERE
col3 = 7;

GO

SET STATISTICS IO OFF;

GO

DROP INDEX dbo.TEst.IX_test;
GO

CREATE NONCLUSTERED INDEX IX_test ON Test(col1, col2, col3);

GO

SET STATISTICS IO ON;

GO

RAISERROR('Point Update with an index with the column as part of the key', 10, 1) WITH nowait;
UPDATE
Test
SET
col2 = 'x'
WHERE
col1 = -1061705188;
GO

RAISERROR('Range Update with an index with the column as part of the key ', 10, 1) WITH nowait;
UPDATE
Test
SET
col2 = 'e'
WHERE
col3 = 7;

GO

SET STATISTICS IO OFF;

GO

DROP TABLE TEST;



You can see the impact it has when doing a seek on the index key and when doing a range not using the index key.

In this example I've added clustered primary key:

CREATE TABLE Test
(
pkcol INT IDENTITY(1, 1)
PRIMARY KEY,
col1 INT NOT NULL,
col2 CHAR(1),
col3 TINYINT
);
GO

INSERT INTO Test
SELECT DISTINCT
AC.object_id,
LEFT(OBJECT_NAME(AC.object_id), 1),
ABS(AC.object_id % 9)
FROM
master.sys.all_columns AS AC;
GO

/* See what happens in IO which is usually the most expensive part of the plan */
SET STATISTICS IO ON;

GO
RAISERROR('Point Update using Clustered PK without an index', 10, 1) WITH nowait;
UPDATE
Test
SET
col2 = '1'
WHERE
col1 = 1;

GO
RAISERROR('Point Update without an index', 10, 1) WITH nowait;
UPDATE
Test
SET
col2 = 'z'
WHERE
col1 = -1061705188;

GO
RAISERROR('Range Update without an index', 10, 1) WITH nowait;
UPDATE
Test
SET
col2 = 'c'
WHERE
col3 = 7;

GO

SET STATISTICS IO OFF;

GO

CREATE NONCLUSTERED INDEX IX_test ON Test(col1) INCLUDE(col2, col3);

GO

SET STATISTICS IO ON;

GO
RAISERROR('Point Update using Clustered PK with an index with the column included', 10, 1) WITH nowait;
UPDATE
Test
SET
col2 = '2'
WHERE
col1 = 1;

GO
RAISERROR('Point Update with an index with the column included ', 10, 1) WITH nowait;
UPDATE
Test
SET
col2 = 'y'
WHERE
col1 = -1061705188;

GO
RAISERROR('Range Update with an index with the column included ', 10, 1) WITH nowait;
UPDATE
Test
SET
col2 = 'd'
WHERE
col3 = 7;

GO

SET STATISTICS IO OFF;

GO

DROP INDEX dbo.TEst.IX_test;
GO

CREATE NONCLUSTERED INDEX IX_test ON Test(col1, col2, col3);

GO

SET STATISTICS IO ON;

GO
RAISERROR('Point Update using Clustered PK with an index with the column as part of the key', 10, 1) WITH nowait;
UPDATE
Test
SET
col2 = '3'
WHERE
col1 = 1;

GO
RAISERROR('Point Update with an index with the column as part of the key', 10, 1) WITH nowait;
UPDATE
Test
SET
col2 = 'x'
WHERE
col1 = -1061705188;
GO

RAISERROR('Range Update with an index with the column as part of the key ', 10, 1) WITH nowait;
UPDATE
Test
SET
col2 = 'e'
WHERE
col3 = 7;

GO

SET STATISTICS IO OFF;

GO

DROP TABLE TEST;





Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
nadersam
nadersam
Mr or Mrs. 500
Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)

Group: General Forum Members
Points: 530 Visits: 456
Dear Jack,

Thanks again for your valuable information, i will try it and let you know.

Regards
Nader
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search