Rebuild indexes on MS SQL, such as

Small daily maintenance manual.

Rebuild indexes

What can serve? Rebuild indexes: index rebuild.

Find out if your DB there are fragmented indexes.
How much?
We should reorganize or rebuild them?

Here’s the answer:

SELECT ' ALTER INDE[' + + ']X ON[' + + '].[' + + '] ' +
       CASE WHEN PS. avg_fragmentation_in_percent > 40 THEN ' REBUILD ' ELSE ' REORGANIZE ' END +
       CASE WHEN 1 THEN ' PARTITION = partition_count > pc. ' + cast (PS. partition_number as nvarchar (max)) ELSE ' ' END
From sys. indexes AS ix INNER JOIN sys. tables t
           On t. object_id = object_id.
       INNER JOIN sys. schemas s
           On t. schema_id = s. schema_id
       INNER JOIN (SELECT object_id, index_id, avg_fragmentation_in_percent, partition_number
                   FROM _ _db_index_physical_stats (DB_ID (), NULL, NULL, NULL, NULL)) ps
           On t. object_id = object_id AND index_id = PS. ix. PS. index_id
       INNER JOIN (SELECT object_id, index_id, COUNT (DISTINCT partition_number) AS partition_count
                   From sys. partitions
                   GROUP BY object_id, index_id) pc
           On t. object_id = object_id AND index_id = ix. PCs. pc. index_id
Where PS. avg_fragmentation_in_percent > 10 AND IS NOT NULL

You get as output the list of TS-query SQl statements to execute. Not bad right?


Lascia un commento

Il tuo indirizzo email non sarà pubblicato. I campi obbligatori sono contrassegnati *

Un progetto da realizzare?

Start up, think tank, idee da sviluppare in sistemi e software ...

© ICT360. All rights reserved. Powered by ICT360.