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.

Instant Initialization – What, Why and How?

http://www.brentozar.com/archive/2014/02/preemptive_os_writefilegather-and-instant-file-initialization-ifi/

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);



 

Package with XMLDatasource Migrated to SSIS 2014 with Error: “the data source you are using does not support mixed content in elements..”

When I migrate an SSIS package which includes an XMLDatasource to import data from SSIS 2008 to SSIS 2014,
I ran into the following error:
“the data source you are using does not support mixed content in elements..”
We have no control for the coming XML file so I decide to move the logic into database
and use an Execute SQL Task to pull data from the coming XML file.

Here is the query I used for the SQL Task:

--Target table
CREATE TABLE [dbo].[SomeTable](
	[cat-code] [nvarchar](255) NULL,
	[class-code] [nvarchar](255) NULL,
	[subclass-code] [nvarchar](255) NULL,
	[ad-number] [nvarchar](255) NULL,
	[start-date] [nvarchar](255) NULL,
	[end-date] [nvarchar](255) NULL
	
) 


--Solution query

declare @tmp table (XmlCol XML)

INSERT INTO @tmp(XmlCol) 
SELECT * FROM OPENROWSET( BULK 'E:\X\data\myXMLfeed.xml', SINGLE_BLOB) AS x ;
 


 insert into  [dbo].[Sometable]

SELECT 
   p.value('(./cat-code)[1]', 'NVARCHAR(255)') AS [cat-code],
   p.value('(./class-code)[1]', 'NVARCHAR(255)') AS [class-code],
   p.value('(./subclass-code)[1]', 'NVARCHAR(255)') AS [subclass-code],
   p.value('(./ad-number)[1]', 'NVARCHAR(255)') AS [ad-number],
   p.value('(./start-date)[1]', 'NVARCHAR(255)') AS [start-date],
   p.value('(./end-date)[1]', 'NVARCHAR(255)') AS [end-date]     
FROM @tmp 
   CROSS APPLY XmlCol.nodes('/web-export/run-date/pub-code/ad-type/.') t(p)

 

A few Queries that are related to Agent Information

You can tweak a few useful queries Datta Sindol compiled to make them useful for you if you want to get some information about agent jobs from MSDB.
Here is the link:

http://www.mssqltips.com/sqlservertip/2561/querying-sql-server-agent-job-information/