Maintenance Trident Files

/*********************************************************/
/***              STRYK System Improvement             ***/
/***    Performance Optimization & Troubleshooting     ***/
/***   GNU General Public License (V3, 29 June 2007)   ***/
/***                   www.stryk.info                  ***/
/*********************************************************/

# # #  M A I N T E N A N C E   T R I D E N T  # # #
 
3 Powerful Stored Procedures for SQL Server Database Maintenance

PREFACE:

	<<It's not HOW you maintain it, it's important THAT you maintain it!>> (Jörg Stryk)

	Yaaawwwnnn ... Yet another set of Database Maintenance routines for SQL Server ... really?
	Yes, indeed! 
	Just adding my flavor of this matter, so you've got more to choose from!
	The M3 procedures should help the EXPERIENCED Database Administrator to quickly implement sufficient maintenance.
	Basically these Procedures just perform the essential Maintenance as Integrity Checks, Index Rebuilds and Statistic Updates,
	all pretty similar to other utilities or standard SQL Maintenance Plans.
	The DIFFERENCE is, that they provided several cool features - which are "regular" for nowadays Servers - 
	to older SQL Servers as well!
	Further, those SP are easy to parameterize and write some more detailed protocols than others. 	

	Enjoy!

CONTENT:

	ssi_checkdb.sql
	ssi_indexrebuild.sql
	ssi_updatestats.sql
	examples.sql
	gpl-3.0.txt
	
REQUIREMENTS:

	Microsoft SQL Server 2005 - 2017 [2019]

	Permissions:
	User must be member of SQL Server-Role "sysadmin"

LICENSE:

	Published under GNU General Public License (V3, 29 June 2007)
	"gpl-3.0.txt"
	https://www.gnu.org/
	
DISCLAIMER:

	USE AS IS, AT OWN RISK, NO WARRANTY, NO GUARANTEE, NO SUPPORT

INSTALLATION:

	Extract "Maint3dent.zip" to any folder and open the scripts in SQL Server Management Studio.
	Replace the tag [DatabaseName] with the name of the db to which you want to install the procedures to.
	This could be any database; [msdb] recommended.
	Execute scripts to install the Stored Procedures; then use SPs in SQL Agent Jobs or Maintenance Plans as required.
	
DESCRIPTION:

	M3/CHECKDB
	Replace [DatabaseName] (line 8) with name of the database where you want to install the SP to.
	The SP "ssi_checkdb" runs a DBCC CHECKDB command on the specified databases.
	It also temporarily changes the "Max. Degree Of Parallelism" (MAXDOP) on Instance level 
	according to the parameter specifications.

	SYNTAX:

		EXEC [ssi_checkdb]
		{
			{ @dbname }			-- name of database to be checked; LIKE patterns could be used; default: null
			{ ,@maxdop }		-- MAXDOP*; values: 0 - 64; default 0
			{ ,@noindex }		-- NOINDEX option; values: 0 = OFF | 1 = ON; default: 0
			{ ,@tablock }		-- TABLOCK option; values: 0 = OFF | 1 = ON; default: 0
			{ ,@physical_only }	-- PHYSICAL_ONLY option; values: 0 = OFF | 1 = ON; default: 0
			{ ,@data_purity }	-- DATA_PURITY option; values: 0 = OFF | 1 = ON; default: 0
		}

		*) @maxdop:
		In older SQL Servers the MAXDOP option is not available for the DBCC CHECKDB.
		This procedure temporarily changes the  MAXDOP setting of the SQL INSTANCE instead! e.g.
		MAXDOP_OLD ==> MAXDOP_NEW ==> DBCC ==> MAXDOP_OLD

		Refer to the "Books Online" about the details of the parameters.

	M3/INDEX REBUILD
	Replace [DatabaseName] (line 8) with name of the database where you want to install the SP to.
	The SP "ssi_indexrebuild" runs an ALTER INDEX REBUILD statement on fragmented indexes accoridn the parameters.
	(I'm not doing any REORGANIZE runs anymore; IMHO that's not feasible)

	SYNTAX:

		EXEC [ssi_indexrebuild]
		{
			{  @dbname }				-- name of database to be checked; LIKE patterns could be used; default: null
			{  ,@fillfactor }			-- set FILLFACTOR; values: 0 - 100; default: 0 (0 = use current FF; tipp: keep between 90 and 100)              
			{  ,@script_only }			-- Output ALTER INDEX statement only, don't execute; values: 0 = OFF | 1 = ON; default: 0
			{  ,@online }				-- ONLINE option; values: 0 = OFF | 1 = ON; default: 0 (only on several Editions; if not possible it will be automatically set to OFF)
			{  ,@pad }					-- PAD_INDEX option; values: 0 = OFF | 1 = ON; default: 0
			{  ,@sort_tempdb }			-- SORT_IN_TEMPDB option; values: 0 = OFF | 1 = ON; default: 0
			{  ,@min_fragmentation }	-- Minimum average fragmentation level in percent; values: 0 - 100; default: 30
			{  ,@maxdop }				-- MAXDOP option; values: 0 - 64; default: 64
			{  ,@min_pages }			-- Minimum index size in Pages; values: 0 - n; default: 1000
			{  ,@maxruntime }			-- maximum runtime* of procedure in minutes; values: 0 - n; default: 0 (0 = no limit)
		}

		*) @maxruntime:
		If set, the SP will calculate a designated stop-time; e.g. 
		STOP_TIME = START_TIME + MAXRUNTIME
		Before executing an ALTER INDEX statement it will check the current time; if stop-time is exceeded, it will cancel/abort; e.g.
		IF CURRENT_TIME < STOP_TIME THEN EXECUTE ELSE CANCEL

		Refer to the "Books Online" about the details of the parameters.

	M3/UPDATE STATISTICS
	Replace [DatabaseName] (line 8) with name of the database where you want to install the SP to.
	The SP "ssi_updatestas" runs a standard "sp_updatestats" on the specified databases.

	SYNTAX:

		EXEC [ssi_updatestats]
		{
			{ @dbname }		-- name of database to be checked; LIKE patterns could be used; default: null
			{ ,@resample }	-- RESAMPLE option; values: 0 = OFF | 1 = ON; default: 0
		}

		Refer to the "Books Online" about the details of the parameters.

	PERIODIC TASKS:

		Once the M3 Procedures have been installed they could/should be used in SQL Agents Jobs to be executed periodically.
		I recommend these frequencies:
		
		ssi_checkdb:		Minimum weekly; if time permits then daily.
							DBCC CHECKDB has a heavy "footprint" on the system and should run outside business hours.
							Tipp: save SQL Agent Job output to textfile!
						
		ssi_indexrebuild:	Usually weekly is sufficient; on heavily fragmenting databases maybe more often required.
							ALTER INDEX REBUILD has a heavy "footprint" on the system and could cause blocking (if ONLINE indexing cannot be used);
							it should run outside business hours.
							Tipp: save SQL Agent Job output to textfile!

		ssi_updatestats:	Ideally daily. The task has a very low "footprint" on the server, thus it could run any time.
	
	ERRORS AND OMISSIONS EXCEPTED

----------------------------------------------------------------------------------------------------

VERSIONS:

		1.0.0	Aug 2019	Jörg Stryk
		1.0.1	Aug 2020	Jörg STRYK
				Stored Procedure "ssi_indexrebuild":
				- Added hardcoded LOCKTIMEOUT of 10 seconds; line 277
				- Improved TRY CATCH part; lines 276 - 292

----------------------------------------------------------------------------------------------------
EOF