SQL Scripts - Best Practices from Microsoft - Performance Analyse

--Collection of Useful T-SQL Scripts for Database Administrators
 
--Most Executed
SELECT TOP 5 execution_count, Plan_handle, query_plan, TEXT
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle)
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle)
ORDER BY execution_count DESC;
 
--Top Avg CPU
SELECT TOP 5 total_worker_time/execution_count AS [Avg CPU Time], execution_count, Plan_handle, query_plan, TEXT
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle)
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle)
ORDER BY total_worker_time/execution_count DESC;
 
--Longest Running
SELECT TOP 25 total_elapsed_time, total_elapsed_time/execution_count AS [Avg Duration], execution_count, query_plan, TEXT, *
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle)
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle)
ORDER BY qs.total_elapsed_time DESC;
--ORDER BY total_elapsed_time/execution_count  DESC;

--Highest Physical I/O
SELECT TOP 5 total_physical_reads, total_physical_reads/execution_count AS [Avg Phys Read], execution_count, query_plan, TEXT
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle)
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle)
ORDER BY total_physical_reads DESC;
--ORDER BY total_physical_reads/execution_count  DESC;

--Top Total I/O
SELECT TOP 30 total_logical_reads + total_logical_writes as TotalIO, (total_logical_reads + total_logical_writes)/execution_count AS [Avg Log IO], total_logical_reads, total_logical_writes, execution_count, query_plan, TEXT
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle)
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle)
--ORDER BY total_logical_reads + total_logical_writes DESC;
ORDER BY (total_logical_reads + total_logical_writes)/execution_count  DESC;
TSQL Coding
        Most Equally to least Equally in that order.
        Ansi style joins
        Be careful with IN/Not IN with a sub query.  Performance declines with large lists of data in an IN statement.
        Try to avoid:
                       <>
                       NOT LIKE
                       LIKE '%xxx'
                       NOT IN
                       Cursors of all types
 
               Avoid
                              IF Exist(Select * .....) - The * in this statement will cause a table scan.  You are bringing data back to the Buffer Pool and not using it.
                              This should be IF Exist(Select 1...) or IF Exist(Select top 1 1...)   --Same result to the developer less abuse on the server.