Confused over Clustered Index seek

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Confused over Clustered Index seek

Author

Topic

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2009-07-21 : 10:56:01

This query takes about a minute, when the cache is clear:

select
  #clm.ID, max(d.Date)
FROM 
  Docket d (NOLOCK), #CLM 
WHERE 
  d.TimeCode = 'IB' AND d.ClaimID = #clm.ID
group by
  #clm.ID

The #CLM table is small (about 1000 rows) the Docket table is big (about 20 million rows). The docket table has an index on (ClaimID, TimeCode) and a Clustered index on ID.

The execution plan says most of the time is going towards a Clustered Index Seek on Docket.ID. Anyone have any ideas why? I don't join or select on it.

Most of the time is spent bringing a couple thousand pages from Disk to cache. Anyone have any ideas on speed improvements? I am thinking of creating a partitioned index on Docket.ClaimID.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-21 : 11:39:37

Try this rewrite

SELECT		c.ID,
		d.Date
FROM		#Clm AS c
INNER JOIN	(
			SELECT		ClaimID,
					MAX(Date) AS Date
			FROM		Docket WITH (NOLOCK)
			WHERE		TimeCode = 'IB'
			GROUP BY	ClaimID
		) AS d ON d.ClaimID = c.ID

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-21 : 11:40:52

Include Date column in your {ClaimID, TimeCode} index to make that index covering.

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-21 : 11:42:18

If you get a clustered index seek, you probably also get a KEY LOOKUP (or BOOKMARK LOOKUP) in your execution plan. This can happen, since SQL Server prefer sequential reads (scans) before random reads (seek).
Sequential reads are less costly for SQL Server.

N 56°04'39.26"
E 12°55'05.63"

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2009-07-21 : 11:52:32

quote:
Originally posted by Peso

If you get a clustered index seek, you probably also get a KEY LOOKUP (or BOOKMARK LOOKUP) in your execution plan. This can happen, since SQL Server prefer sequential reads (scans) before random reads (seek).
Sequential reads are less costly for SQL Server.

N 56°04'39.26"
E 12°55'05.63"

Thanks a lot.

I did not get a KEY LOOKUP (or BOOKMARK LOOKUP).

Will try the query rewrite but I am trying an index ReBuild currently. Says it had scan density of 12%. Do you think this could help?

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2009-07-21 : 11:56:51

quote:
Originally posted by Peso

Include Date column in your {ClaimID, TimeCode} index to make that index covering.

N 56°04'39.26"
E 12°55'05.63"

I agree that should work. But, for all the other queries in this database on the table, I would have to add maybe another 20+ indexes on 3 columns or even more for queries involving 4 or 5 columns... which I just might do. Of course if there is a new query with a different combination of 3 columns, then I would need yet another index.

But I am wondering if the Partitioned Index might be a more 'permanent' solution. And requiring less indexes so the inserts/updates won't suffer.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-21 : 13:22:53

Are you sure it was a clustered index seek, and not a clustered index scan?

How do my rewrite compare to your original query in terms of duration and cpu?

N 56°04'39.26"
E 12°55'05.63"

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2009-07-21 : 13:43:19

quote:
Originally posted by Peso

Are you sure it was a clustered index seek, and not a clustered index scan?

How do my rewrite compare to your original query in terms of duration and cpu?

N 56°04'39.26"
E 12°55'05.63"

OK. Just ran it. Roughly the same time and CPU.

Again looks like most of the time is caching the pages. Subsequent runs are like bullets for both queries.

Both have clustered Index seek.

The index rebuild didn't seem to help (damn.).

Still not sure why it wants to cache so many pages.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-21 : 14:54:34

What about a CROSS APPLY?

SELECT		c.ID,
		d.Date
FROM		#Clm AS c
CROSS APPLY	(
			SELECT	MAX(q.Date) AS Date
			FROM	Docket AS q WITH (NOLOCK)
			WHERE	q.ClaimID = c.ClaimID
				AND q.TimeCode = 'IB'
		) AS d

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources