Index consideration using execution plan

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

SQL Server Administration (2005)

Index consideration using execution plan

Author

Topic

brij.dba
Starting Member

2 Posts

Posted - 2010-06-12 : 07:57:22

I have a table having 84000+ records, I am planning to consider index on this table. I am putting few details of execution plan below: -

When table has no Index: -

Select * from <table name>;

Table scan happens,
Estimated I/O cost = 0.517278
Estimated CPU cost = 0.0930076
Estimated operator cost = 0.610285(100%)
Estimated sub-tree cost = 0.610285

When table has clustered Index on id column: -
Clustered Index scan happens,
Estimated I/O cost = 0.517199
Estimated CPU cost = 0.0930861
Estimated operator cost = 0.610285(100%)
Estimated sub-tree cost = 0.610285

When table has Non-clustered Index on id column: -
Table scan happens,
Estimated I/O cost = 0.517199
Estimated CPU cost = 0.0930861
Estimated operator cost = 0.610285(100%)
Estimated sub-tree cost = 0.610285

Now my question is without indexing estimated CPU cost 0.0930076 is very less compare to others 0.0930861 and estimated I/O cost 0.517278 is higher than others 0.517199. Then which Index I need to create, how can I consider these values? I am bit confused about this, please help.

SQL DBA

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-06-12 : 09:12:53

Also asked here: http://www.sqlservercentral.com/Forums/Topic936426-360-1.aspx

--
Gail Shaw
SQL Server MVP

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-06-12 : 09:14:56

quote:
0.0930076 is very less compare to others 0.0930861

I wouldn't consider that "very less", it's barely a 1 percent difference.

The reason the index is not making any difference is because you are using SELECT *. Unless there's only 1 column in the table, the optimizer can't use that index for seek operations. You also don't have a WHERE clause in your query, so it will have to scan the entire table anyway.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-06-12 : 09:15:38

I swear I didn't steal your reply Gail.

Kristen
Test

22859 Posts

Posted - 2010-06-12 : 09:27:31

I'm glad I checked the link first. Gail said EXACTLY what I would have said. Honestly! ... and saved me having to write a reply ... well .. other than this one

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources