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
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
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.