November 27, 2012 at 8:32 am
Hi,
If a table as an index with Col1, Col2, Col3 and includes Col5, Col4, Col7 (by this order) this index is obsolete if we have another index with Col1, Col2, Col3 and includes Col4, Col5, Col6, Col7, right?
But what about an index with Col1, Col2, Col3 compared with Col1, Col3, Col2 ?!
Is it worth having both indexes?
The queries can have any column on the search criteria since the App UI allows it.
Thanks,
Pedro
November 27, 2012 at 8:45 am
PiMané (11/27/2012)
Hi,If a table as an index with Col1, Col2, Col3 and includes Col5, Col4, Col7 (by this order) this index is obsolete if we have another index with Col1, Col2, Col3 and includes Col4, Col5, Col6, Col7, right?
Yes.
But what about an index with Col1, Col2, Col3 compared with Col1, Col3, Col2 ?!
Is it worth having both indexes?
Maybe.
http://www.sqlservercentral.com/articles/Indexing/68636/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 27, 2012 at 8:49 am
Check the below link
http://channel9.msdn.com/Events/TechEd/NorthAmerica/2012/DBI406
November 27, 2012 at 8:51 am
In a first approach is it "wise" to just create one of the indexes (Col1, Col2, Col3) and not both (Col1, Col3, Col2) and after analyzing the workload for a few days/weeks see what SQL Server engine suggests?
Start from a simple approach and not the "big picture" since we can be creating indexes that won't be used?
If we have a 4 columns searchable table we can have loads of indexes or just create 4 (start with one different column and add the others by any order)...
Thanks,
Pedro
November 27, 2012 at 9:52 am
Just one more thing...
Is Col1, Col2, Col3 equal to having Col1, Col2 ?
Some tables have indexes like this an the wider indexes probably make the other obsolete..
Thanks,
Pedro
November 28, 2012 at 4:35 am
GilaMonster (11/27/2012)
PiMané (11/27/2012)
Hi,If a table as an index with Col1, Col2, Col3 and includes Col5, Col4, Col7 (by this order) this index is obsolete if we have another index with Col1, Col2, Col3 and includes Col4, Col5, Col6, Col7, right?
Yes.
But what about an index with Col1, Col2, Col3 compared with Col1, Col3, Col2 ?!
Is it worth having both indexes?
Maybe.
Hi,
After reading your blog about indexes http://sqlinthewild.co.za/index.php/2011/11/11/sql-university-advanced-indexing-indexing-strategies/ I made a query to get "duplicate" indexes considering that the first two columns are important and the other can be "joined", for example an index col1, col2, col3 and col1, col2, col5 can be "joined" in a col1, col2, col3, col5... I'm creating wider indexes and removing small ones.
I'm still not considering the include columns but joining two "duplicate" indexes include columns is the next step..
DECLARE @FirstColumnsKeys INT = 2
;WITH IndexInfo AS (
SELECT
SCHEMA_NAME(t.schema_id) SchemaName,
t.name TableName,
i.name IndexName,
(SELECT ' ' + CAST(ic.column_id AS VARCHAR(10)) + ' ' FROM sys.index_columns ic WHERE i.index_id = ic.index_id AND i.object_id = ic.object_id AND ic.is_included_column = 0 AND ic.index_column_id <= @FirstColumnsKeys ORDER BY ic.index_column_id FOR XML PATH('')) FirstKeyColumns,
(SELECT ' ' + CAST(ic.column_id AS VARCHAR(10)) + ' ' FROM sys.index_columns ic WHERE i.index_id = ic.index_id AND i.object_id = ic.object_id AND ic.is_included_column = 0 AND ic.index_column_id > @FirstColumnsKeys ORDER BY ic.column_id FOR XML PATH('')) OtherKeyColumns,
(SELECT ' ' + CAST(ic.column_id AS VARCHAR(10)) + ' ' FROM sys.index_columns ic WHERE i.index_id = ic.index_id AND i.object_id = ic.object_id AND ic.is_included_column = 1 ORDER BY ic.column_id FOR XML PATH('')) IncludeColumns
FROM sys.indexes i
INNER JOIN sys.tables t ON i.object_id = t.object_id AND t.is_ms_shipped = 0
WHERE i.type != 0)
SELECT i1.* FROM
IndexInfo i1 INNER JOIN IndexInfo i2 ON i1.SchemaName = i2.SchemaName AND i1.TableName = i2.TableName AND i1.IndexName <> i2.IndexName AND i1.FirstKeyColumns = i2.FirstKeyColumns
ORDER BY i1.SchemaName, i1.TableName, i1.FirstKeyColumns, i1.OtherKeyColumns, i1.IndexName
Still working on the query but this is as it is so far...
Is this assumption, that the first two columns are what really matters and the other can be joined, valid for foreign keys without indexes? If I have a FK on col2, col3 and col4 can an index with col2, col3, col5, col4 be considered "good" for FK validation?
Thanks,
Pedro
November 28, 2012 at 6:42 am
Pedantic nitpick. The ; is a statement terminator. It does not begin statements. ;WITH is an abomination that I wish would go away.
PiMané (11/28/2012)
DECLARE @FirstColumnsKeys INT = 2;
WITH IndexInfo AS (
Is this assumption, that the first two columns are what really matters and the other can be joined, valid for foreign keys without indexes?
Maybe. It depends how unique the combinations are (and that goes in general, not for foreign keys).
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 28, 2012 at 6:53 am
GilaMonster (11/28/2012)
Pedantic nitpick. The ; is a statement terminator. It does not begin statements. ;WITH is an abomination that I wish would go away.
Agree... sorry 🙂
Maybe. It depends how unique the combinations are (and that goes in general, not for foreign keys).
Do the indexes on FKs have to have the exact same columns by the same order to have impact?
Thanks,
Pedro
November 28, 2012 at 8:20 am
About FKs and indexes...
If a FK has Col1, Col2, Col3 by this order is a Col1, Col2 index any good?
It's probably better than nothing but should a Col1, Col2, Col3 index exist or a Col1, Col2, Col3, Col4, .... A Col1, Col2, Col4, Col3 probably is the same as just Col1, Col2 for a FK validation...
Thanks,
Pedro
November 28, 2012 at 9:14 am
PiMané (11/28/2012)
About FKs and indexes...If a FK has Col1, Col2, Col3 by this order is a Col1, Col2 index any good?
yes but always (not there, where col3 is involve)
PiMané (11/28/2012)
About FKs and indexes...It's probably better than nothing but should a Col1, Col2, Col3 index exist or a Col1, Col2, Col3, Col4, .... A Col1, Col2, Col4, Col3 probably is the same as just Col1, Col2 for a FK validation...
no .. i will suggest you to read article referred/written by Gail
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
November 28, 2012 at 9:20 am
Hi,
I read it...
A wide index with Col1, Col2, Col4, Col3 with a FK Col1, Col2, Col3 seems the same as an index Col1, Col2 since the "big" one will make a seek on Col1, Col2 and then a scan on Col4 for Col3 since it has no way of seeking Col3 directly.
So the "big" index and the Col1, Col2 seem the same... aside the small index needs less reads to get the same data from Col1, Col2...
Also about FKs... I have a table with 10 FKs... by creating indexes for all FKs will be adding 10 indexes... Is that a good thing to do? One wide index is better than lots thinner indexes but FKs should have indexes and most of then are thinner (1 or 2 columns tops) ... It's a bit contradictory...
Thanks,
Pedro
November 29, 2012 at 2:46 am
Hi,
Regarding this question I read some articles that consider partial duplicate indexes those indexes with the same first column and "reversible" indexes those indexes with the same columns but with reverse order.
In cases where the application can filter by any table column, say the table has 30 or 40 columns, the only way to determine the best index(es) is by monitoring for some time the app usage and then determine the best one(s)...
But in any case the FKs indexes should always be created ... but should they all have the exact columns and order?
For example:
Table1: T1_1, T1_2, T1_3, T1_4
Table2: T2_1, T2_2, T2_3
Table3: T3_1, T3_2, T3_3, T3_4, T3_5
Table2 references Table1 => T2_1 => T1_1 and T2_2 => T1_3
Table3 references Table1 => T3_1 => T1_1 and T3_2 => T1_2
In this example should we create 4 indexes (2 for T1 and 1 for T2 and T3) or is 3 enough (1 for T1 with T1_1, T1_2, T1_3) ? Or assuming T1 has less than 1.000 rows should and index on T1_1 be enough?
Thanks,
Pedro
November 29, 2012 at 4:59 am
PiMané (11/28/2012)Also about FKs... I have a table with 10 FKs... by creating indexes for all FKs will be adding 10 indexes... Is that a good thing to do? One wide index is better than lots thinner indexes but FKs should have indexes and most of then are thinner (1 or 2 columns tops) ... It's a bit contradictory...
I asked this cause we have a table with 30 FKs with just one column... 30 narrow indexes...
They won't have impact on updates (not much at least) cause they are on data that isn't changed a lot once it's inserted (payment method, dispatch method, ...).
So it's probably better to have them so the check validations are fast (seeks instead of scans), right?
Thanks,
Pedro
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply