How to determine Which Index will be helpful in SQL server Database table?

Hello Friends,
Today I have to do some performance tuning work for a store procedure on a database which has millions on records.
So I just tried basic first with like dbo uses, proper joins, avoiding of inline queries. But could not get a huge success.
Then I tried for index. But the thing which is in mind that which index should be appropriate for my table which has millions of records.
I am in dilemma which index should be appropriate I just wondering the utility which give some sort of guidance because as we know Indexing is very critical thing in both the way if we increase the index insert & delete, update will be slow.
And size of database is also increase so it should be very effective.
So in searching I find a useful thing which Dynamic View Management (DVM) Which is helpful in finding proper index.
I find “sys.dm_db_missing_index_details
Which provide you information of missing index. It give basically return following columns when you run it.
Index_handle, database_Id, object_Id, Equality Columns, Inequality Columns, included columns and statements.

Now we have to mainly concentrate on Equality columns, Inequality Columns, and included columns these 3 columns has their own indication
1)Equality columns:-
It mainly have the actual database table column name(s) on which we can create new index. Most of the time this column is where clause , join etc. as shown in fig
2)Inequality Columns:-
This are the columns which we have to avoid for index most of the time this column is null but if have value then we have to check it out and exclude from index.(As shown in fig)
3)Included columns:- This column can be included in creating new index if required (as shown in fig)

So from above fig if I need to figure out some conclusion than I can say I will create a index on tblSample table on the basis of equality columns which is productBatchId and more over I can include other columns like serializationId, client id also if required.

So in this way DVM command help in guiding Index.

I hope you will also take advantage of this.

Enjoy SQL
Enjoy Indexing.

Thanks
Rajat

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s