adding nvarchar column to the index in sql server

shan 2014-03-28 17:32:40

Hi,

I am trying to tune a query where two nvarchar(255) columns are used in the order by clause of select query. Do i need to add these two columns to the index or do i need to add these to include list.These columns are also in the select list. can someone please suggest the best way to index these columns.

Aaron Bertrand 2014-03-28 17:36:06
There's not really a "this is always best" answer. Any potential index changes should be tested against not just this one query but, since indexes require more space and can have a dramatic impact on the write portion of your workload, should also be tested to see any detrimental effects against your entire workload over a full business cycle. I wouldn't consider it a win if you made this query 10% faster, while making your nightly ETL processes 50% slower. You also need to consider how often is this query being run compared to all other queries (both SELECT and writes) that will also use or touch this index.
shan 2014-03-28 17:43:02
Thanks Aaron.

I am mainly concerned about the select performance.I am ok if it takes long time to load data.After analyzing logical reads,i have added one column to index and one to the include list.However,its taking hours to update stats on this table. Do i need to change this index.

Aaron Bertrand 2014-03-28 18:08:50
I don't really have enough information about your table or the query to know which option is better, but mixing them (one in the key and one in include) certainly seems curious. Also, what options did you choose when updating stats? How big is the table (both how wide and how many rows)? How many indexes? What is the clustered index? What is the query?