execution plan question - SQL Server Forums

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

Old Forums

CLOSED - General SQL Server

execution plan question

Author

Topic

Tim
Starting Member

392 Posts

Posted - 2003-07-21 : 02:16:42

I have this table...

create table K
(
k1 uniqueidentifier not null,
k2 uniqueidentifier not null,
k3 uniqueidentifier not null,
k4 uniqueidentifier not null
)

create unique clustered index PK on K (k1,k2,k3)

create index K4 on K (k1,k2,k4)

select * from K order by k1,k2,k3

select * from K order by k1,k2,k4

Execution plan for the two queries uses the appropriate index as I expected.

But in production, there are only about 1500 records. The execution plan always uses the clustered index for both queries.

Why is this?

Does it think it will be faster to physically access the clustered index?

Would it be different if I used shorter keys?

How do I know how many records it will take to tip the balance? (am considering not having the second index if it doesn't get selected)

thanks for any help

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-07-21 : 03:15:38

Hi Tim!

What if use an index hint: from ... with index(...)
and compare execution times? But too few records in the table.

- Vit

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-07-21 : 05:50:19

Hm... Tim,

how did you manage to stay on the Starting Member while
having more than 300 posts??

- Vit

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-21 : 09:41:34

I think Tim asked graz to leave him that way...

No! Force an index when all is lost.

Tim:

A couple of things. What's in your select statement (more than just the key columns I suspect). If it was only thopse three, then I would suspect INDEX SEEK...but he prob has to go to the data pages...in which case he (ther I go again, the optimizer) prob. thinks it's faster to hit the pages in the sorted order.

Let the optimizer do it';s job (unless of course it seems confused...then...)

MOO

I don't know, what does anyone else think

I do know in DB2, if the amount of data is less than a certain point the optimizer will scan regardless...

Brett

8-)

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-07-21 : 12:28:44

Just now experimented it. The same 1500 rows. But my optimizer chooses
different indexes... For the 1st query it chooses the clustered index and for
the 2nd - the k1, k2, k4 index.

If I force using the clustered index for the 2nd query:

select * from K (index(0)) order by k1, k2, k4

then execution time is 11/10 of the time of the query without the index hint.

- Vit

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-21 : 12:53:49

Yeah but are they the only columns you're using add 3 more columns add don't add those to the index...still 1500 is pretty small

Brett

8-)

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2003-07-21 : 14:01:13

Tim-
1500 rows is hardly anything. The optimizer is making the correct decision by scanning the clustered index.

Are you indexing just to optimize the Sort operator or are you anticipating the need to seek for a small # of rows?

Jonathan
{0}

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-21 : 14:15:26

Because there is so little data I thought it would do a scan...I know DB2 would..(or used to I get so confused...)

Notice the differences in the Access paths:


USE Northwind
GO

CREATE TABLE myTable99(col1 int IDENTITY (1,1), col2 int, col3 int, col4 int, col5 int)
GO
CREATE UNIQUE CLUSTERED INDEX x1 ON myTable99  (col1, col2, col3)
GO
CREATE INDEX x2 ON myTable99 (col1, col2, col4)
GO

INSERT INTO myTable99 (col2, col3, col4, col5)
SELECT 0,0,0,0 UNION ALL
SELECT 1,1,1,1 UNION ALL
SELECT 2,2,2,2 UNION ALL
SELECT 3,3,3,3 UNION ALL
SELECT 4,4,4,4 UNION ALL
SELECT 5,5,5,5 UNION ALL
SELECT 6,6,6,6 UNION ALL
SELECT 7,7,7,7 UNION ALL
SELECT 8,8,8,8 UNION ALL
SELECT 9,9,9,9
GO


SELECT col1,col2,col4 FROM myTable99 WHERE col1 = 3 AND col2 IN (1,2,3,4,5,6,7,8,9)  AND col4 IN (1,2,3,4,5,6,7,8,9)
GO

--Index Seek using ix2

SELECT col1,col2,col3 FROM myTable99 WHERE col1 = 3 AND col2 IN (1,2,3,4,5,6,7,8,9)  AND col3 IN (1,2,3,4,5,6,7,8,9)
GO

--Clustered Index Seek using ix1

SELECT * FROM myTable99 WHERE col1 = 3 AND col2 IN (1,2,3,4,5,6,7,8,9)  AND col4 IN (1,2,3,4,5,6,7,8,9)
GO

--Clustered Index seek using ix1

DROP TABLE myTable99
GO

Brett

8-)

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-07-21 : 14:23:41

To see the whole picture drag the vertical scrollbar of the Result Pane.

- Vit

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-07-21 : 14:29:29

I meant the Estimated Execution Plan tab...

- Vit

Tim
Starting Member

392 Posts

Posted - 2003-07-28 : 21:02:13

Sorry for slack reply guys, I was out of action. (in a hot tub with a bunch of swedish chicks for two weeks... then I woke up)

Actually I came upon this when trouble shooting a dodgy crystal report.

Crystal generated the SQL Statement and when I looked at the execution plan I couldn't figure why it would not choose the index that matched the order by.

I might shove in a million rows and see what happens.

- Stoad, see this one [url]http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=6831[/url] ... and this [url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=21054[/url] ... if only he had listened!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources