I tried to use this traceflag after reading the High Performance article from microsoft. I find it very useful on individual queries where you are accessing large tables. I have a base table of just over 320 million records and under certain circumstances I have seen improvements of over 1000%
Found something interesting today. I have an SSIS package that simply runs a DTS package using the 'Execute DTS 200 Package Task' (I know, I didn't like it either, but deadlines prevented a rewrite of this DTS, which happen to be 2 200 line active X tasks.) The DTS package currently exists within a SQL Server 2005 machine, so we were running this with 'Windows Authentication'. We kept the package security at 'EncryptSensitiveWithUserKey'To get this to run I needed first to make sure ran in 32-bit mode not 64-bit mode. After running I got this error:
SSIS was born for parallel processing, while sql server wasn't; well not from the users perspective. Yes, sql server breaks a user's query into parallel processes, but there is no way I can 'fork' within a stored procedure, or do an asynchronous call. I originally designed a process which I outline here "Parallel Processing in SQL"which works beautifully if in fact the work to be done is broken into equal parts.