Skip to content

sql

SQL Server Parallel Index Defragmentation

I work for a company that has a pretty large SQL database. It’s about 4 terabytes in size. We had been using Ola Hallengren’s index maintenance script for a while but it got to be such that the script would take over 24 hours to run. That became a problem. That’s not to say there was really anything wrong with Ola’s script. It really is an excellent solution. However, it does only do index defragmentation serially … meaning one at a time. The same would be true for SQL Server Maintenance Plans. It works fine on smaller databases but when you have a HUGE database that no longer works well.

To solve this I created multiple jobs using his script and tried to break up the indexes between the jobs to have somewhat of a balance between them. That became a pretty big hassle to maintain. I would have to every-once-in-a-while go in and tweak it and some days it would work fine and other days it would not. So, I set out to see if anyone had created a solution to defragment indexes in parallel … meaning more than one at a time. I could not find one on the web so I built my own.

My solution will create a SQL Agent controller job and multiple worker jobs. The controller job will create a queue of indexes to be defragmented based on runtime parameters passed to it. Once the queue is created it will spawn the worker jobs which will proceed to take indexes off the queue until the queue is depleted or the termination window has expired.

The below table shows the performance gains we were able to get with our index defragmentation. Just using one job with Ola’s script took over 24 hours. When we broke it out into four jobs it still took 6-8 hours. With my script and six worker jobs the time went down to 3 hours for most all the indexes. There are two REALLY big indexes that take about 7 and 4 hours individually because the data in the table changes a lot daily. Finally, I realized that most of the indexes were set to a fill factor of 100%. Which means that I was constantly having high fragmentation on tables. So, I rebuilt all the indexes at 85% fill factor and the defragmentation time went down to about an hour per day with those two other indexes still being the outliers. I feel like that is a pretty good result.

ScriptJobs Duration
Ola Hallengren’s script1Over 24 hours
Ola Hallengren’s script46-8 hours
My script63 hours with 1 index at 7 hours and 1 at 4 hours
My script with fill factor at 85%6Monday: 1 hour (30 minutes of that is to build the queue) Other Days: 20 minutes (15 minutes of that is to build the queue)

The maintenance solution is release under the MIT License so feel free to use it as you wish just please let me know if you have found it to be helpful. You can click the button below to download the solution. The second link is to the source code repository.

https://bitbucket.org/davinstuder/index-maintenance/

Release Notes

1.3.1 – Added a check to see if the index can be defragmented online or not. Added start and end logging to the controller stored procedure. Fixed an issue with error logging not saving the error details
1.3.0 – Worker jobs are no longer statically created. They are spawned at runtime dynamically by the controller job. Cleaned up some SQL to standardize on formatting.
1.2.0 – Added history cleanup
1.0.2 – Removed references to deprecated master.dbo.UpdateIndexesQueueHistory table
1.0.1 – Edited query to find highest compatibility level for server
1.0.0 – Initial release

Using SQL to Query up Your Agent Job Run History

It is important to keep an eye on the run history of your agent jobs. That way you are aware if your jobs fail or the time they take to run is starting to creep upward. Ultimately, you should at the very least have email notifications sent to tell you if your jobs fail. However, what if a job that used to take one hour to run is now taking 10 hours? How will you know this is happening if the job doesn’t actually fail? The only way is to make sure you are keeping an eye on it, but that can be cumbersome with SQL Server Management Studio. One beef I have with SSMS is that it tells you the time your job started and how long it took, but to get the end time you have to do the math. Ugh! The below SQL will give you the run history of your jobs with a start and end time! Woo hoo! No more date/time math. It will also give you the duration hours, minutes, and seconds in separate columns so you can easily see trends in the time your jobs take to run. I hope you find it helpful.

use msdb

select
	J.name,
	dbo.agent_datetime(H.run_date, H.run_time) start_time,
	DATEADD(second, run_duration%100, DATEADD(minute, run_duration/100%100, DATEADD(hour, run_duration/10000, dbo.agent_datetime(H.run_date, H.run_time)))) end_time,
	run_duration/10000 duration_hours,
	run_duration/100%100 duration_minutes,
	run_duration%100 duration_seconds,
	case
		when H.run_status = 0 then 'Failed'
		when H.run_status = 1 then 'Succeeded'
		when H.run_status = 2 then 'Retry'
		when H.run_status = 3 then 'Canceled'
		when H.run_status = 4 then 'In Progress'
	end run_status,
	H.message
from msdb.dbo.sysjobs J
inner join msdb.dbo.sysjobhistory H on J.job_id = H.job_id
where step_id = 0
order by
	J.name, start_time desc

Using SQL to Query up Your Agent Job Details

If you are a SQL DB admin the odds are you have a bunch of SQL agent jobs that run throughout the day. If you haven’t yet, at some point you will want to audit the specifics of those jobs and when they are running. The below script will query up the schedule details of your SQL Agent jobs. The script will also suggest SQL to change the job owner and schedule owner of any of your jobs/schedules that are owned by user logins. If you’ve not been bit by that in the past it is important to note that if a user login owns a job and that login is removed or denied access to connect to the database engine the job will cease to run correctly. Thus, I recommend setting the owner for all your jobs to “sa” to make sure that your jobs continue to run in perpetuity.

use msdb

select
	J.job_id,
	J.name 'job_name',
	case 
		when J.notify_level_email = '0' then 'Never'
		when J.notify_level_email = '1' then 'When the job succeeds'
		when J.notify_level_email = '2' then 'When the job fails'
		when J.notify_level_email = '3' then 'Whenever the job completes (regardless of the job outcome)'
		else cast(J.notify_level_email as varchar)
	end notify_level_email,
	O.name 'notify_name',
	O.email_address 'notify_email_address',
	SUSER_SNAME(J.owner_sid) 'job_owner',
	case
		when SUSER_SNAME(J.owner_sid) <> 'sa' and SUSER_SNAME(J.owner_sid) not like '##%' then 'exec dbo.sp_update_job @job_id=''' + cast(J.job_id as varchar(255)) + ''', @owner_login_name = ''sa'''
	end as job_owner_fix,
	J.enabled 'job_enabled',
	S.schedule_id,
	S.name 'schedule_name',
	S.enabled 'schedule_enabled',
	SUSER_SNAME(S.owner_sid) 'schedule_owner',
	case
		when SUSER_SNAME(S.owner_sid) <> 'sa' and SUSER_SNAME(S.owner_sid) not like '##%' then 'exec dbo.sp_update_schedule @schedule_id=' + cast(S.schedule_id as varchar) + ', @owner_login_name = ''sa'''
	end as schedule_owner_fix,
	case
		when S.freq_type = 1 then 'Once'
		when S.freq_type = 4 then 'Daily'
		when S.freq_type = 8 then 'Weekly'
		when S.freq_type = 16 then 'Monthly'
		when S.freq_type = 32 then 'Monthly relative'
		when S.freq_type = 64 then 'When SQLServer Agent starts'
	end frequency,
	left(convert(varchar, dbo.agent_datetime('19000101', S.active_start_time), 114), 8) start_time,
	case
		when S.freq_subday_interval = 0 then null
		else left(convert(varchar, dbo.agent_datetime('19000101', S.active_end_time), 114), 8)
	end end_time,
	case
		when S.freq_subday_interval = 0 then 'Once'
		else cast('Every ' + right(S.freq_subday_interval, 2) + ' ' +
			case
				when S.freq_subday_type = 1 then 'Once'
				when S.freq_subday_type = 4 then 'Minutes'
				when S.freq_subday_type = 8 then 'Hours'
			end as char(16))
	end as 'sub_frequency',
	case
		when S.freq_type = 8 and S.freq_interval & 1 = 1 then 'x'
		else null
	end sunday,
	case
		when S.freq_type = 8 and S.freq_interval & 2 = 2 then 'x'
		else null
	end monday,
	case
		when S.freq_type = 8 and S.freq_interval & 4 = 4 then 'x'
		else null
	end tuesday,
	case
		when S.freq_type = 8 and S.freq_interval & 8 = 8 then 'x'
		else null
	end wednesday,
	case
		when S.freq_type = 8 and S.freq_interval & 16 = 16 then 'x'
		else null
	end thursday,
	case
		when S.freq_type = 8 and S.freq_interval & 32 = 32 then 'x'
		else null
	end friday,
	case
		when S.freq_type = 8 and S.freq_interval & 64 = 64 then 'x'
		else null
	end saturday,
	convert(varchar, dbo.agent_datetime(S.active_start_date, '000000000'), 101) duration_start,
	case
		when S.active_end_date = '99991231' then null
		else convert(varchar, dbo.agent_datetime(S.active_end_date, '000000000'), 101)
	end duration_end
from msdb.dbo.sysjobs J
left outer join msdb.dbo.sysoperators O on J.notify_email_operator_id = O.id
left outer join msdb.dbo.sysjobschedules JS on J.job_id = JS.job_id
left outer join msdb.dbo.sysschedules S on JS.schedule_id = S.schedule_id
order by
	J.name

Doing a select on a stored procedure

I came across a scenario today where I just wished that I could do a SQL select on a stored procedure. Akin to something like this …

select * from (exec myStoredProcedure) where column = 'value'

Alas, this is not possible … urg! However, I did manage to find a workaround that gives me basically what I wanted. It involves turning on some insecure settings in SQL sever. So we want to make sure we turn them back off after the fact.

The gist of the below SQL is as follows. We first enable the insecure settings. Next we execute the stored procedure using openrowset and store the results into a temp table. Once we have the data in a temp table we can work with it just like any other table, yeay!! Finally, we want to make sure to turn off the openrowset feature again.

/**********************************/
/* Allow openrowset               */
/**********************************/
sp_configure 'Show Advanced Options', 1
go
reconfigure
go
sp_configure 'Ad Hoc Distributed Queries', 1
go
reconfigure
go
/***********************************/

/***********************************/
/* This is the query               */
/***********************************/
select * into #tempTable from openrowset('SQLNCLI', 'Server=(local);Trusted_Connection=yes;','exec myStoredProcedure')
select * from #tempTable -- <-- Do your stuff here
/***********************************/


/**********************************/
/* Set the insecure settings back */
/**********************************/
sp_configure 'Ad Hoc Distributed Queries', 0
go
reconfigure
go
sp_configure 'Show Advanced Options', 0
go
reconfigure
go
/**********************************/