New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

sp_BlitzFirst throws an error if it calls sp_BlitzCache with @SortOrder = 'memory grant' for SQL Server versions that do not support it #1910

Closed
RonMacNeil opened this Issue Jan 4, 2019 · 1 comment

Comments

Projects
None yet
2 participants
@RonMacNeil
Copy link
Contributor

RonMacNeil commented Jan 4, 2019

Version of the script
Bug in sp_BlitzFirst - (6.10)

What is the current behavior?
When triggering sp_BlitzFirst as part of a SQL Agent job, this fails at the same time every day with this error:
"Your version of SQL does not support sorting by memory grant or average memory grant. Please use another sort order."
While this error is valid based on the triggered query (i.e. sp_BlitzCache @SortOrder = 'memory grant'), as this server is version 10.0.6535.0, it prompted investigations into potential memory grant issues.

Ideally the triggered sp_BlitzCache would not be called with the @SortOrder = 'memory grant' for SQL Server versions that would cause it to fail. This would allow sp_BlitzFirst to fully complete its analysis.

To address this we can take the SQL Server version check from sp_BlictCache and implement it in sp_BlitzFirst.

Ahead of the line RAISERROR('Calling sp_BlitzCache',10,1) WITH NOWAIT; add this:

	DECLARE	@v DECIMAL(6,2),
		@build INT,
		@memGrantSortSupported BIT = 1;

	RAISERROR (N'Determining SQL Server version.',0,1) WITH NOWAIT;

	INSERT INTO #checkversion (version)
	SELECT CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(128))
	OPTION (RECOMPILE);


	SELECT @v = common_version ,
		   @build = build
	FROM   #checkversion
	OPTION (RECOMPILE);

	IF (@v < 11)
	OR (@v = 11 AND @build < 6020) 
	OR (@v = 12 AND @build < 5000) 
	OR (@v = 13 AND @build < 1601)
		SET @memGrantSortSupported = 0;

Change:
WHEN Finding = 'Poison Wait Detected: RESOURCE_SEMAPHORE' THEN 'memory grant' WHEN Finding = 'Poison Wait Detected: RESOURCE_SEMAPHORE_QUERY_COMPILE' THEN 'memory grant'

To this:
WHEN Finding = 'Poison Wait Detected: RESOURCE_SEMAPHORE' AND @memGrantSortSupported = 1 THEN 'memory grant' WHEN Finding = 'Poison Wait Detected: RESOURCE_SEMAPHORE_QUERY_COMPILE' AND @memGrantSortSupported = 1 THEN 'memory grant'

Change:
IF @BlitzCacheSortOrder IS NULL AND EXISTS (SELECT * FROM #BlitzFirstResults WHERE CheckID = 34)

To this:
IF @BlitzCacheSortOrder IS NULL AND EXISTS (SELECT * FROM #BlitzFirstResults WHERE CheckID = 34) AND @memGrantSortSupported = 1

What is the expected behavior?
sp_BlitzFirst should complete successfully on all supported SQL Server versions (2008 and up) even when CheckID 34 is encountered.

Which versions of SQL Server and which OS are affected by this issue? Did this work in previous versions of our procedures?
All versions of SQL Server where calling sp_BlitzCache with @SortOrder = 'memory grant' would fail are affected.
As I am not yet a GitHubian I'm reporting this with the hope someone could have a look at making this change.

RonMacNeil pushed a commit to RonMacNeil/SQL-Server-First-Responder-Kit that referenced this issue Jan 4, 2019

M-Ron M-Ron
Issue BrentOzarULTD#1910 Add SQL Server version check before choosing…
… 'memory grant' as the @BlitzCacheSortOrder.

RonMacNeil pushed a commit to RonMacNeil/SQL-Server-First-Responder-Kit that referenced this issue Jan 4, 2019

@BrentOzar

This comment has been minimized.

Copy link
Member

BrentOzar commented Jan 4, 2019

Great job! Congratulations on getting your first (two!) pull requests accepted. Thumbs up! Now go enjoy the beverage of your choice for the weekend. This will be in the Feb release of the First Responder Kit, and we'll credit you in the announcement.

@BrentOzar BrentOzar closed this Jan 4, 2019

BrentOzar added a commit that referenced this issue Jan 28, 2019

2019-01 Release (#1932)
* Updating readme.md for sp_DatabaseRestore

Documenting @ExistingDBAction for https://dba.stackexchange.com/questions/226145/sp-databaserestore-msg-50000.

* 1900 sp_BlitzIndex add histograms

When @TableName is specified and sys.dm_db_stats_histogram is available. Closes #1900.

* #1903 sp_Blitz SQLServerCheckup

Adding filter for that app name. Closes #1903.

* #1905 sp_BlitzIndex remove BOU link

Nothing against BOU, just don't need it in that particular place. Closes #1905.

* #1908 Update copyright dates

Ah, the glamour. Closes #1908.

* Issue #1904 Change RAISERROR 'severity' for that should trigger throw and error.

* Issue #1910 Add SQL Server version check before choosing 'memory grant' as the @BlitzCacheSortOrder.

* Issue #1910 Add temp table creation.

* #1914 sp_BlitzIndex partition error severity

Dropping severity level from 16 to 0 since we're logging it in the result set anyway. Closes #1914.

* Issue 1894 Moved RESTORE HEADERONLY up

* LF line endings

* added some extra checks + corrected some nesting

* rebase and line endings

* Web site commit

Does this fix line endings?

* #1916 sp_Blitz ignore backup on TempDB drive

Closes #1916.

* Auto line endings

Dealing with sp_DatabaseRestore's line endings.

* 1919 sp_Blitz ignoring a few checks for Managed Instances (#1920)

* 1919 sp_Blitz ignoring a few checks for Managed Instances

Working on #1919.

* #1919 sp_Blitz excluding restored databases

When checking backup history. Working on #1919.

* #1919 sp_Blitz ignore some checks on Mgd Instances

Changing RCSI wording on Azure SQL DB. Working on #1919.

* #1921 Sp_BlitzIndex Add Drop and Create Columns to Output (#1923)

Joined output query to #IndexCreateTsql to retrieve the CreateTsql already generated earlier in the script.  Created Drop TSQL based off evaluating what type of index was present.

* #1921 sp_BlitzIndex adding drops (#1924)

Moving drop and create TSQL to the end of mode 2's results. Closes #1921.

* #1925 sp_Blitz AWS RDS detection (#1926)

Don't just rely on EC2 VM name to detect RDS. Also adds a new result noting that checks were skipped. Closes #1925.

* #1927 sp_BlitzIndex skip rdsadmin db (#1928)

GetAllDatabases = 1 fails when it hits rdsadmin because they're referring to the resource db. Closes #1927.

* 2019_01 Release (#1931)

Prep work for the release - changing version numbers, building build scripts.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment