October 13, 2014

Often when query tuning you’ll find that the execution plan is skewed by an insert/update statement and will have a high value such as 99% of the query plan making it difficult to find the costly parts of your query.

Use DBCC SETIOWEIGHT to reduce the influence of the IO


caused my insert to drop from 99% to 3% of my execution plan making it a lot easier to spot the expensive operations.

split a set of tables up into equalish size groups

October 2, 2014

problem: transfer a set a tables from one server to another, 4 tables in parallel, using a pre-created stored proc, they should all complete around the same time

solution: within a dataflow create a source which fetches your list of tables and each tables size, include a row number field order on size in descending order, put this in a cte.

Select from the cte and create a new column as follows 1+(((r%4)+(CEILING(r/4.0))) % 4) called group_id, this ensure that each group is of a similar size, I cant explain why it works, it made sense as I was writing it, it just works.

Use a conditional split to send each group to an ole db command operator each of which executes your import procedure and passes the relevant parameters

azure transfer wizard bcp settings

September 24, 2014

EXEC xp_cmdshell ‘bcp.exe “dbase.schema.table” out “file path” -E -n -C RAW -S ab1-srv-ab02 -T’

EXEC xp_cmdshell ‘bcp.exe “dbase.schema.table” in “file path” -E -n -C RAW -b 10000 -a 16384 -q -S ab1-srv-ab02 -T’

format / convert

September 17, 2014

Tuning a piece of code today I managed to get the execution time down from 32 minutes to 2 minutes by replacing a format function with a convert function. Format is a clr function and doesnt perform well against large datasets.

sidestep view/funct​ion limitation​s

September 16, 2014

Problem: need to create an object that can be used in a select from statement but due to limitations such as not being allowed to use variables in views or use side effecting operators in functions no object can be easily created.


Create a stored procedure which returns the required dataset
Ensure a self referencing linked server exists on the server
Create view as a select * from openquery(SERVER,’exec stored_proc_name’)

in some cases you may need to modify the openquery statement

if your proc uses dml use the nocount on clause
if your proc uses temp table use the set fmtonly on clause

eg: Create view as a select * from openquery(SERVER,’SET NOCOUNT ON;SET FMTONLY OFF;exec stored_proc_name’)

View running jobs

September 5, 2014

EXEC master.dbo.xp_sqlagent_enum_jobs 1,’xxx’ will output a table where running = 1 indicates a job is running

the following will return a list of running jobs

FROM msdb.dbo.sysjobactivity a
INNER JOIN msdb.dbo.sysjobs b ON a.job_id=b.job_id
WHERE session_id = (SELECT MAX(session_id) FROM msdb.dbo.syssessions)
AND start_execution_date IS NOT NULL AND stop_execution_date IS NULL

add a row details group to a SSRS table where all other groups have been deleted

September 2, 2014

View code, search for TablixRowHierarchy

replace {TablixMember /}


{Group Name=”Details” /}
{TablixMember /}

and switch back to your report, make sure to replace curly brackets with less than / greater than symbols (which are not allowed by wordpress on this page)

Capturing dead lock graph using extended event

September 1, 2014

event: xml_deadlock_report

output the data to a file so that when you view target data you will be presented a deadlock graph which give you the visual deadlock graph, this isnt visible if you output to ring buffer

Allow non admin users to connect to SSIS remotely

August 26, 2014

When a user without sufficient rights attempts to connect to an instance of Integration Services on a remote server, the server responds with an “Access is denied” error message. You can avoid this error message by ensuring that users have the required DCOM permissions.

If the user is not a member of the local Administrators group, add the user to the Distributed COM Users group. You can do this in the Computer Management MMC snap-in accessed from the Administrative Tools menu.
Open Control Panel, double-click Administrative Tools, and then double-click Component Services to start the Component Services MMC snap-in.

Expand the Component Services node in the left pane of the console. Expand the Computers node, expand My Computer, and then click the DCOM Config node.
Select the DCOM Config node, and then select SQL Server Integration Services 11.0 in the list of applications that can be configured.

Right-click on SQL Server Integration Services 11.0 and select Properties.

In the SQL Server Integration Services 11.0 Properties dialog box, select the Security tab.
Under Launch and Activation Permissions, select Customize, then click Edit to open the Launch Permission dialog box.

In the Launch Permission dialog box, add or delete users, and assign the appropriate permissions to the appropriate users and groups. The available permissions are Local Launch, Remote Launch, Local Activation, and Remote Activation. The Launch rights grant or deny permission to start and stop the service; the Activation rights grant or deny permission to connect to the service.

Click OK to close the dialog box.

Under Access Permissions, repeat steps 7 and 8 to assign the appropriate permissions to the appropriate users and groups.

Close the MMC snap-in.

Restart the Integration Services service.

What is filling up the transaction log?

August 14, 2014

SELECT a.transaction_id ,
DB_NAME(database_id) dbase_name,
database_transaction_begin_time ,
database_transaction_log_record_count ,
database_transaction_log_bytes_used ,
database_transaction_log_bytes_reserved ,
FROM sys.dm_tran_database_transactions a
LEFT JOIN sys.dm_tran_session_transactions b ON a.transaction_id = b.transaction_id
ORDER BY database_transaction_log_record_count DESC


Get every new post delivered to your Inbox.