Do you have a comment or suggestion to improve SQL Server? We’d love to hear it!

sys.dm_db_stats_histogram and sys.dm_db_stats_properties only work in the current database

If you prefix them by database name, they don't throw an error, and return incorrect results. Repro script:

/* Get an object_id and stats_id from msdb */
USE msdb;
GO
DECLARE @object_id INT, @stats_id INT;
SELECT TOP 1 @object_id = object_id, @stats_id = stats_id
FROM msdb.sys.stats
WHERE object_id = OBJECT_ID('dbo.backupfile');

/* Check its histogram - works fine when your context is in the same database */
SELECT * FROM msdb.sys.dm_db_stats_histogram(@object_id, @stats_id);

/* Now try the same thing in the master database context */
USE master;
GO
DECLARE @object_id INT, @stats_id INT;
SELECT TOP 1 @object_id = object_id, @stats_id = stats_id
FROM msdb.sys.stats
WHERE object_id = OBJECT_ID('dbo.backupfile');

/* Check its histogram.
Note that the DMF below is prefixed with MSDB, but it doesn't take effect.
No rows are returned.*/
SELECT * FROM msdb.sys.dm_db_stats_histogram(@object_id, @stats_id);

1 vote
Sign in
Check!
(thinking…)
Reset
or sign in with
    Password icon
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Brent Ozar shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

    0 comments

    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
      Password icon
      Signed in as (Sign out)
      Submitting...

      Feedback and Knowledge Base