usp_AdaptiveIndexDefrag question about 'Not partition number specific'

[d-moloney]

I am receiving the message Not partition number specific..

On a SQL 2017 database server.

Any insight on why I get this message. I tried looking at the code, but can't seem to figure it out.

Debug and print mode indicates

There are still fragmented indexes or out-of-date stats from last execution. Resuming... Looping through batch list... There are 39 indexes to defragment in 7 database(s)! Looping through batch list... There are 39 index related statistics to update in 7 database(s), plus 1696 other statistics to update in 13 database(s)! Selecting an index to defragment... Getting partition count... Getting selected index information... Not partition number specific... Checking if any LOBs exist... Checking for Columnstore index... Checking if index does not allow page locks... Found 17.5896 percent fragmentation on index [PK_spotlight_playback_data]... Building SQL statements... Index eligible for REORGANIZE... Printing SQL statements... Executing: .

.

[croblesm]

Based on the code, this is not an error or something for you to worry about. This is more an informative message to let you know there are no partitioned indexes.

Here is the fragment of the code, where this validation occurs:

/* Get object names and info */
IF @partitionCount > 1 AND @dealMaxPartition IS NOT NULL AND @editionCheck = 1
BEGIN
    IF @debugMode = 1
        RAISERROR ('    Partition number specific...',0,42) WITH NOWAIT;

    SELECT TOP 1 @objectName = objectName
        ,@schemaName = schemaName
        ,@indexName = indexName
        ,@dbName = dbName
        ,@fragmentation = fragmentation
        ,@partitionNumber = partitionNumber
        ,@pageCount = page_count
        ,@range_scan_count = range_scan_count
        ,@is_primary_key = is_primary_key
        ,@fill_factor = fill_factor
        ,@record_count = record_count
        ,@ixtype = [type]
        ,@is_disabled = is_disabled
        ,@is_padded = is_padded
        ,@has_filter = has_filter
        ,@currCompression = [compression_type]
    FROM dbo.tbl_AdaptiveIndexDefrag_Working
    WHERE objectID = @objectID
        AND indexID = @indexID
        AND dbID = @dbID
        AND ((@Exec_Print = 1 AND defragDate IS NULL)
        OR (@Exec_Print = 0 AND defragDate IS NULL AND printStatus = 0))
    ORDER BY partitionNumber ASC; -- ensure that we have always the same sequence in order to continue resumeable operations
END
ELSE
BEGIN
    RAISERROR ('    Not partition number specific...',0,42) WITH NOWAIT;

    SELECT TOP 1 @objectName = objectName
        ,@schemaName = schemaName
        ,@indexName = indexName
        ,@dbName = dbName
        ,@fragmentation = fragmentation
        ,@partitionNumber = NULL
        ,@pageCount = page_count
        ,@range_scan_count = range_scan_count
        ,@is_primary_key = is_primary_key
        ,@fill_factor = fill_factor
        ,@record_count = record_count
        ,@ixtype = [type]
        ,@is_disabled = is_disabled
        ,@is_padded = is_padded
        ,@has_filter = has_filter
        ,@currCompression = [compression_type]
    FROM dbo.tbl_AdaptiveIndexDefrag_Working
    WHERE objectID = @objectID
        AND indexID = @indexID
        AND dbID = @dbID
        AND ((@Exec_Print = 1 AND defragDate IS NULL)
        OR (@Exec_Print = 0 AND defragDate IS NULL AND printStatus = 0));
END

Hopefully this makes sense and answers your question :)

Cheers!

[biohazardxxx]

Would be good to add "info:" to the message so people don't worry and will not google it each time :-)

[d-moloney]

I agree, this, this kind of message really doesn't make sense. I am not even sure its necessary to display, Its like the premium youtube offer, keeps reminding you even though you don't want it, need it or ever want to see the message again.

[cpotemski]

In my case the @editionCheck failed because the SELECT SERVERPROPERTY('EditionID') of my database was not listed as "supports partition-wise operations". I added my EdititonId and now it uses the partition specific commands (if configured)