June 12, 2010 at 5:33 am
Sorry I previously posted my query in wrong category.
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.
June 12, 2010 at 7:05 am
Select * from table requires that SQL read the entire table. There's no index that can help with that operation.
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
June 12, 2010 at 8:16 am
GilaMonster (6/12/2010)
Select * from table requires that SQL read the entire table. There's no index that can help with that operation.
100% correct with the exception that you need select * order by...
In THAT case putting the CI in the same sequense as the order by would help since the sort operation would not be required.
But then again, on 84K rows I don't see that being a good solution for anybody GUI wise.
June 13, 2010 at 10:53 am
beejug1983
You need to consider how this table is to utilized ... what is a typical T_SQL command to be executed against the data in the table once you have determined that then you can proceed.
For example consider a table containing information on inventory, items on hand and/or on order. Who would run a select * from a table contains 84K + rows, and then scan it visually to find the specific item they are looking for. Or would a typical T_SQL statement be something along the order of SELECT * FROM Mytable WHERE ... column name = some value. The typical or most prevalently used T_SQL should be examined for what index(s) would be most efficient, and then those T_SQL statements tested.
As a starting point I would suggest you read these articles
By Gail Shaw - the first article in a three (3) part series
http://www.sqlservercentral.com/articles/Indexing/68439/
By Ramesh Gummadi
http://www.sqlservercentral.com/articles/Indexing/indexingstrategies/1252/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply