Rebuild Indexing on SQL Server 2016 standard Edition

Devendra Kumar Sahu 236 Reputation points
2021-12-18T09:03:21.75+00:00

i have SQL Server 2016 SP2 Standard Edition and ** 24*7 critical environment** and Database size 1.31TB and i have free space 186GB on drive. I check database in maintained plan Rebuild indexing is not start before one year till know approximately and i know the standard edition is work offline.

can i start Rebuild indexing?

I thought it long time and comes many blocking

Please Suggested me what can i do for this.... without any issue..

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,807 questions
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 101.7K Reputation points MVP
    2021-12-18T11:52:39.74+00:00

    Whether you can do it or not? How could I know? I mean, I don't know anything about your system, so I can't tell whether you can take the impact.

    Now, you do say "Standard Edition and 24*7 critical environment." I will say that it sounds to me that you are cheapskate. "24*7 critical" has Enterprise Edition all over it, not only for online index rebuilds.

    With Standard Edition, you will have to take downtime, at least when you rebuild critical tables.

    I would suggest that you first make a test in a backup environment, so that you can see how long time each table takes, and also how much log you produce and how much the log grows.

    REORGANIZE may be an alternative. It does not give equally good results as REBUILD, but it is always an online operation.

    Also, investigate if you have to do anything at all. With spinning disks, logical fragmentation is a big thing, that is, the pages in a clustered disk do not come in order on the disk. That matters quite little with SSDs. On the other hand, it is never good to have half-full pages, because that this means that you waste precious buffer-pool memory no nothing.

  2. CathyJi-MSFT 21,096 Reputation points Microsoft Vendor
    2021-12-20T05:55:12.61+00:00

    Hi @Devendra Kumar Sahu ,

    You can perform a reorg or rebuild an index based on its fragmentation values. Usually, you should rebuild the index if it has a fragmentation greater than 30% and reorganize it if it has less than 30% fragmentation.

    For information about how to maintenance index, refer to MS document Maintaining indexes using Transact-SQL.

    Or using Ola Hallengren's free scripts as Erland mentioned.

    If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".