Code to write code (part 3)

In the previous example, we took a command that runs manually to alter the index fill factor to adjust it from 80 to 100%. If you run it manually, this really means reactionary, and it takes one of the most precious resources, your time. What are the manual steps of this?

The selection of the databases
The trigger to say I need this to run
The time to actually execute the code

Selecting the database can be solved using one of two stored procedures sp_msforeachdb or called sp_foreachdb given out by Brent Ozar’s FirstResponderKit.

The parameters for the sp_msforeachdb are:

ALTER proc [sys].[sp_MSforeachdb]
@command1 nvarchar(2000),
@replacechar nchar(1) = N'?',
@command2 nvarchar(2000) = null,
@command3 nvarchar(2000) = null,
@precommand nvarchar(2000) = null,
@postcommand nvarchar(2000) = null
as

The paramters for sp_foreachdb are:

ALTER PROCEDURE [dbo].[sp_ineachdb]
-- mssqltips.com/sqlservertip/5694/execute-a-command-in-the-context-of-each-database-in-sql-server--part-2/
@command nvarchar(max) = NULL,
@replace_character nchar(1) = N'?',
@print_dbname bit = 0,
@select_dbname bit = 0,
@print_command bit = 0,
@print_command_only bit = 0,
@suppress_quotename bit = 0, -- use with caution
@system_only bit = 0,
@user_only bit = 0,
@name_pattern nvarchar(300) = N'%',
@database_list nvarchar(max) = NULL,
@exclude_pattern nvarchar(300) = NULL,
@exclude_list nvarchar(max) = NULL,
@recovery_model_desc nvarchar(120) = NULL,
@compatibility_level tinyint = NULL,
@state_desc nvarchar(120) = N'ONLINE',
@is_read_only bit = 0,
@is_auto_close_on bit = NULL,
@is_auto_shrink_on bit = NULL,
@is_broker_enabled bit = NULL,
@user_access nvarchar(128) = NULL,
@Help BIT = 0,
@Version VARCHAR(30) = NULL OUTPUT,
@VersionDate DATETIME = NULL OUTPUT,
@VersionCheckMode BIT = 0
-- WITH EXECUTE AS OWNER – maybe not a great idea, depending on the security your system
AS

When you see the code implemented, you will see why you want to use sp_foreachdb over sp_msforeachdb. For me, the parameters of @exclude_list, @system_only and, @user_only sold me. But there are a lot more possibilities also. Read the documentation and the code. You will see a lot of good patterns here.

The trigger was to run the code manually. We can create an agent job to run this on a schedule. And this allows it to run without you in the office. Meaning the third item, “time,” resolved itself.

From the parameters listed above in both stored procedures, you select SQLCommand this is text passed in a SQL script with a few adjustments. You have to quote the string, so every quote also needs a quote, and you see stuff like ‘’ (double single quotes) all over the place, and the SSMS editor gets confused when color-coding it. Also, with complex code that naturally contains single quotes, carriage returns, or line feeds, it gets a tad messy. You also have a special variable, “?” this stores the database you are working in.

Here is the overview of the code:

Create a temp table, variables, and the string of the command we are going to run
Call this with sp_foreachdb inserting into the temp table, repeat until all the databases have been processed.
Loop through the table, one row at a time
With the for executing sp_executesql and other @SQLCMD with data from the table
After the loop, clean the house. I like to do this initially to be sure.

Step 1 – Create the variables and temp table

 DECLARE @SQLCmd NVARCHAR(2000)

DECLARE @Iterator INT

DECLARE @MaxIterator INT

 

CREATE TABLE #FillFactor

( Iterator   INT IDENTITY(1, 1),

DBName     NVARCHAR(128) NOT NULL,

TBName     NVARCHAR(128) NOT NULL,

AlterStmt  NVARCHAR(300) NOT NULL

)

Step 2 – create the data collector to pass to sp_MSforeachdb

 SELECT @SQLCmd = 'IF ''?'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'') BEGIN

USE ?

INSERT INTO #FillFactor

( [DBName], [TBName], [AlterStmt] )

SELECT  DB_NAME(), o.name,

N''ALTER INDEX ''+i.name+ N'' ON ''+DB_NAME()+N''.''+s.name+N''.''+o.name+ N'' REBUILD WITH (FILLFACTOR = 100); ''

FROM sys.indexes i

JOIN sys.objects o on i.object_id = o.object_id

JOIN sys.schemas s on o.schema_id = s.schema_id

WHERE fill_factor <= 80

AND fill_factor <> 0

AND is_disabled = 0

AND is_hypothetical = 0; END'

 

EXEC sp_MSFOREACHDB @SQLCmd

If you use the improved for each database code:

 SELECT @SQLCmd = 'USE ?

INSERT INTO #FillFactor

( [DBName], [TBName], [AlterStmt] )

SELECT  DB_NAME(), o.name,

N''ALTER INDEX ''+i.name+ N'' ON ''+DB_NAME()+N''.''+s.name+N''.''+o.name+ N'' REBUILD WITH (FILLFACTOR = 100); ''

FROM sys.indexes i

JOIN sys.objects o on i.object_id = o.object_id

JOIN sys.schemas s on o.schema_id = s.schema_id

WHERE fill_factor <= 80

AND fill_factor <> 0

AND is_disabled = 0

AND is_hypothetical = 0;'

 

EXEC sp_FOREACHDB @SQLCmd, @user_only = 1

I ran it on a database, with the command checking for <100% instead of <=80%. Yes, this gives me all indexes, but I am only testing, and here are the results:

Step 3 – loop through the temp table

We have to get the max iterator and an integrator counter

 /*set my range*/

SELECT @MaxIterator = MAX(Iterator), @Iterator = 1

FROM   #FillFactor;

Prep up the first command to run. I like calling this priming the pump. I have ever gone camping you understand the seeding the pump with water to allow the pump to work. Now we are ready to enter the loop and print statement to see what would be running. Finally, we increment the iterator and loop.

 /*initiate the first @SQLCmd*/

SELECT @SQLCmd = AlterStmt

FROM   #FillFactor

WHERE  Iterator = @Iterator;

Step 4 – Add the sp_executesql call before the print command.

 /*check for errors*/

BEGIN TRY

/*iterate through the set */

WHILE @Iterator <= @MaxIterator

BEGIN

EXEC sp_executesql @SQLCmd

Print @SQLCmd

 

/* Incement my counter*/

SET @Iterator = @Iterator +1;

 

/*get my next index*/

SELECT @SQLCmd = AlterStmt

FROM   #FillFactor

WHERE  Iterator = @Iterator;

 

END;

END Try

 

/* Well any errors*/

BEGIN CATCH

SELECT ERROR_NUMBER() AS ErrorNumber ,

ERROR_MESSAGE() AS ErrorMessage;

END CATCH;

Step 5 – remove the temp table

 /* Do some house cleaning*/

Drop Table #FillFactor;

Now the fun part, assemble it all together into one package. Schedule it to run.

 /*

<documentation>

<summary>Adjust FillFactor to 100% across all databases</summary>

<project>DBA Toolset</project>

<description>Alter index with FillFactor changes as need due to

ESRI process that reset the FillFactor to 80%</description>

<database>For each database on a server</database>

<calledBy>SQL Server Agent Job</calledBy>

<returns>Nothing</returns>

<issues>None</issues>

<author>Joe McBratnie</author>

<created>2020-03-01</created>

<modified>2021-04-19 by Joe McBratnie</modified>

<version>1.2</version>

<sourceLink></sourceLink>

</documentation>

*/

 

/*verify the house is clean before starting*/

IF OBJECT_ID('tempdb..#FillFactor') IS NOT NULL DROP TABLE #FillFactor

GO

 

DECLARE @SQLCmd NVARCHAR(2000)

DECLARE @Iterator INT

DECLARE @MaxIterator INT

 

CREATE TABLE #FillFactor

( Iterator   INT IDENTITY(1, 1),

DBName     NVARCHAR(128) NOT NULL,

TBName     NVARCHAR(128) NOT NULL,

AlterStmt  NVARCHAR(300) NOT NULL

)

 

SELECT @SQLCmd = 'USE ?

INSERT INTO #FillFactor

( [DBName], [TBName], [AlterStmt] )

SELECT  DB_NAME(), o.name,

N''ALTER INDEX ''+i.name+ N'' ON ''+DB_NAME()+N''.''+s.name+N''.''+o.name+ N'' REBUILD WITH (FILLFACTOR = 100); ''

FROM sys.indexes i

JOIN sys.objects o on i.object_id = o.object_id

JOIN sys.schemas s on o.schema_id = s.schema_id

WHERE fill_factor <= 80

AND fill_factor <> 0

AND is_disabled = 0

AND is_hypothetical = 0;'

 

EXEC sp_FOREACHDB @SQLCmd, @user_only = 1

 

/*set my range*/

SELECT @MaxIterator = MAX(Iterator), @Iterator = 1

FROM   #FillFactor;

 

/*initiate the first @SQLCmd*/

SELECT @SQLCmd = AlterStmt

FROM   #FillFactor

WHERE  Iterator = @Iterator;

 

/*check for errors*/

BEGIN TRY

/*iterate through the set */

WHILE @Iterator <= @MaxIterator

BEGIN

EXEC sp_executesql @SQLCmd

Print @SQLCmd

 

/* Incement my counter*/

SET @Iterator = @Iterator +1;

 

/*get my next index*/

SELECT @SQLCmd = AlterStmt

FROM   #FillFactor

WHERE  Iterator = @Iterator;

 

END;

END Try

 

/* Well any errors*/

BEGIN CATCH

SELECT ERROR_NUMBER() AS ErrorNumber ,

ERROR_MESSAGE() AS ErrorMessage;

END CATCH;

 

/* Do some house cleaning*/

Drop Table #FillFactor;

If you want, you can email the results. But you should also email on a failure. If you notice, I clean the house before and after it runs just to be sure all is good, at any time.

Things I don’t like about this the double single quotes make readability hard. You have to remember to force your white space in the command. Also, while debugging the code in a single line of text with no formatting.

Jmcbratnie

All author posts

Jmcbratnie

Add comment Cancel reply