Query Performance comparison between instances

,

Compares performance of queries between different SQL Server instances.
1) 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.
2) Then execute output at destination and will do the same and show comparison of matching queries!!
The query only creates temp tables and perform selects on dm_exec_query_stats, you can execute it without issues.
It will show comparision of query plans that hash to the same value.

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

Rate

3.33 (3)

Share

Share

Rate

3.33 (3)