0

I'd like to use

EXECUTE [master].dbo.IndexOptimize
@Databases = 'mydatabase',
@FragmentationLow = NULL,
@FragmentationMedium = NULL,
@FragmentationHigh = NULL,
@UpdateStatistics = 'ALL',
@MinNumberOfPages = 0,
@MaxNumberOfPages = 10000,
@StatisticsSample = 100,
@MaxDOP = 1,
@LogToTable = 'Y';

to use FULLSCAN in case of smaller tables, then use another job step, another invocation for tables - say - 10.001 to 1.000.000 pages using @StatisticsSample of 50, and then smaller @StatisticsSample for even larger tables. After I'm done with initial maintenance, on further runs I also plan to use @OnlyModifiedStatistics = 'Y', obviously.

Does IndexOptimize obey the @MaxNumberOfPages when updating statistics?

I've just launched it and it processes statistics for a table of 172.217.713 rows, so I suppose it doesn't.

Is this by design? Could it be improved?

0

2 Answers 2

2

Don't fall into the hole I did!

The page count check does not take into account LOB data to calculate the number of pages the table is using. So, for example if in row data is under the @MaxNumberOfPages but you have say a varchar(max) datatype on the table that is stored out of row then get ready for that table to be included in your list and a long reindex to occur (I had 30 GB of LOB on one table for example)

I cobbled together this script to help you find if your table is using LOB datatypes and list out how much LOB data is on the table and the columns using LOB. Enjoy....

declare @tblName varchar(999) ='MyTableName'

select case when spc.type in (1, 3) then 'Regular data'
            else 'LOB data' end as allocation_type,
    cast(sum(spc.used_pages * 8) / 1024.00 as numeric(36, 2)) as used_mb,
    cast(sum(spc.total_pages * 8) / 1024.00 as numeric(36, 2)) as allocated_mb
    ,tab.name
from sys.tables tab
    inner join sys.indexes ind 
        on tab.object_id = ind.object_id
    inner join sys.partitions part 
        on ind.object_id = part.object_id and ind.index_id = part.index_id
    inner join sys.allocation_units spc
        on part.partition_id = spc.container_id
        where tab.name =@tblName
group by tab.name, case when spc.type in (1, 3) then 'Regular data' 
        else 'LOB data' end

        select t.table_schema as schema_name,
    t.table_name, 
    c.column_name,
    c.data_type
from information_schema.columns c
    inner join information_schema.tables t
        on c.table_schema = t.table_schema
        and c.table_name = t.table_name
where t.table_name = @tblName 
and ((c.data_type in ('VARCHAR', 'NVARCHAR') and character_maximum_length = -1)
or c.data_type in ('TEXT', 'NTEXT', 'IMAGE', 'VARBINARY', 'XML', 'FILESTREAM'))
order by t.table_schema, 
    t.table_name,
    c.column_name
1
  • Thank you, that is an interesting insight! Unfortunately I did not make a note about the table involved and I forgot the details. But I'll try to remember your advice next time I'll be working on similar issue.
    – Endrju
    Commented Oct 22, 2023 at 12:23
1

I think the table you are using has a lot of rows, however 1 page (depending on the size of the rows), can have multiple rows. So therefor the number of rows you are given are a unrelated to the Pages, which the script uses:

SELECT SUM(page_count) FROM sys.dm_db_index_physical_stats(DB_ID(@ParamDatabaseName), @ParamObjectID, @ParamIndexID, @ParamPartitionNumber, ''LIMITED'') WHERE alloc_unit_type_desc = ''IN_ROW_DATA'' AND index_level = 0'

to define the number of pages. Later in the script it uses this to define if it will use this to check if it is between the given input:

AND (@CurrentPageCount >= @MinNumberOfPages OR @MinNumberOfPages = 0)
AND (@CurrentPageCount <= @MaxNumberOfPages OR @MaxNumberOfPages IS NULL)

To check the number of pages Ola uses, you can use what he is using:

SELECT SUM(page_count)
FROM   sys.dm_db_index_physical_stats(
                                        DB_ID('database name'),
                                        OBJECT_ID('table name'),
                                        DEFAULT,
                                        DEFAULT,
                                        'LIMITED'
                                     )
WHERE  alloc_unit_type_desc = 'IN_ROW_DATA'
       AND index_level = 0;

Perhaps your table has different partitions?

You can edit the Ola script to PRINT the outcomes, or the strings he will execute to show the data he is working with.

4
  • Thanks Peter. Even if there was the absolute maximum 8192 rows per 1 page, you can't fit 172 million rows in under 10 thousand pages, it's just impossible. The exact numbers for this table are: TotalPages:1.040.241 UsedPages:1.040.156 UnusedPages:85. So the @MaxNumberOfPages value wasn't obeyed.
    – Endrju
    Commented Dec 13, 2022 at 15:27
  • 1
    And when you use the query Ola uses? What rowcount do you have? Perhaps that one is different than the one I sent
    – Peter
    Commented Dec 13, 2022 at 15:33
  • 2
    There it is! The script is getting index size from sys.dm_db_index_physical_stats with LIMITED (line 1913). For LIMITED it's NULL. That explains everything. It should use sys.dm_db_partition_stats instead.
    – Endrju
    Commented Dec 13, 2022 at 15:42
  • 1
    I will remove the other query from my answer for future reference!
    – Peter
    Commented Dec 13, 2022 at 15:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.