Skip to content
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_BlitzCache - Allows invalid @SortOrder values #1945

RichBenner opened this Issue Feb 5, 2019 · 3 comments


None yet
2 participants
Copy link

RichBenner commented Feb 5, 2019

Version of the script
Current (7.2)

What is the current behavior?
if you pass @SortOrder = 'memory grants' for example the script runs fine but 'memory grants' (plural) isn't valid.

What is the expected behavior?
It would be nice to do 2 things here.

  1. make the script accept all plural and singular versions of the sort orders.
  2. pop up an informational message that the sort order was invalid so it defaulted to 'cpu' (if this is indeed the behaviour, i'll check)

This comment has been minimized.

Copy link
Contributor Author

RichBenner commented Feb 13, 2019

I'm thinking something like this, thoughts?

DECLARE @SortOrder varchar(20) = 'avg'

SET @SortOrder = LOWER(@SortOrder);
SET @SortOrder = REPLACE(REPLACE(@SortOrder, 'average', 'avg'), '.', '');

SET @SortOrder = CASE 
                     WHEN @SortOrder IN ('executions per minute','execution per minute','executions / minute','execution / minute','xpm') THEN 'avg executions'
                     WHEN @SortOrder IN ('recent compilations','recent compilation','compile') THEN 'compiles'
                     WHEN @SortOrder IN ('read') THEN 'reads'
                     WHEN @SortOrder IN ('avg read')	THEN 'avg reads'
                     WHEN @SortOrder IN ('write') THEN 'writes'
                     WHEN @SortOrder IN ('avg write') THEN 'avg writes'
                     WHEN @SortOrder IN ('memory grants') THEN 'memory grant'
                     WHEN @SortOrder IN ('avg memory grants') THEN 'avg memory grant'
                     WHEN @SortOrder IN ('spill') THEN 'spills'
                     WHEN @SortOrder IN ('avg spill') THEN 'avg spills'
                     WHEN @SortOrder IN ('execution') THEN 'executions'
                 ELSE @SortOrder END

SELECT @SortOrder

RAISERROR(N'Checking sort order', 0, 1) WITH NOWAIT;
IF @SortOrder NOT IN ('cpu', 'avg cpu', 'reads', 'avg reads', 'writes', 'avg writes',
                       'duration', 'avg duration', 'executions', 'avg executions',
                       'compiles', 'memory grant', 'avg memory grant',
					   'spills', 'avg spills', 'all', 'all avg', 'sp_BlitzIndex')
  RAISERROR(N'Invalid sort order chosen, reverting to cpu', 16, 1) WITH NOWAIT;
  SET @SortOrder = 'cpu';

This comment has been minimized.

Copy link

BrentOzar commented Feb 13, 2019

Yeah, that looks great!


This comment has been minimized.

Copy link

BrentOzar commented Feb 15, 2019

Nice job, sir! Thanks. It's in, and it'll be in the Feb release.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.