SQL Trace - simple SELECT shows writes

Question

SQL Trace - simple SELECT shows writes

GordonLiddy

Ten Centuries

Points: 1047
More actions
August 29, 2012 at 8:59 am

#393044

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

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

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

patrickmcginnis59 SSCertifiable Points: 6483 More actions · Answer 1

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.

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 2

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/

Gazareth One Orange Chip Points: 27737 More actions · Answer 3

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.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

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

GordonLiddy Ten Centuries Points: 1047 More actions · Answer 5

Sounds reasonable. Thank you everyone for your answers!

Sincerely,

Gord