Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2007
    Posts
    97

    Unanswered: SQL Server takes Index instead of Primary Key

    Hallo,

    Following scenario:


    Table1 with column: Year char(4), Quarter char(1), ID decimal(10,0), Hits int
    PK_table1 on Year,Quarter,Hits

    If i do a "Select Year From table1 group by Year", the executionplan looks like
    Code:
    select year from dbo.table1 group by year	1	1	0	NULL	NULL	1	NULL	4	NULL	NULL	NULL	86,21644	NULL	NULL	SELECT	0	NULL
      |--Stream Aggregate(GROUP BY:([Testdb].[dbo].[Table1].[YEAR]))	1	2	1	Stream Aggregate	Aggregate	GROUP BY:([Testdb].[dbo].[Table1].[YEAR])	NULL	4	0	6E-06	11	86,21644	[Testdb].[dbo].[Table1].[YEAR]	NULL	PLAN_ROW	0	1
           |--Parallelism(Gather Streams, ORDER BY:([Testdb].[dbo].[Table1].[YEAR] ASC))	1	3	2	Parallelism	Gather Streams	ORDER BY:([Testdb].[dbo].[Table1].[YEAR] ASC)	NULL	8	0	0,02854749	11	86,21643	[Testdb].[dbo].[Table1].[YEAR]	NULL	PLAN_ROW	1	1
                |--Stream Aggregate(GROUP BY:([Testdb].[dbo].[Table1].[YEAR]))	1	4	3	Stream Aggregate	Aggregate	GROUP BY:([Testdb].[dbo].[Table1].[YEAR])	NULL	8	0	3,624843	11	86,18788	[Testdb].[dbo].[Table1].[YEAR]	NULL	PLAN_ROW	1	1
                     |--Clustered Index Scan(OBJECT:([Testdb].[dbo].[Table1].[PK_Table1]), ORDERED FORWARD)	1	5	4	Clustered Index Scan	Clustered Index Scan	OBJECT:([Testdb].[dbo].[Table1].[PK_Table1]), ORDERED FORWARD	[Testdb].[dbo].[Table1].[YEAR]	1,449936E+07	74,58831	7,974729	11	82,56304	[Testdb].[dbo].[Table1].[YEAR]	NULL	PLAN_ROW	1	1
    Now, with another index IX_Hits on hits and the same sql query, sql server now takes IX_Hits instead of PK_table1. And, it takes more time. Any idea why?

    Code:
    select Year from dbo.table1 group by Year	1	1	0	NULL	NULL	1	NULL	4	NULL	NULL	NULL	85,54985	NULL	NULL	SELECT	0	NULL
      |--Sort(DISTINCT ORDER BY:([Testdb].[dbo].[Table1].[YEAR] ASC))	1	2	1	Sort	Distinct Sort	DISTINCT ORDER BY:([Testdb].[dbo].[Table1].[YEAR] ASC)	NULL	4	0,01126126	0,000137451	11	85,54985	[Testdb].[dbo].[Table1].[YEAR]	NULL	PLAN_ROW	0	1
           |--Parallelism(Gather Streams)	1	3	2	Parallelism	Gather Streams	NULL	NULL	8	0	0,02850749	11	85,53845	[Testdb].[dbo].[Table1].[YEAR]	NULL	PLAN_ROW	1	1
                |--Hash Match(Partial Aggregate, HASH:([Testdb].[dbo].[Table1].[YEAR]), RESIDUAL:([Testdb].[dbo].[Table1].[YEAR] = [Testdb].[dbo].[Table1].[YEAR]))	1	4	3	Hash Match	Partial Aggregate	HASH:([Testdb].[dbo].[Table1].[YEAR]), RESIDUAL:([Testdb].[dbo].[Table1].[YEAR] = [Testdb].[dbo].[Table1].[YEAR])	NULL	8	0	49,6358	11	85,50995	[Testdb].[dbo].[Table1].[YEAR]	NULL	PLAN_ROW	1	1
                     |--Index Scan(OBJECT:([Testdb].[dbo].[Table1].[IX_Table1_Hits]))	1	5	4	Index Scan	Index Scan	OBJECT:([Testdb].[dbo].[Table1].[IX_Table1_Hits])	[Testdb].[dbo].[Table1].[YEAR]	1,449936E+07	27,89942	7,974729	11	35,87415	[Testdb].[dbo].[Table1].[YEAR]	NULL	PLAN_ROW	1	1
    Thank You!!

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    If I were to guess...
    It has used the NC index since the CI keys are included in it. Since it is scanning the entire index in either case, the optimiser probably expects to have to scan less pages scanning the NC index. If you look at estimated IO it is much lower for the NC index scan the C index scan.

    What does statistics IO tell you?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Mar 2007
    Posts
    97
    Quote Originally Posted by pootle flump
    What does statistics IO tell you?
    What's that?

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    It is a T-SQL command that that returns the io statistics for a given query. Try looking up "SET STATISTICS_IO ON" in BoL. Post the stats of your two queries.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Mar 2007
    Posts
    97
    Ok, thanks. I'll have to answer that next week. I can't access the server till then.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Super. It will be interesting comparing the estimated reads (as per showplan) and the actual reads. I wonder if the optimiser is over estimating the size of your table compared to the NC index....
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •