• The writes could be due to a memory spill

    http://www.sqlpassion.at/blog/PermaLink,guid,2a24830a-5cf9-4438-96e9-af0a6d7372c3.aspx

    In terms of what else *could* (pure guesswork 🙂 ) be happening here:

    The "delay" may not even be in SQL Server, quite often the client app is slow at reading the data.

    As you have gazzilions of , presumabley, wide rows this could well be the case.

    An easy test is to run the query in SSMS but turn on the "discard results after execution option" , you will probably find a massive improvement 🙂

    Use the "Include client statistics" to quantify ( in bytes ) the result set size.

    Also this query is *wrong*. Why use option (RECOMPILE) AND OPTIMIZE for Uknown , doesent make sense?!?

    Optimize for unknown prevents usage of the varaiable value to build an execution plan but you are taking the hit of recompile anyway so why not use the value.



    Clear Sky SQL
    My Blog[/url]