What am I missing

Transact-SQL
1

Hello, I have the following two queries where the first one gives me values for "StatisticsDate" while the second one does not. I know it is because the object_id/index_id value is wrong but I cannot figure out what to use.

------------------------------------------------------------------------------------------
USE AcctEnc;
GO 
SELECT 
      QUOTENAME(t.name)                                     AS TableName 
    , QUOTENAME(i.name)                                     AS IndexName 
    , STATS_DATE(i.[object_id], i.index_id)                 AS StatisticsDate
    , ROUND(ISNULL(ps.avg_fragmentation_in_percent,0), 2)   AS Fragmentation
FROM sys.tables                 t 
INNER JOIN sys.indexes          i ON t.[object_id] = i.[object_id]
INNER JOIN sys.dm_db_index_physical_stats(db_id(), NULL, NULL, NULL, 'LIMITED') ps ON i.[object_id] = ps.[object_id] 
                                    AND i.index_id = ps.index_id 
WHERE t.[type] = 'U' 
    AND t.is_ms_shipped = 0 
ORDER BY StatisticsDate
    , IndexName;
GO
------------------------------------------------------------------------------------------
USE master;
GO
SELECT 
      QUOTENAME(t.name)                                     AS TableName 
    , QUOTENAME(i.name)                                     AS IndexName 
    , STATS_DATE(i.[object_id], i.index_id)                 AS StatisticsDate
    , ROUND(ISNULL(ps.avg_fragmentation_in_percent,0), 2)   AS Fragmentation
FROM AcctEnc.sys.tables                 t 
INNER JOIN AcctEnc.sys.indexes          i ON t.[object_id] = i.[object_id]
INNER JOIN sys.dm_db_index_physical_stats(db_id('AcctEnc'), NULL, NULL, NULL, 'LIMITED') ps ON i.[object_id] = ps.[object_id] 
                                    AND i.index_id = ps.index_id 
WHERE t.[type] = 'U' 
    AND t.is_ms_shipped = 0 
ORDER BY StatisticsDate
    , IndexName;
GO

Thank you for your time.
DJJ

2

INNER JOIN **AcctEnc.**sys.dm_db_index_physical_stats(db_id('AcctEnc'), NULL, NULL, NULL, 'LIMITED') ps ON i.[object_id] = ps.[object_id]
AND i.index_id = ps.index_id

3

Thank you for the reply.

Actually AcctEnc is not needed there (and does not solve the problem) as you can put the database in the parameters (see db_id). I had tried that at one point in time.

4

It's because of STATS_DATE function. It is looking up the information in the master database for the ids you passed in. You'll need to run the code in the user database instead. If this is part of a larger script and you can't switch to the user database, you can instead use dynamic SQL.

5

Instead of using STATS_DATE you may want to use the DMV:

Cross Apply sys.dm_db_stats_properties(ss.[object_id], ss.stats_id) sp

The code still needs to be run in the context of the database but this provides a better method of getting access to the stats properties. You can use this to identify the number of rows used to generate the stats which will tell you whether or not the stats were updated using a sampling rate or full scan.

6

@TaraKizer, this is for a dynamic process. :slight_smile:
I was trying to come up with a way to query all my databases to see what statistics have not been updated recently.

I have a couple different ways to get this but was working on a new way.

@jeffw8713, thank you, I will look into that I thought there might be a dmv but did not remember that one.

1 Like
7

Sorry, quite right, I answered too quickly.

8

Been there, done that. :blush:

This is just an exercise to see what different commands do. With the id values being at the database level running the functions in the master database is a problem.

By-the-way @jeffw8713, your suggestions works within the database but like STATS_DATE it does not work when not in the desired database context.

Thank you everyone for the suggestions. As I say, I am just trying to learn something new.

DJJ