SQL Trace - simple SELECT shows writes

  • Hi there!

    As the subject says, when I read a trace file and the SQL:BatchCompleted event, the Writes column contains a value, when the query is only a SELECT statement. It has four JOINS, a few values in the WHERE clause, and finally an ORDER BY. There are no SELECT INTO or INSERT INTO present in the query.

    BOL says this about the Writes column:

    "The number of physical disk write operations that are performed by the server on behalf of the event."

    Are there any internal operations happening on "behalf of the event", or am I missing something here, when reading and trying to interpret the trace file? Been trying to find the answer myself, using Dr.Google, with no luck ...

    Thankful for an answer!

    Sincerely,

    Gord

  • I'm guessing that your query made use of temp tables during its execution. That will generate some writes. I once filled up tempdb by executing a select top n * order by <some columns>. If your query is using a temp table, its going to write to it.

  • One more option is that you had a multi-line function that explicitly used temporary tables in its code.

    Adi

    --------------------------------------------------------------
    To know how to ask questions and increase the chances of getting asnwers:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Prime suspect for me would be the order by clause - as it acts after the main execution, on the result set which I guess has to be stored somewhere whilst it's being operated on.

  • Hash joins use a work table that's in tempDB. Both hashes and sorts can spill to tempDB as well.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Sounds reasonable. Thank you everyone for your answers!

    Sincerely,

    Gord

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply