Number of page into index for maintenance operation

Alen Cappelletti 891 Reputation points
2021-03-31T22:03:20.083+00:00

Hi all,
I'm using a script for reorganize/rebuild but it work on a threshold for the number of pages present:

  • @MinNumberOfPages int = 1000

Naturally after the operation some of them aren't reorganized/rebuilt.
I would like to know more about this setting... it's correct or it is better to lower it a little bit 'til 500.
Also, are numbers like 10/20/30 ridiculous?

Thanks Alen Itay

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,477 questions
0 comments No comments
{count} votes

Accepted answer
  1. Erland Sommarskog 100.1K Reputation points MVP
    2021-03-31T22:14:07.65+00:00

    I think 1000 pages is a magic number everyone has agreed on. If you want to do some testing to find out where it matters, well try it out.

    Although I think the testing Tibor Karaszi did some time back is more interesting. The question he wanted to answer was: with SSDs does it matter at all? http://sqlblog.karaszi.com/category/indexes/

    0 comments No comments

0 additional answers

Sort by: Most helpful