0

I have SQL Server 2014 CU11 with sp_Blitz 5/17/18 8:44AM release. sp_Blitz is reporting that I have user created statistics on some of my databases. I've executed a script that deletes all user created statistics. However, when I re-run sp_Blitz it still reports them. If I use the T-SQL on Brent's page: SELECT * FROM sys.stats WHERE user_created = 1 then I see all the user created indexes. However if I use alternate code like:

SELECT st.name AS TableName, ss.name AS StatisticName
FROM sys.stats ss
JOIN sys.tables st ON ss.object_id=st.object_id
WHERE ss.user_created = 1
ORDER BY st.name, ss.name;

I see no user created indexes. I've tried several variations of the "find user stats" script & the only one that reports any is Brent's. Why would this be? I've removed almost all the user created statistics, and would like to take care of these leftovers if possible.

1 Answer 1

2

You can have rows in sys.stats without a corresponding row in sys.tables.

Classic example: an indexed view. Watch this:

CREATE TABLE dbo.T1 (ID INT PRIMARY KEY CLUSTERED);
INSERT INTO dbo.T1 VALUES (1);
GO
CREATE OR ALTER VIEW dbo.vwJetta WITH SCHEMABINDING AS
  SELECT T1.ID
  FROM dbo.T1;
CREATE UNIQUE CLUSTERED INDEX CLIX ON dbo.vwJetta(ID);
GO
CREATE STATISTICS STAT ON dbo.vwJetta(ID);
GO
SELECT * FROM sys.stats WHERE user_created = 1;

Our query correctly shows a row:

Un stat

Whereas your query does not, because there's no table involved:

Sad trombone

Moral of the story: never, ever doubt anything you see on BrentOzar.com. Just kidding. But believe what you're seeing right here, and get to work digging into those stats.

2
  • Thank you! I knew Brent wasn't leading me astray. I just couldn't figure out why they weren't showing up. So I now just need to modify the removal script I have to look for the "user_created". I'll run some tests first & get them purged from the system. The previous "DBA" was clearly trying to be too smart for his own good. Trying to get this system back in shape. May 17, 2018 at 19:14
  • Best of luck on it! That last guy is always the worst. ;-)
    – Brent Ozar
    May 17, 2018 at 19:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.