May 10, 2012 at 4:55 am
Hi,
I had been working on some code improvements for performance gains every now and then. I was going through a query and its execution plan to see where could be some issues. It runs great in OLTP environments because it has some indexes but not in OLAP where indexes are not there on big tables.
Now, I understand that index scan is causing the query to go slow and consume resources.Instead of blindly copying the index, I looked upto DTA to advise me something. It came up with a suggestion and I was not surpised. It suugests the same index as OLTP one.
Now,my question is that in my query I have one column in where clause from that big table and 2 columns in inner join clause and the DTA suggests that all 4 columns should be a key columns in an index which is required. I am surprised because at some places, only columns in where clause are advised to go as key columns.
So how do we decide? Should we go ahead and see the selectivity of these columns where lower is the density and higher is selectivity. Can I use some commands to find this(dbcc showstatistics??).
I want to learn how to excel without database tuning advisor.
Kindly bear with my long question and suggest some help.
Regards
Chandan
May 10, 2012 at 9:14 am
It would help to see the table definition and query you're working with but are the 4 columns in question the only 4 being used from that table? I ask because it sounds like the DTA is recommending a nonclustered covering index.
_____________________________________________________________________
- Nate
May 10, 2012 at 10:43 am
RP_DBA (5/10/2012)
It would help to see the table definition and query you're working with but are the 4 columns in question the only 4 being used from that table? I ask because it sounds like the DTA is recommending a nonclustered covering index.
Agreed on both points!
chandan_jha18 (5/10/2012)
Now,my question is that in my query I have one column in where clause from that big table and 2 columns in inner join clause and the DTA suggests that all 4 columns should be a key columns in an index which is required. I am surprised because at some places, only columns in where clause are advised to go as key columns.
It can depend on whether the fields in the join are used in equality or inequality comparisons - it may be for equality: key column, inequality: included column.
Not hard and fast rules though.
I also only count 3 columns listed by you there!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply