question

First, gather metrics about the system to understand how it's behaving. One of the key metrics there is wait statistics. Evaluate the statistics gathered to ensure you have your server set up correctly. Adjust server settings as necessary. Then, start gathering query metrics. The best way to do this is with Extended Events, but older systems will have to use Trace Events. Identify the top 10 longest running queries and the top 10 most frequently called queries. Look at those queries to understand if they have any common code smells. Look at the query execution plans for those queries to see how the optimizer is working with the query and your data structures. Adjust your query and/or data structure to optimize the behavior of the query. Repeat this process over and over. In addition, ensure that you have good statistics maintenance routines running. You should also explore index maintenance. And that's it in a nutshell. If you want all the details associated with all the steps, I suggest getting a copy of my book, [SQL Server Query Performance Tuning.][1] [1]: http://www.amazon.com/SQL-Server-Query-Performance-Tuning/dp/1430267437/ref=sr_1_1?ie=UTF8&qid=1430651799&sr=8-1&keywords=fritchey