Expert Performance Indexing in Azure SQL and SQL Server 2022 by Edward Pollack & Jason Strate

Author:Edward Pollack & Jason Strate
Language: eng
Format: epub
ISBN: 9781484292150
Publisher: Apress

Listing 11-30Script to Build Index Defragmentation Statements

Note

One of the improvements in SQL Server 2017 Enterprise Edition is the ability to perform online index rebuilds when the index contains columns with large object (LOB) data types.

Combining all of these pieces into the template from the beginning of this section to create an index defragmentation script provides similar functionality to that of the maintenance plan tasks. With the ability to set the size and fragmentation levels in which the defragmentation occurs, this script removes the fragmentation from indexes that really need the work done on them vs. defragmenting every index in the database. Using Extended Events on AdventureWorks2017 to trace the output of the script reveals that the ALTER INDEX syntax for the results of the previous queries is similar to that in Listing 11-31.ALTER INDEX ALL ON [HumanResources].[EmployeePayHistory] REBUILD WITH (ONLINE=ON, SORT_IN_TEMPDB=ON) ;

ALTER INDEX ALL ON [HumanResources].[JobCandidate] REORGANIZE; WITH (ONLINE=ON, SORT_IN_TEMPDB=ON) ;

ALTER INDEX ALL ON [dbo].[AllocationCycle] REBUILD WITH (ONLINE=ON, SORT_IN_TEMPDB=ON) ;

ALTER INDEX ALL ON [dbo].[PageCompression] REORGANIZE; WITH (ONLINE=ON, SORT_IN_TEMPDB=ON) ;

ALTER INDEX ALL ON [Sales].[SalesPersonQuotaHistory] REBUILD WITH (ONLINE=ON, SORT_IN_TEMPDB=ON) ;

Download

Copyright Disclaimer:
This site does not store any files on its server. We only index and link to content provided by other sites. Please contact the content providers to delete copyright contents if any and email us, we'll remove relevant links or contents immediately.