Could you please explain more what you're trying to do and what problems you're encountering.

You have a table with 2.1 billion rows in it?

I'm most interested in the wait type (it's hex), the last wait type and wait resource (if any)

If the CPU is increasing or static, if the reads are increasing or...

Checkpoints are internal operations. They won't show up in profiler

You can log the output of:

SELECT * from sys.dm_exec_requests where session_id < 50

Log it every few sec. You should see some...

Very odd. The optimiser has a limit on how much time it's allowed to spend considering a plan. It shouldn't be that long.

What do you see in sysprocesses for that...

It's very likely the checkpoint, which writes dirty pages to disk. The frequency that the checkpoint runs at depends on the frequency of changes to the database

It looks like you're...

Why? What volumes of data are we talking here? Why kind of queries? How fast...

No, not really.

You said before you want 1 row per departure airport, the one with the cheapest price. Now you say "Any which the departure date is greater than...

Can you go into more detail on exactly what is slow in your current app. Selects? Inserts?

With a properly designed table, well-written SQL and optimal indexes, 10 million rows shouldn't...

Are you sure it's the plan generation that's taking the time? If the data returned by the view is not in the data cache, it has to be retrieved from...

Should do.

Which of those rows is the query supposed to return?

http://www.sqlservercentral.com/Forums/Topic447796-4-1.aspx

Neither. Put all the data into 1 table with a date column

Perhaps start by reversing the string (REVERSE). From there it should be easy to get 2 delimiters into the string with CHARINDEX and it's optional StartPosition parameter.

IF EXISTS (SELECT 1 from master.dbo.sysobjects where name = 'Table1' and xtype = 'U')

BEGIN

-- do something here.

END

The index definitions please?

This is going to prevent index seeks on these two columns

AND ISNULL(C.col5,'')

AND ISNULL(TP.col5,'')

In addition, the kind of 'search-all' query that you have is quite well known...