create necessary dB index from any SQL server view

Dear all,

any script you all use to find out/script out the necessary index from any DMV of SQL server 2008 and above and we can expect to use the output of this script create reasonable index easily without reading too much execution plan and still generate the wrong index ?
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
0
Vikas GargBusiness Intelligence DeveloperCommented:
Hi,

Indexing is to fasten the retrieval so it can not be found from the structure but it has to be built based on the searching conditions and joining conditions
0
marrowyungSenior Technical architecture (Data)Author Commented:
"(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 ?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
>this need time to read the execution plan, which at this stage I prefer to see other easier method

the only "easier" thing is, from what I know, this:
* run a full trace on your instance during a day (or a week)
* load that trace into the sql tuning advisor
* take the index suggestions from that tool

but "easier" is not "more efficient" ... there is the human part which can outbeat the automated tool easily


>too much inner join for example ?
rather badly formulated sqls, or application not sending any where clause at all, and doing the filtering locally
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
marrowyungSenior Technical architecture (Data)Author Commented:
"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?
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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
0
Scott PletcherSenior DBACommented:
SQL can give you info to help with this, without you having to run Profiler/trace ... assuming you know how to use it.

At a minimum, you'll need:
missing index stats, from views sys.dm_db_missing_index*
index usage stats, from view sys.dm_db_index_usage_stats

Is there an "easy" way to determine proper indexes?  No, there simply isn't.  But if you're looking to do index tuning, look for instructions on using those views, not on using DTA, which is unreliable.
0
marrowyungSenior Technical architecture (Data)Author Commented:
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?
0
marrowyungSenior Technical architecture (Data)Author Commented:
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;

Open 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;

Open in new window

0
marrowyungSenior Technical architecture (Data)Author Commented:
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

Open in new window

0
marrowyungSenior Technical architecture (Data)Author Commented:
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/
0
marrowyungSenior Technical architecture (Data)Author Commented:
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 ?
0
marrowyungSenior Technical architecture (Data)Author Commented:
also can the above DMV also gives what TYPE of index it is missing, unique index, cover index , etc?
0
marrowyungSenior Technical architecture (Data)Author Commented:
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 ?
0
Vikas GargBusiness Intelligence DeveloperCommented:
yes,

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

Indexing
0
marrowyungSenior Technical architecture (Data)Author Commented:
so in this way, it is much easier to consider index creation candidates, agree ?
0
Scott PletcherSenior DBACommented:
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!
0
marrowyungSenior Technical architecture (Data)Author Commented:
"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

Open in new window

0
marrowyungSenior Technical architecture (Data)Author Commented:
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:"
0
Mark WillsTopic AdvisorCommented:
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.
0
marrowyungSenior Technical architecture (Data)Author Commented:
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 :)
0
Mark WillsTopic AdvisorCommented:
*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...
0
marrowyungSenior Technical architecture (Data)Author Commented:
"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

Open in new window


you can see similar thing discussion on this too : http://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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.