For a complex query how do i increase the time SQL Server searches for the best execution plan Our system generates SQL with a very large number of tests. I suspect on some occasions it is taking too long to find the ideal execution plan. So SQL just picks the best plan it has found so far which then takes a long time to run. Is it possible to increase the time SQL searches for the best plan?
There are some undocumented trace flags that allow one to increase or remove some of the limits that the optimizer uses when determining whether a "good enough" plan has been found.

However, all of the results I've ever seen show that plan quality is only minimally improved, if at all, when using these flags.  There is a good reason the timeouts and limits exist.  You're almost certainly better off breaking these queries up into smaller chunks somehow (great YouTube video from Erik Darling on the subject), to make them more manageable for the optimizer to make choices about.

Additionally, these trace flags are undocumented.  So if you do find a better plan using this method (on a test system!), you should try to get that plan using conventional means, like:


rewriting the query, 
adding documented query or index hints, 
adding / tweaking indexes,
using a plan guide if necessary


The trace flags I'm aware can be enabled at the query level like this:

/* your query here */
OPTION
(
    QUERYTRACEON 8780,
    QUERYTRACEON 8671
)


References:


8780: Optimizer Without Borders (Part 1) by Dmitry Pilugin
8671: Optimizer Without Borders (Part 2) also by Dmitry Pilugin