Issue: Long Running Database Restore and a Fix


I have a new QA server with a pretty slow restore operation. I found PREEMPTIVE_OS_WRITEFILEGATHER is very high by using Glenn Berry’s query
and I recalled from previous readings about this issue.
We need to assign SQL Server service account to perform volumn maintenance tasks.
The folllowing is how to and some references to explain why.

How to grant service account to “PERFORM VOLUME MAINTENANCE TASKS”

From Window machines search secpol.msc.

Use secpol.msc (search from window search box or search Gpedit.msc to drill down to Local Policies …)

From UI:
WIn 2012 Administrative Tools ->Local Security Policy>>Local Policies -> User Right Assignment -> Perform Volume Maintenance Tasks
Right click on it and click Add User or Group to add the SQL Server Service account running on your instance.

Restart SQL Server service to make the change into effect.

http://www.sqlskills.com/blogs/kimberly/instant-initialization-what-why-and-how/
http://www.brentozar.com/archive/2014/02/preemptive_os_writefilegather-and-instant-file-initialization-ifi/
http://sqlserverperformance.wordpress.com/2014/04/18/sql-server-diagnostic-information-queries-for-april-2014/

From above link:
(Glenn Berry’s check wait query)

   

;WITH Waits
AS (SELECT wait_type, CAST(wait_time_ms / 1000. AS DECIMAL(12, 2)) AS [wait_time_s],
	CAST(100. * wait_time_ms / SUM(wait_time_ms) OVER () AS DECIMAL(12,2)) AS [pct],
	ROW_NUMBER() OVER (ORDER BY wait_time_ms DESC) AS rn
	FROM sys.dm_os_wait_stats WITH (NOLOCK)
	WHERE wait_type NOT IN (N'CLR_SEMAPHORE', N'LAZYWRITER_SLEEP', N'RESOURCE_QUEUE',N'SLEEP_TASK',
			                N'SLEEP_SYSTEMTASK', N'SQLTRACE_BUFFER_FLUSH', N'WAITFOR', N'LOGMGR_QUEUE',
			                N'CHECKPOINT_QUEUE', N'REQUEST_FOR_DEADLOCK_SEARCH', N'XE_TIMER_EVENT',
			                N'BROKER_TO_FLUSH', N'BROKER_TASK_STOP', N'CLR_MANUAL_EVENT', N'CLR_AUTO_EVENT',
			                N'DISPATCHER_QUEUE_SEMAPHORE' ,N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'XE_DISPATCHER_WAIT',
			                N'XE_DISPATCHER_JOIN', N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', N'ONDEMAND_TASK_QUEUE',
			                N'BROKER_EVENTHANDLER', N'SLEEP_BPOOL_FLUSH', N'SLEEP_DBSTARTUP', N'DIRTY_PAGE_POLL',
			                N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',N'SP_SERVER_DIAGNOSTICS_SLEEP',
							N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP', N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',
							N'WAIT_XTP_HOST_WAIT', N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG', N'WAIT_XTP_CKPT_CLOSE',
							N'PWAIT_ALL_COMPONENTS_INITIALIZED')),
Running_Waits 
AS (SELECT W1.wait_type, wait_time_s, pct,
	SUM(pct) OVER(ORDER BY pct DESC ROWS UNBOUNDED PRECEDING) AS [running_pct]
	FROM Waits AS W1)
SELECT wait_type, wait_time_s, pct, running_pct
FROM Running_Waits
WHERE running_pct - pct <= 99
ORDER BY running_pct
OPTION (RECOMPILE);



 
Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s