Is this really a better tweak for a single query in sp_BlitzCache?

-- EXEC sp_BlitzCache @SortOrder = 'duration';

declare @ErrorMessage nvarchar(256);

-- Original query. 48% cost overall.
if not (
		select [compatibility_level]
		from sys.databases
		where database_id = DB_ID()
		) >= 90
begin;
	set @ErrorMessage = N'The database ' + QUOTENAME(DB_NAME(DB_ID())) + N' has to be in compatibility level 90 or higher.';

	raiserror ('%s', 16, 1, @ErrorMessage) with nowait;
end;

-- Slight tweak. Less nodes in the execution plan. 41% cost overall.
if not (
		select top 1 [compatibility_level]
		from sys.databases
		where database_id = DB_ID()
		) >= 90
begin;
	set @ErrorMessage = N'The database ' + QUOTENAME(DB_NAME(DB_ID())) + N' has to be in compatibility level 90 or higher.';

	raiserror ('%s', 16, 1, @ErrorMessage) with nowait;
end;

-- Best tweak? Only 10% cost overall!
if not exists (
		select *
		from sys.databases
		where database_id = DB_ID()
			and [compatibility_level] >= 90
		)
begin;
	set @ErrorMessage = N'The database ' + QUOTENAME(DB_NAME(DB_ID())) + N' has to be in compatibility level 90 or higher.';

	raiserror ('%s', 16, 1, @ErrorMessage) with nowait;
end;

Author: Protiguous

C# Software Developer, Father, and seeker of Truth.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s