Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

Indexes with Include Expand / Collapse
Author
Message
Posted Monday, November 28, 2011 10:51 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 3, 2013 10:41 AM
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)
Post #1212905
Posted Tuesday, November 29, 2011 2:07 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, April 1, 2014 3:26 PM
Points: 316, Visits: 1,497
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.
Post #1213011
Posted Tuesday, November 29, 2011 4:58 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Friday, September 12, 2014 10:44 AM
Points: 20,572, Visits: 9,617
TheSQLGuru (6/30/2009)
>> negative faith

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


Or simply DTA = ANTItuning
Post #1213082
Posted Wednesday, March 27, 2013 7:04 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, April 7, 2014 5:18 AM
Points: 85, Visits: 205
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
Post #1435883
Posted Wednesday, March 27, 2013 7:38 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 12:10 PM
Points: 10,191, Visits: 13,115
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

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
Post #1435906
Posted Wednesday, March 27, 2013 8:13 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, April 7, 2014 5:18 AM
Points: 85, Visits: 205
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.
Post #1435927
Posted Wednesday, March 27, 2013 8:57 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 12:10 PM
Points: 10,191, Visits: 13,115
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

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
Post #1435961
Posted Thursday, March 28, 2013 12:53 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, April 7, 2014 5:18 AM
Points: 85, Visits: 205
Dear Jack,

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

Regards
Nader
Post #1436281
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse