

Now if you are wanting to read just 10 of those fields, you still have to look at that whole piece of paper and pull out just those 10 fields. Then it starts over again with the next record again all in one long string printed across all these physical piece of paper. All your records are striped in one long string at the end of the each record (99 fields), you have some delimiter.

Think of it like this: You have a word doc all your data printed out on paper. My guess is either one or both of the following is occurring in your query in regards to the questions above:ġ) The actual rows are way off from the estimated rows (because the sql optimizer has no good estimates on what is actually in your tables)Ģ) The IO from your query listed above is at least a magnitude higher than if you just inserted those same 10 fields into another table and ran the same query (if those 10 fields are no larger than your average across all those 99 fields)Įven if you don't use those other 89 fields (since your query looks to only select 10 fields), SQL can only read what is on the page - all 99 fields.

Without even seeing any of that I would say you definitely need a non-clustered index. Have you checked your actual rows vs estimated rows in the actual execution plan? Also, have you checked the reads from your query compared to the reads from a table made up of just the fields you are selecting on?
