SQL server tools to identify query optimisations and missing indexes I have a broad question regarding SQL Server that was given to me on a quiz and I am not sure what the answer is as I do not have access to SQL Server at home. My question is how can I utilise SQL Server tools to identify query optimisations, missing indexes etc?

Can somebody give me a very brief answer to this?

Thank you
how can I utilise SQL Server tools to identify query optimisations, missing indexes etc?


No tool is perfect and you should not rely implementing the suggestions without proper testing your entire business life cycle.

Native tools like DTA are OK, but I have seen many problems with using it. I would not rely on it.

That being said, SQL Server exposes a wealth of information using DMVs and Extended Events.

I would suggest you start with : SQL Server Diagnostic Information Queries - Glenn Berry - choose as per your version of SQL Server.

Also, highly recommend this FREE book from RedGate - Performance Tuning with SQL Server Dynamic Management Views 

You have to baseline your servers and then you can compare what is good and bad for your environment.

Remember performance tuning is an art, not science. It will take you some time to digest the resources I have cited and this is how I have learned.

You should be cautious when creating Missing indexes exposed by DMVs

Other blogs that contain a wealth of information are (in no particular order):


SQLSkills.com 
BrentOzar.com
SQLPerformance.com
Querrprocessor.com
CSS SQL Server Engineers
SQLServerCentral - Stairway series