Query Performance comparison between instances

USE Master
GO
SET NOCOUNT ON
GO
/**
* Author: Rodrigo Acosta
* e-mail: acosta_rodrigo@hotmail.com
* Compares performance of queries between different SQL Server instances.
* Execute at source, and will generate an output with TSQL commands to create temp tables filled with top 50 long lasting queries from all databases.
* Then execute output at destination and will do the same and show comparison of matching queries!!
*/IF OBJECT_ID('tempdb..#temp') IS NOT NULL DROP TABLE #temp
GO
-- Temp table that will store source server stats.
CREATE TABLE #temp(
[dbname] sysname,
query_hash varbinary(MAX),
execution_count bigint,
avg_time_milliseconds bigint,
max_cpu_time_milliseconds bigint,
max_physical_reads bigint,
max_logical_writes bigint)

-- Gather stats from all databases
DECLARE @fillQuery nvarchar(MAX);
SELECT @fillQuery = N'SELECT TOP 50  "?" as dbname, 
HASHBYTES(' + '''' + 'SHA1' + '''' + ',CONVERT(varchar(8000), st.[text])) as text_checksum, 
qs.execution_count, 
((qs.total_elapsed_time / qs.execution_count) / 1000)  AS avg_time_milliseconds,
max_worker_time / 1000 as max_cpu_time_milliseconds,
max_physical_reads,
max_logical_writes
FROM sys .dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text( qs.sql_handle ) AS st 
ORDER BY qs.last_elapsed_time DESC;'

INSERT INTO #temp
exec sp_msforeachdb  @fillQuery;

-- remove system databases stats
DELETE FROM #temp WHERE dbname IN (SELECT name FROM sysdatabases WHERE dbid < 5)

-- variables declaration
DECLARE @dbname sysname;
DECLARE @query_hash varbinary(MAX);
DECLARE @cmd varchar(MAX), @insert varchar(MAX);
DECLARE @execution_count bigint,
@avg_time_milliseconds bigint, 
@max_cpu_time_milliseconds bigint,
@max_physical_reads bigint,
@max_logical_writes bigint

--define initial command
SELECT @cmd = 'CREATE TABLE #tempSource ([dbname] sysname, query_hash nvarchar(4000), execution_count bigint, avg_time_milliseconds bigint, max_cpu_time_milliseconds bigint, max_physical_reads bigint, max_logical_writes bigint) '

-- creates output table
IF OBJECT_ID('tempdb..#output') IS NOT NULL DROP TABLE #output
CREATE TABLE #output (id int IDENTITY(1,1), output nvarchar(MAX))

-- insert initial commands.
INSERT INTO #output (output) VALUES ('SET NOCOUNT ON');
INSERT INTO #output (output) VALUES ('GO');
INSERT INTO #output (output) VALUES (@cmd);

-- cursor that will iterate the temp table to form the final query
DECLARE statsCursor CURSOR LOCAL FORWARD_ONLY  
FOR SELECT dbname, query_hash, MAX(execution_count), MAX(avg_time_milliseconds), MAX(max_cpu_time_milliseconds), MAX(max_physical_reads), MAX(max_logical_writes) FROM #temp GROUP BY dbname, query_hash ORDER BY dbname, query_hash

OPEN statsCursor    
FETCH NEXT FROM statsCursor INTO @dbname, @query_hash, @execution_count, @avg_time_milliseconds, @max_cpu_time_milliseconds, @max_physical_reads, @max_logical_writes
WHILE (@@FETCH_STATUS = 0)
BEGIN  
SELECT @insert = 'INSERT INTO #tempSource (dbname, query_hash, execution_count, avg_time_milliseconds, max_cpu_time_milliseconds, max_physical_reads, max_logical_writes) VALUES (' + '''' + @dbname + '''' + ', ' + '''' +    CONVERT(nvarchar(4000), master.dbo.fn_varbintohexstr(@query_hash)) + '''' + ', ' + CONVERT(nvarchar(10), @execution_count) + ', ' + CONVERT(nvarchar(10), @avg_time_milliseconds)  + ', ' + CONVERT(nvarchar(10), @max_cpu_time_milliseconds)  + ', ' + CONVERT(nvarchar(10), @max_physical_reads)  + ', ' + CONVERT(nvarchar(10), @max_logical_writes) + ')'
INSERT INTO #output (output) VALUES (@insert);

FETCH NEXT FROM statsCursor INTO @dbname, @query_hash, @execution_count, @avg_time_milliseconds, @max_cpu_time_milliseconds, @max_physical_reads, @max_logical_writes  
END
CLOSE statsCursor;  
DEALLOCATE statsCursor; 

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Prepare statements to be executed at the destination server.
DECLARE @cmd2 nvarchar(MAX)
SELECT @cmd2 = N'CREATE TABLE #tempDestination(
[dbname] sysname,
[text] ntext,
query_hash varbinary(4000),
execution_count bigint,
avg_time_milliseconds bigint,
max_cpu_time_milliseconds bigint,
max_physical_reads bigint,
max_logical_writes bigint)'
INSERT INTO #output (output) VALUES (@cmd2)
SELECT @fillQuery = REPLACE(@fillQuery,'as dbname,', 'as dbname, [text], '); -- I'm adding the Text column to the table in destination

SELECT @cmd2 = 'DECLARE @cmd nvarchar(MAX); SELECT @cmd = ' + '''' + REPLACE(@fillQuery, '''', '''''') + '''' + ' INSERT INTO #tempDestination exec sp_msforeachdb @cmd' 
INSERT INTO #output (output) VALUES (@cmd2)

SELECT @cmd2 = N'
SELECT a.dbname db_source, b.dbname db_destination, b.[text], a.query_hash, a.execution_count exec_count_source, b.execution_count exec_count_destination, a.avg_time_milliseconds avg_time_millis_source, b.avg_time_milliseconds avg_time_millis_destination, 
a.max_cpu_time_milliseconds max_cpu_time_millis_source, b.max_cpu_time_milliseconds max_cpu_time_millis_destination, a.max_physical_reads max_physical_reads_source, b.max_physical_reads max_physical_reads_destination,  a.max_logical_writes max_log_writes_source, b.max_logical_writes max_log_writes_destination
FROM #tempSource a
INNER JOIN #tempDestination b
ON a.query_hash = CONVERT(nvarchar(4000), master.dbo.fn_varbintohexstr(b.query_hash))
ORDER BY b.dbname'
INSERT INTO #output (output) VALUES (@cmd2)

SELECT @cmd2 = N'-- SELECT * FROM #tempSource;' 
INSERT INTO #output (output) VALUES (@cmd2)
SELECT @cmd2 = N'-- SELECT * FROM #tempDestination;' 
INSERT INTO #output (output) VALUES (@cmd2)

SELECT output FROM #output ORDER BY id