3

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

7

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):

1
  • +1 For Brent Ozar. I would highly recommend getting his free stored procs like sp_BlitzCache and sp_BlitzIndex (for example) as they help you wade through the DMVs and DMFs to find what you are looking for. Jul 24 '15 at 12:42
4

I have a broad question regarding SQL Server...

Depending on which server version you have access to, the tools vary. Microsoft has an entire documentation section on optimization tools for each of the different server versions. Despite the differences, the fundamentals are the same across most versions.

Missing indexes, for example, are addressed in many sections of that link -- not all of which have automatic tools. Some index coverages techniques are as much as art as it science. For most basic optimization, I would start with the Execution Plan and the Database Tuning Advisor. Knowing how to decipher the execution plan can give deep insights into missing indexes.

Hope this helps.

Not the answer you're looking for? Browse other questions tagged or ask your own question.