Archive for October, 2014

SETIOWEIGHT

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

eg using DBCC SETIOWEIGHT(0)

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