Enable parallellism for specific query

Many times when we work with SQL Server based applications, we solve performance problems by adjusting parallellism settings to limit the genereration of parallell query plans. However, sometimes we need to do the opposite, that is allow certain queries to run with a parallell plan even if the server settings restrict or disable it. There are two settings that control this: Max Degree of Parallellism wich limits the number of worker threads that the individual operators in the query plan can use, and Cost Threshold for parallellism, wich defines the query plan cost limit beyond wich SQL Server starts evaluating parallell plans. So if you want to limit your query plan to use 2 CPU cores/Worker threads per operator you would use OPTION (MAXDOP 2) in the query. But what if you would like SQL Server to always allow parallell plans for this particular query only? This is not so well known, but there are actually an undocumented trace flag that will help you with that. I is called 8649 and what it does is effectively the same as setting Cost Threshold for parallellism to 0. But how do we do that for just one query? Well, we combine the trace flag with the QUERYTRACEON option, and viola 🙂  The  syntax is OPTION (QUERYTRACEON 8649)