Update usp_AdaptiveIndexDefrag.sql

[IanMoroney]

Fixes #196

[pmasl]

There shouldn't be a NULL stats id. Can you please turn on debug mode and let me know where this error is triggered? Maybe even make available the db clone (if no PII in there)?

[IanMoroney]

Can't give a clone of the DB, and even the debug output has IP (but not PII) data which I can't show and will take a while to obfuscate. I re-ran it in debug mode and it's not currently erroring, so it seems i can't replicate the issue. The problem was happening constantly though, to the point that the scheduled defrag would stop working in production, which is undesirable. I'd rather have a null or two in the Stats log table than have the whole process terminate. I can't have a scenario where the defrag doesn't work for weeks on end. I made this change locally and it fixed the problem, hence contributing it here. Ultimately, yes there probably is a root cause as to why nulls are being there, but this solution is better than the alternative imo.

[IanMoroney]

@pmasl , i've encountered the error with debug mode on, and below is the output. Note, my database is now encountering extremely poor performance, because the defrag job quit after encountering this issue, as it couldn't enter the null in the table.

Major ver
16
Microsoft SQL Azure (RTM) - 12.0.2000.8 
    Feb 23 2022 11:32:53 
    Copyright (C) 2021 Microsoft Corporation

Validating options...
Starting up...

Executing AdaptiveIndexDefrag v1.6.6.9 on Microsoft SQL Azure (RTM) - 12.0.2000.8 
    Feb 23 2022 11:32:53 
    Copyright (C) 2021 Microsoft Corporation

The selected parameters are:
Defragment indexes with fragmentation greater or equal to 5;
Rebuild indexes with fragmentation greater than 30;
Rebuild columnstore indexes with fragmentation greater than 10;
Defragment ALL indexes;
Commands WILL be executed automatically;
Defragment indexes in DESC order of the RANGE_SCAN_COUNT value;
Time limit was specified and is 480 minutes;
ALL databases will be defragmented;
ALL tables will be defragmented;
We will resume any existing previous run. If so, we WILL NOT be rescanning indexes;
The scan will be performed in LIMITED mode;
LOBs will be compacted;
Limit defrags to indexes with more than 8 pages;
Indexes will be defragmented OFFLINE;
Indexes will be sorted in the DATABASE;
Indexes will use CURRENT compression setting;
Indexes will keep its EXISTING Fill Factor;
All partitions will be considered;
Statistics WILL be updated on all stats belonging to parent table;
Statistics will be updated using a calculated threshold similar to TF2371 on tables.
Statistics will be updated with Incremental property (if any) not changed from current setting.
Defragmentation will use system defaults for processors;
Lock timeout is set to system default for offline rebuilds;
Starting with SQL Server 2014, lock timeout is set to 5 for online rebuilds;
Starting with SQL Server 2014, lock timeout action is set to SELF for online rebuilds;
 DO NOT print the sql commands;
 DO NOT output fragmentation levels;
Wait 00:00:05 (hh:mm:ss) between index operations;
Execute in DEBUG mode.

There are still fragmented indexes or out-of-date stats from last execution. Resuming...
Looping through batch list... There are 14 indexes to defragment in 1 database(s)!
Looping through batch list... There are 0 index related statistics to update in 0 database(s), plus 0 other statistics to update in 0 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 5.14417 percent fragmentation on index [nci_wi_Table_2B7192CD230141DC6D09A336561F3E05]...
  Building SQL statements...
We are unable to defrag index [nci_wi_Table_2B7192CD230141DC6D09A336561F3E05] on table [my-db-mycompany].[schema1].[Table]
 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 14.0949 percent fragmentation on index [PK__my___3214EC073FD54E23]...
  Building SQL statements...
  Printing SQL statements...
   Executing: 
     ALTER INDEX [PK__my___3214EC073FD54E23] ON [my-db-mycompany].[schema1].[Table2] REORGANIZE WITH (LOB_COMPACTION = ON);
   Updating index related statistics using finer thresholds (if any)...
   Selecting a statistic to update...
   Getting information on selected statistic...
   Determining modification row counter for statistic [PK__my___3214EC073FD54E23] on table or view [Table2] of DB [my-db-mycompany]...
     Using sys.dm_db_incremental_stats_properties DMF...
     Found a row modification counter of 0.000 and 2700478 rows...
     No need to update statistic [PK__my___3214EC073FD54E23] on table or view [Table2] of DB [my-db-mycompany]...
     Error 515 has occurred. Message: Cannot insert the value NULL into column 'statsID', table 'my-db-mycompany.dbo.tbl_AdaptiveIndexDefrag_Stats_log'; column does not allow nulls. INSERT fails. (Line Number: 2530)
 Reseting working table statuses.
 Dropping temporary objects
All done!

(1 row affected)

Completion time: 2022-05-09T14:36:22.0825279+01:00

[hvoerman]

I have the same issue.

Debug shows:

     No need to update statistic (1) [<index>] on table or view [<table>] of DB [<database>]...
     Error 515 has occurred. Message: Cannot insert the value NULL into column 'statsID', table '<database>.dbo.tbl_AdaptiveIndexDefrag_Stats_log'; column does not allow nulls. INSERT fails. (Line Number: 2117)
 Reseting working table statuses.
 Dropping temporary objects
All done!

Completion time: 2022-05-12T20:30:47.4269099+02:00

I added the (1) in de usp because this message (No need to update statistic) is used in 8 places and without some sort of indication there's no way to tell which of the eight messages is shown. Perhaps a suggestion to make every debugmessage unique, so you can find it back in the usp.

I hope this can be fixed.

[biohazardxxx]

Please merge!

[IanMoroney]

@pmasl can this be merged?