Find inefficient query plans

Introduction

This T-SQL script will check the plan cache for the top 25 inefficient query plans. It will list total CPU time, CPU time per execution, total read IOs, average read IOs per execution, total recompiles and average recompiles per execution.

Scenarios

The script will check the plan cache for the top 25 inefficient query plans. 

Script

You can use this script in this way:
1. Open the SQL Server Management Studio (SSMS) and connect to SQL Server.

2. Drag the script to the SSMS and select the database you want to use then run the script.

Here are some code snippets for your references:

SQL
Edit|Remove
IF @sqlmajorver = 9 
BEGIN 
    SET @sqlcmd = N'SELECT @StartDateOUT = login_time, @UpTimeOUT = DATEDIFF(mi, login_time, GETDATE()) FROM master..sysprocesses WHERE spid = 1'; 
END 
ELSE 
BEGIN 
    SET @sqlcmd = N'SELECT @StartDateOUT = sqlserver_start_time, @UpTimeOUT = DATEDIFF(mi,sqlserver_start_time,GETDATE()) FROM sys.dm_os_sys_info'; 
END 
  
SET @params = N'@StartDateOUT DATETIME OUTPUT, @UpTimeOUT VARCHAR(12) OUTPUT'; 
  
EXECUTE sp_executesql @sqlcmd, @params, @StartDateOUT=@StartDate OUTPUT, @UpTimeOUT=@UpTime OUTPUT; 
  
SELECT 'Uptime_Information' AS [Information], GETDATE() AS [Current_Time], @StartDate AS Last_Startup, CONVERT(VARCHAR(4),@UpTime/60/24) + 'd ' + CONVERT(VARCHAR(4),@UpTime/60%24) + 'h ' + CONVERT(VARCHAR(4),@UpTime%60) + 'm' AS Uptime

Prerequisites

SQL Server 2005 or higher version