SQL Server Utilities - Use Powershell to Multi Thread SQL Server Index Maintenance tasks

This is my very first blog post at my own site after over a year of none blogging. What happened is that the site I use to blog at http://blogs.microsoft.co.il/yaniv_etrogi has gone down…. yes, I know…. with tones of my blog posts, I had so many posts just around the Replication features, Partitioning and lots more. Actually I think I started blogging there sometime around 2007.

Any how, moving forward In this post, I share a solution I developed for a client who has a true VLDB, that is a multi TB SQL Server database.

Here is the link to the git project in case you do not wish to go through the entire post.

Since this was my first time to implement multi threading in Powershell I took the time to do some reading and came across some cool posts.

Here is one that I would like to point out.

The process uses Powershell to implement the .NET CreateRunspacePool Method of the runspacefactory Class to develop a Multi Threading code that executes T-SQL tasks in parallel.

The Powershell script executes 2 Stored Procedures:

sp_get_indexes_to_defrag
sp_index_defrag

A sample execution of the T_SQL code would look like this

USE AdventureWorks2017;

--1.
EXEC dbo.sp_get_indexes_to_defrag 
		     @min_index_size_mb = 0
                    ,@exclude_current_partition = 1;

--2.
EXEC dbo.sp_index_defrag 
					 @reorg_threshold = 20
					,@rebuild_threshold = 70
					,@online = 1
					,@maxdop = 0
					,@sort_in_tempdb = 0
					,@single_index = 1
					,@debug = 0;

Some T-SQL related points to be aware

When @debug is set True nothing gets executed but printings only.
When @single_index is set True the procedure sp_index_defrag will process a single index and terminate. When @single_index is set False the procedure sp_index_defrag will process all indexes and only then terminate (a single threaded job the traditional way).
The table created by the procedure sp_get_indexes_to_defrag is a permanent object instead of a temporary object just to allow for easy tracking of the number of indexes yet to be processed.
The script creating the log table DBA_Maintenance_Log assumes the existence of a database named DBA.
Do not stay obligated to the fragmentation values adopted by the community as the best practices but adjust for your environment.
- For example if your edition do not support online rebuild and the index maintenance process is introducing database contention affecting other processes you can push the process towards reorganize only

Some Powershell related points to be aware

The value assigned to the variable $MaxThreads determines the number of simultaneous threads that will be working where each thread executes the stored procedure sp_index_defrag with the @single_index parameter set True. .
The variable $Server is being assigned the host name of the machine where the script is executed for a named instance this will need to be modified.
Take it easy and start slowly and do not assign a value greater than 4 to the $MaxThreads variable in order not to overwhelm the disk subsystem. Learn the generated workload and gradually adjust the value to meet your needs.
- In environments with Always On Availability Groups loading the Primary replica can result in a latency at the Secondary Replica(s).
- In environments with Transactional Replication be aware that the Log Reader agent may not keep up resulting in a latency at the Subscriber(s).
- In any environment where there are components consuming log records (i.e. Replication, Always On etc. ) there is a potential that these components will not keep up with the high paste leading to a state where there is no log truncation and the t-log size grows.
  - Be aware that rebuilding indexes ONLINE generates much more log records.
The ConnectionString is hard coded within the ps script and uses Integrated Security (Windows Authentication).
- In case SQL Authentication is required there is an example commented out ready for you to edit.
The Application Name=index_defrag in the connection string allows for easy tracking using SQL Trace, Profiler and XE.

Components included in the process

Object	Type	Description
1. index_defrag.ps1	Powershell script	The code than manages the entire process. This is the code that needs to be executed by a scheduler such as SQL Server Agent
2. sp_get_indexes_to_defrag	Stored Procedure	Populates the index_defrag_queue table with indexes information to be used as a queue
3. sp_index_defrag	Stored Procedure	Dependent on the queue table index_defrag_queue populated by the stored procedure sp_get_indexes_to_defrag
4. DBA_Maintenance_Log	Log Table	A table that logs all the operations including index fragmentation percent and exceptions