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_BlitzIndex: split heap forwarded-fetch and deleted-records into 2 warnings #1685

Closed
BrentOzar opened this Issue Jul 17, 2018 · 1 comment

Comments

Projects
None yet
1 participant
@BrentOzar
Copy link
Member

BrentOzar commented Jul 17, 2018

Do you want to request a feature or report a bug?
Feature

What is the current behavior?
Right now, if a heap has either forwarded fetches or deletes, we're throwing the same alert. We don't usually take immediate action on heaps with deletes - that doesn't necessarily indicate a problem, because they might have had inserts to go along with it, and there may not be a page density problem.

What is the expected behavior?
Split those two things into two separate warnings. The deleted-rows one should only show up under expert mode (4), but forwarded fetches should show up in all.

@BrentOzar BrentOzar added this to the Classic Sock Weekend milestone Jul 17, 2018

@BrentOzar BrentOzar self-assigned this Jul 17, 2018

BrentOzar added a commit that referenced this issue Jul 17, 2018

#1685 sp_BlitzIndex split heap warnings
Now 2 warnings for forwarded fetches and for deletes. Closes #1685.
@BrentOzar

This comment has been minimized.

Copy link
Member Author

BrentOzar commented Jul 17, 2018

Code to test the new changes:

CREATE TABLE dbo.HeapsOfSadness (Id INT IDENTITY(1,1), Stuffing VARCHAR(5000));
INSERT INTO dbo.HeapsOfSadness (Stuffing)
  SELECT 'Stuff' /* Short */
  FROM sys.messages;
GO
/* No heap warnings should get returned because the heap is fine: */
sp_BlitzIndex
GO


/* Update Stuffing to be way longer */
UPDATE dbo.HeapsOfSadness
  SET Stuffing = REPLICATE('o', 5000);
GO

/* sp_BlitzIndex should report forwarded fetches: */
sp_BlitzIndex
GO


/* Delete the rows */
DELETE dbo.HeapsOfSadness WHERE Id IN (SELECT TOP 1000 Id FROM dbo.HeapsOfSadness);
GO 300


/* sp_BlitzIndex should still report forwarded fetches, and not the deletes
   (and yes, the forwarded fetches numbers go bonkers) */
sp_BlitzIndex
GO



TRUNCATE TABLE dbo.HeapsOfSadness
GO

/* Now the forwarded fetch warnings are gone because we're below the size threshold: */
sp_BlitzIndex
GO


/* Repopulate the table, this time with big data from the start: */
INSERT INTO dbo.HeapsOfSadness (Stuffing)
  SELECT REPLICATE('o', 5000)
  FROM sys.messages;
GO


/* We're back to the forwarded fetch warning, which isn't technically true
   anymore since the table was truncated, but we can't tell that: */
sp_BlitzIndex
GO


/* Now redo it and do deletes instead this round, no forwarded fetches: */
DROP TABLE dbo.HeapsOfSadness;
GO
CREATE TABLE dbo.HeapsOfSadness (Id INT IDENTITY(1,1), Stuffing VARCHAR(5000));
INSERT INTO dbo.HeapsOfSadness (Stuffing)
  SELECT REPLICATE('o', 5000)
  FROM sys.messages;
GO
DELETE dbo.HeapsOfSadness WHERE Id IN (SELECT TOP 1000 Id FROM dbo.HeapsOfSadness);
GO 300


/* By default, we don't get the heaps deletion warning: */
sp_BlitzIndex
GO


/* Ask for expert mode and we get the priority 200 warning about heaps with deletes: */
sp_BlitzIndex @Mode = 4
GO

DROP TABLE dbo.HeapsOfSadness;

BlitzErik added a commit that referenced this issue Jul 17, 2018

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.