I have a table that has 99 columns, all of them are not needed but they were there when we started. There is a Primary Key (Objectuid) which is a GUI and has the Clustered Index on the PK.
The Table is updated often and has close to 2 million rows. there are no other non clustered indexes in this table.
I have a view that pulls necessary field from the table and does a left outer join with another table which has only 7 rows. It then adds 4 filters on the where clause. it retrieves about half a million record but takes about a minute.
Is it normal? How can I make it to run for less than five seconds? The Server has 64 GB RAM and 2 processors with 8 cores each. Its Hard Drive Space is about a TB.
Do you think it is improper Indexing? If so How can I figure out which Column to index to make it run faster? I need to keep in mind that there are inserts , updates and deletes on a regular basis.
The query is like this:
SELECT a,b,c,d,e,f,g,h,i,j
FROM TBLA LEFT OUTER JOIN TBLB
ON TBLA.Objectuid = TBLB.objectuid
WHERE TBLA.a NOT LIKE '11%'
AND TBLA.b <> '2100'
AND TBLA.c <> 'alloc'
AND TBLA.d NOT LIKE 'lm-%'
AND TBLA.e NOT LIKE 'ap-%'
AND TBLA.f >= '2014'
TBLA has 99 columns out of which the view only uses 15. TBLA has a PK which is clustered is a GUID. even when i remove the index it takes the same amount of time.
Best Answer
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?
Without even seeing any of that I would say you definitely need a non-clustered index. My guess is either one or both of the following is occurring in your query in regards to the questions above:
1) 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)
2) 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)
Even 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. Think of it like this: You have a word doc all your data printed out on paper. All your records are striped in one long string; at the end of the each record (99 fields), you have some delimiter. Then it starts over again with the next record; again all in one long string printed across all these physical piece of paper. 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. This is exactly how SQL Server works. If those 10 fields make up 10% of the actual data length, you are wasting 90% of the paper in his massive book you have printed out. If you create a non-clustered index on just these 10 fields you will save a lot of reading (because now these sheets of paper you have printed out will be completely crammed full of good data and the overall size will be 10% the original book. You will of course want to index on something that limits the most (based on the criteria of your query) and then just throw the other fields into an includes. Also, if those 7 rows you mention in your other query are static values, I would recommend to even go further and do a filtered index on those 7 values OR a filtered index on one of your where criteria. That will shrink your index down even further.