October 11, 2012 at 6:56 pm
Thre is a table with 387912 records which there's 2 non-clustered indexes on it(all the stats are updated ).when executing this query:
SELECT count(*) FROM PointCurrent WHERE PointTypeID=1
the optimizer uses the index "[IX_1]" and not the "[IX_2]".when I run the query with the "with(index(IX_2))" table option the logical reads are 868 but executing the query with the "with(index(IX_1))" performs 680 logical reads.
here's the question :the "IX_2" has narrower index key (PointTypeID) compared to the "IX_1" ([PointTypeID] ,[BackTotal]) so logically, seeking that should have less cost compared to "IX_1".So why my guess is not right? any idea?
-----------------------------------------
-----------------------------------------
CREATE TABLE [dbo].[PointCurrent](
[MemberID] [int] NOT NULL,
[PointTypeID] [int] NOT NULL,
[BackTotal] [int] NOT NULL,
[Level] [int] NOT NULL,
[Rank] [int] NOT NULL,
CONSTRAINT [PK_PointCurrent] PRIMARY KEY CLUSTERED
(
[MemberID] ASC,
[PointTypeID] ASC
)
--------------------------------------------------
--------------------------------------------------
CREATE NONCLUSTERED INDEX [IX_1] ON [dbo].[PointCurrent]
(
[PointTypeID] ASC,
[BackTotal] ASC
)
--------------------------------------------------
--------------------------------------------------
CREATE NONCLUSTERED INDEX [IX_2] ON [dbo].[PointCurrent]
(
[PointTypeID] ASC
)
Pooyan
October 11, 2012 at 11:57 pm
i do think narrow index should have lesser logical reads
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
October 12, 2012 at 1:26 am
Low page density from fragmentation or deletes.
p.s. The smaller index is redundant, it's unlikely to be needed.
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
October 12, 2012 at 1:35 am
Gail,
Here OP says that statistics are updated still delete can play role here ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
October 12, 2012 at 1:46 am
Yes. Statistics updates have no effect whatsoever on the structure, density, fragmentation or layout of an index.
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
October 12, 2012 at 2:08 am
GilaMonster (10/12/2012)
Yes. Statistics updates have no effect whatsoever on the structure, density, fragmentation or layout of an index.
structure and layout of index i can understand but how come density and fragmentation.. as we know that DML ( here delete )
operations affects the fragmentation which leads to out dated statistics ( major difference in actual rows and expected rows in exec plan)
and also please explain
smaller index is redundant, it's unlikely to be needed
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
October 12, 2012 at 2:16 am
Bhuvnesh (10/12/2012)
GilaMonster (10/12/2012)
Yes. Statistics updates have no effect whatsoever on the structure, density, fragmentation or layout of an index.structure and layout of index i can understand but how come density and fragmentation.. as we know that DML ( here delete )
operations affects the fragmentation which leads to out dated statistics ( major difference in actual rows and expected rows in exec plan)
Delete doesn't cause fragmentation and fragmentation does not lead to outdated statistics. Updating stats just updates the distribution statistics structure, it doesn't do anything to the actual index.
and also please explain
smaller index is redundant, it's unlikely to be needed
What do you need me to explain? The index is redundant, it's a subset of an existing index
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
October 12, 2012 at 2:52 am
GilaMonster (10/12/2012)
What do you need me to explain? The index is redundant, it's a subset of an existing index
So you mean to say here that another ( single colum index ) will never get used
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
October 12, 2012 at 11:18 am
GilaMonster (10/12/2012)
Low page density from fragmentation or deletes.p.s. The smaller index is redundant, it's unlikely to be needed.
you're right the fill factor is set to 80%.It's true the smaller index is redundant but since this query is part of a proc that is executed so many times and is one of top 10 CPU intensive queries on the system and there's not so many inserts,deletes,updates on the tables and 77% of the query cost is because of seeking the bigger index ,it is useful to have the smaller redundant index.I should maybe increase the fill-factor though.Thanks
Pooyan
October 12, 2012 at 11:35 am
Bhuvnesh (10/12/2012)
GilaMonster (10/12/2012)
What do you need me to explain? The index is redundant, it's a subset of an existing indexSo you mean to say here that another ( single colum index ) will never get used
By increasing the fill-factor of the larger index now the optimizer is using the narrower index and the query cost decreased by 15%
Pooyan
October 12, 2012 at 12:02 pm
Bhuvnesh (10/12/2012)
GilaMonster (10/12/2012)
What do you need me to explain? The index is redundant, it's a subset of an existing indexSo you mean to say here that another ( single colum index ) will never get used
No, I did not mean to say it will never be used, because that would be false. It will be used. It is however unnecessary in the vast majority of cases and hence can be dropped.
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
October 12, 2012 at 12:04 pm
pooyan_pdm (10/12/2012)
GilaMonster (10/12/2012)
Low page density from fragmentation or deletes.p.s. The smaller index is redundant, it's unlikely to be needed.
you're right the fill factor is set to 80%.It's true the smaller index is redundant but since this query is part of a proc that is executed so many times and is one of top 10 CPU intensive queries on the system and there's not so many inserts,deletes,updates on the tables and 77% of the query cost is because of seeking the bigger index ,it is useful to have the smaller redundant index.I should maybe increase the fill-factor though.Thanks
77% of what? What really is the difference between using the two indexes? Not in query plan % (which is an estimate and a % of a fairly meaningless number), but in terms of resources used?
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
October 12, 2012 at 12:31 pm
the cost of seeking the larger index was 77% of the whole Sp execution cost.
for the query I mentioned "select count(*) ..." the logical reads dropped from 1023 to just 376
Pooyan
October 12, 2012 at 12:39 pm
the query is not just the "SELECT count(*) FROM PointCurrent WHERE PointTypeID=1" it's an left join query like this "SELECT count(*) FROM PointCurrent WHERE PointTypeID=someOthertable.PointTypeID" so using the smaller index makes a considerable difference.
Pooyan
October 12, 2012 at 1:11 pm
A 4 byte vs 8 byte index shouldn't cause that much of an effect. Post the execution plans please.
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
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply