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);

3 votes
Sign in
(thinking…)
Sign in with: oidc
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
(thinking…)
Sign in with: oidc
Signed in as (Sign out)
Submitting...

Feedback and Knowledge Base