create necessary dB index from any SQL server view

indexes on tables/view are something that depend on usage (basically, the SQL and their WHERE clauses)
so, just looking at the tables (or views) will never tell you what indexes the application(s) will need.
you will need to either know the sql of those applications, or check the slow-running sql and tune them by adding indexes (if possible).
sometimes the sql is so bad that first the SQL needs tuning, up to eventually some application or table redesign

"(basically, the SQL and their WHERE clauses)"

I totally agree with it! so the column in where the clause should be index so that optimizer search the column value will be much faster, right?

"you will need to either know the sql of those applications, or check the slow-running sql and tune them by adding indexes (if possible). "

this need time to read the execution plan, which at this stage I prefer to see other easier method

"sometimes the sql is so bad that first the SQL needs tuning, up to eventually some application or table redesign"

too much inner join for example ?

"rather badly formulated sqls, or application not sending any where clause at all, and doing the filtering locally"

yeah, no where clause is a nightmare, like Select * from xxx;  this is totally not acceptable.

any example of filtering locally ?

"* load that trace into the sql tuning advisor"

is sql tuning advisor always give suggestion on sth ALREADY been done? that's why DBA from time to time need to read the execution plan, right? tuning advisor seems useless,.

any step by step guide on how to create  that trace?

>any example of filtering locally ?
my very first application (20 years ago) using database was doing that, as microsoft ADO suggested to do so and then to use ado recordset filtering ... was nice up to 100 records, then got really bad

tuning advisor tutorial start:
https://msdn.microsoft.com/en-us/library/ms166575.aspx

create a trace startup:
https://msdn.microsoft.com/en-us/library/ms175047.aspx

Guy Hengel [angelIII / a3],

">any example of filtering locally ?
my very first application (20 years ago) using database was doing that, as microsoft ADO suggested to do so and then to use ado recordset filtering ... was nice up to 100 records, then got really bad"

any real life example ?

ScottPletcher,

"not on using DTA, which is unreliable."

you mean DTA do not give good answer?

ScottPletcher,

"missing index stats, from views sys.dm_db_missing_index*

I read this :

https://msdn.microsoft.com/en-us/library/ms345421.aspx

and this part:

SELECT migs.group_handle, mid.*
FROM sys.dm_db_missing_index_group_stats AS migs
INNER JOIN sys.dm_db_missing_index_groups AS mig
    ON (migs.group_handle = mig.index_group_handle)
INNER JOIN sys.dm_db_missing_index_details AS mid
    ON (mig.index_handle = mid.index_handle)
WHERE migs.group_handle = 24;

Select allOpen in new window

return nothing, anything wrong? I have to use "use database; " ?

this one return 10 for me:

SELECT TOP 10 *
FROM sys.dm_db_missing_index_group_stats
ORDER BY avg_total_user_cost * avg_user_impact * (user_seeks + user_scans)DESC;

Select allOpen in new window

did any of you use this before and how accurate it is :

SELECT 
statement AS [database.scheme.table],
column_id , column_name, column_usage, 
migs.user_seeks, migs.user_scans, 
migs.last_user_seek, migs.avg_total_user_cost,
migs.avg_user_impact
FROM sys.dm_db_missing_index_details AS mid
CROSS APPLY sys.dm_db_missing_index_columns (mid.index_handle)
INNER JOIN sys.dm_db_missing_index_groups AS mig 
ON mig.index_handle = mid.index_handle
INNER JOIN sys.dm_db_missing_index_group_stats  AS migs 
ON mig.index_group_handle=migs.group_handle
ORDER BY mig.index_group_handle, mig.index_handle, column_id
GO

Select allOpen in new window

also I expected that the following view;


sys.dm_db_missing_index_details
sys.dm_db_missing_index_group_stats
sys.dm_db_missing_index_groups
sys.dm_db_missing_index_columns

is going to record the index which the SQL optimizer is expecting and NOT ALREADY implemented, right?


http://www.sql-server-performance.com/2009/identify-missing-indexes-using-sql-server-dmvs/

can those DMV combine with other DMV to also associate the spid information to the output so that I can know which query let the DMV give the missing index result ?

also can the above DMV also gives what TYPE of index it is missing, unique index, cover index , etc?

Vikas Garg,

"but it has to be built based on the searching conditions and joining conditions"

so basically to make it simple, we can check if the column in the where clause has been indexed or not ? if not , just add that column has to be index so that where search can be faster ? and the execution plan will be simpler, agree ?

yes,

columns which are used in joining conditions or where clause if indexed will give fast data retrieval.

Indexing

so in this way, it is much easier to consider index creation candidates, agree ?

No.  There is no super-easy way to properly design and implement indexes.  No one can ever give you a quick guide or 10-15 minutes of instruction that will make you an "expert" on maintaining indexes.  It's a complex process which takes time to become competent at, period!

"No.  There is no super-easy way to properly design and implement indexes."

I know, but we can know it much quicker

" No one can ever give you a quick guide or 10-15 minutes of instruction that will make you an "expert" on maintaining indexes.  It's a complex process which takes time to become competent at, period!"

yeah, for me, this just give me a spotlight on what should I do when I want to do it quickly, e.g,,

then dig into the execution plan.

so no DMV will will quickly tell you what TYPE of index we need to create  but execution plan ?

may I know anyway to link query spid to this query to LINK information on what query looking for the index:

SELECT 
statement AS [database.scheme.table],
column_id , column_name, column_usage, 
migs.user_seeks, migs.user_scans, 
migs.last_user_seek, migs.avg_total_user_cost,
migs.avg_user_impact
FROM sys.dm_db_missing_index_details AS mid
CROSS APPLY sys.dm_db_missing_index_columns (mid.index_handle)
INNER JOIN sys.dm_db_missing_index_groups AS mig 
ON mig.index_handle = mid.index_handle
INNER JOIN sys.dm_db_missing_index_group_stats  AS migs 
ON mig.index_group_handle=migs.group_handle
ORDER BY mig.index_group_handle, mig.index_handle, column_id
GO

Select allOpen in new window

any help on this:

"so no DMV will will quickly tell you what TYPE of index we need to create  but execution plan ?

may I know anyway to link query spid to this query to LINK information on what query looking for the index:"

Check out : http://www.brentozar.com/blitzindex/ a free download and can help with some of your questions - in fact, check out the free tools on http://allen-mcguire.blogspot.com.au/2013/08/my-dba-toolbox.html

Having said that, I am bailing out of this thread :)

There seems to be too much discussion after the answer is chosen.

tks.

good night Mark wills, you have helped a lot and I will check the link one by one.

have a  good day. sorry if I make you bored :)

*laughing*

you should ask what you need to know before accepting answers.

what I need to do, is to provide answers before the question has been answered.

never bored and no apologies needed. sorry if I offended, didn't mean to...

"you should ask what you need to know before accepting answers."

NONO. I already get most out of it but I can't get perfect answer for it as no answer is prefect ! As what ScottPletcher said there are no easy/super easy way to do it and I am ok with it but just want to get most out of it.

I just need some good link/pdf/ebook to tell me the best/correct concept I need to know in order to find out, from time to time, the best missing index. I may focus on TOO MUCH developer's question on HOW TO WRITE the best query and the DIRECTION on write the right query instead of finding the right index, this can be very time consuming.

I am not going to make myself perfect here anyway, someone will blame me. I knew it ! you knew it too !

"never bored and no apologies needed. sorry if I offended, didn't mean to... "

NONO, just want to make everyone who answer my question happy ! so that they will come back and help. :):):):)

"http://www.brentozar.com/blitzindex/"

basically as I am changing job now and I don't have an environment to test it right now ! but what I am worrying about is:

as you can see, there are many diff type of index, like XML index, Spatial indexes, and full-text serach index, unique index, etc. so I look at other books, like this: http://www.amazon.com/gp/product/1430237414?psc=1&redirect=true&ref_=ox_sc_sfl_title_4&smid=ATVPDKIKX0DER

so the tools from this link:http://www.brentozar.com/blitzindex/:,  will help to find the CORRECT index type for me ? and it seems that it will also help to generate that index creation script for me which take care the CORRECT TYPE of index, right? I don't think it will be prefect as it will not!   you use this tools all the time ?

I will use the following script to find missing index so that I can verify the script/SP from that link: http://www.brentozar.com/blitzindex/

SELECT 
statement AS [database.scheme.table],
column_id , column_name, column_usage, 
migs.user_seeks, migs.user_scans, 
migs.last_user_seek, migs.avg_total_user_cost,
migs.avg_user_impact
FROM sys.dm_db_missing_index_details AS mid
CROSS APPLY sys.dm_db_missing_index_columns (mid.index_handle)
INNER JOIN sys.dm_db_missing_index_groups AS mig 
ON mig.index_handle = mid.index_handle
INNER JOIN sys.dm_db_missing_index_group_stats  AS migs 
ON mig.index_group_handle=migs.group_handle
ORDER BY mig.index_group_handle, mig.index_handle, column_id
GO

Select allOpen in new window

you can see similar thing discussion on this too : https://www.experts-exchange.com/questions/28711297/find-missing-index-or-wanted-MS-SQL-index-from-reading-execution-plan.html, which looking for index resource to reads.