SQL Server: How do I identify the queries that are worth tuning?

Have you ever asked this question about how do I identify the queries that worth tuning? The simple answer it is neither easy or hard to obtain such information if you have managed your SQL Server by deploying industry best practices (wherever possible).

Then it comes what kind of practice you should deploy in order to obtain the information in identifying the queries that are worth tuning, it is worth referring most viewed blog on this site that is Identify-Troubleshoot-slow-running-queries blog post and few posts with DMV-Tags. In general I would always keep a list of queries that are executed frequently (where the performance is complained on that database). At first you may not have handful information and in this case I feel it is reliable method in getting such information by performing a set of traces of all queries against your database server, also group them by their signatures. To refer more on the signatures of query execution I suggest that all the DBAs to go through Itzik Ben-Gan's book Inside Microsoft SQL Server 2005: T-SQL Querying.as query signatures are difficult to generate that describes a solution using CLR user-defined functions and regular expressions in his book.

General practice to recommend is to look for the execution plan for a query that has complaints in performance, do not leave minimum values in a complext query such as a simple SELECT statement (joins) execution resulted within 2 minutes & number of times it has been executed within this complex join query. Recently Technet Magazine has revealted very useful query in getting information Identifying top 20 most expensive queries in terms of read I/O as referred on this blog.

Say once you have obtained the list by using above query & traces from your server you are good to go in identifying which are troublesome that needs a tuning exercise even though the performance hasn't been complained, better be safe than sorry. In concluding this topic I refer another DMV that is used commonly when looking at query execution plan:

select st.text, qp.query_plan, cp.cacheobjtype, cp.objtype, cp.plan_handle from sys.dm_exec_cached_plans cp cross apply sys.dm_exec_sql_text(cp.plan_handle) st cross apply sys.dm_exec_query_plan(cp.plan_handle) qp