-5

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?

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

1

2 Answers 2

2

Not enough data to suggest something, but in your case I would think about the following:

  1. If it works for a year without reindex, may it's fine for the current system? Are indexes fragmented at all?
  2. What is the actual size of data in the database? 1.3TB is total size, maybe there's a log file of 500-1000GBs?
  3. Real sizes of the indexes (many small ones or several large ones)?
  4. Is it ok to build a defragmented copy of existing fragmented index and then just drop old one?
  5. If rebuild is really needed, you can add another drive, expand your database there (new FG) and build new indexes there.
1
  • 1 Ans.:- Yes it working fine but some time we find blockage issue in database. Yes i need to indexes fragmented all table in database 2 Ans.:- Actual Size of Database is 1.26TB and Log File Size 20.3GB and Temp Size 80.1 GB 3 Ans.:- Yes it needed
    – Devendra
    Dec 20, 2021 at 5:00
1

You're right - index rebuild with Standard edition is offline operation (i.e. preventing sessions access the table whose index(es) are rebuilt). Are you really sure the indexes need to be defragmented? You can check the fragmentation with this query:

SELECT S.name as 'Schema',
T.name as 'Table',
I.name as 'Index',
DDIPS.avg_fragmentation_in_percent,
DDIPS.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS DDIPS
INNER JOIN sys.tables T on T.object_id = DDIPS.object_id
INNER JOIN sys.schemas S on T.schema_id = S.schema_id
INNER JOIN sys.indexes I ON I.object_id = DDIPS.object_id
AND DDIPS.index_id = I.index_id
WHERE DDIPS.database_id = DB_ID()
and I.name is not null
AND DDIPS.avg_fragmentation_in_percent > 0
ORDER BY DDIPS.avg_fragmentation_in_percent desc

Source: sqlshack

on 1.3TB DB, it'll take a while to finish the diagnostic query above. You also didn't write what is your biggest table in the DB, (besides other) the size of the table influences the REBUILD times. If the fragmentation on the indexes in question is < 30%, you would be fine with REORGANIZE instead of REBUILD. REORGANIZE is always online operation regardless of the SQL server version.

For the sake of the easier index rebuild, you could also think about partitioning the biggest tables, because you can rebuild individual partitions. However, there are many caveats with partitioning and for the sake of easier rebuild only, I can't recommend it.

Last thing - what is your motivation about defragmenting indexes? If it is performance tuning, reorganization rarely gets you "over the finish line".

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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