July 23, 2013 at 3:35 pm
Hi All,
I have a table [BusinessProcesses] with BpId as primary/clustered key/index on it.
I have the following index on column CorrespondingContractNumber:
CREATE NONCLUSTERED INDEX [IBPCorrespondingContractNumber] ON [dbo].[BusinessProcesses]
([CorrespondingContractNumber] ASC)
I execute:
dbcc show_statistics('dbo.BusinessProcesses','IBPCorrespondingContractNumber')
/*
All densityAverage LengthColumns
8.592541E-051.272107CorrespondingContractNumber
1.788491E-085.272107CorrespondingContractNumber, BPId
*/
I create the next index:
CREATE NONCLUSTERED INDEX [IBPCorrespondingContractNumber2] ON [dbo].[BusinessProcesses]
([CorrespondingContractNumber] ASC)
INCLUDE(BpId)
I execute:
dbcc show_statistics('dbo.BusinessProcesses','IBPCorrespondingContractNumber2')
/*
All densityAverage LengthColumns
3.906723E-061.311324CorrespondingContractNumber
*/
The second index is more selective.
I execute this example query:
select CorrespondingContractNumber
from BusinessProcesses
where CorrespondingContractNumber is not null
and the second index is being used by the query optimizer. Conclusion: The second index (IBPCorrespondingContractNumber2) is narrower and the optimizer simply uses it.
Then i create
CREATE NONCLUSTERED INDEX [IBPCorrespondingContractNumber3] ON [dbo].[BusinessProcesses]
([CorrespondingContractNumber] ASC,
bpid asc)
dbcc show_statistics('dbo.BusinessProcesses','IBPCorrespondingContractNumber3')
/*
All densityAverage LengthColumns
3.906723E-061.311324CorrespondingContractNumber
1.788491E-085.311324CorrespondingContractNumber, BPId
*/
I execute this example query:
select CorrespondingContractNumber
from BusinessProcesses
where CorrespondingContractNumber is not null
Now the optimizer uses IBPCorrespondingContractNumber3 which is not narrower thatn IBPCorrespondingContractNumber2
I need someones of you to put here your thinking about this...
Thank you in advance,
IgorMi
Igor Micev,My blog: www.igormicev.com
July 24, 2013 at 3:36 am
I suspect the index used by the optimizer is smaller, rather than narrower.
SELECT
i.name,
SUM(ips.page_count) AS page_count,
MAX(ips.index_depth) AS index_depth
FROM
sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('BusinessProcesses'), NULL, NULL, 'DETAILED') ips
INNER JOIN
sys.indexes i
ON ips.object_id = i.object_id
AND ips.index_id = i.index_id
GROUP BY
i.name
ORDER BY
page_count DESC;
July 24, 2013 at 3:47 am
Hi
This is the output from your query:
namepage_countindex_depth
IBPCorrespondingContractNumber798454
IBPCorrespondingContractNumber2795023
IBPCorrespondingContractNumber3795023
The clustered index on BpId:
namepage_countindex_depth
IBPContentId5763204
It seems it's not the size, especially for the last two indexes.
Thank you, anyway.
Igor Micev,My blog: www.igormicev.com
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply