SQL Trace - simple SELECT shows writes

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » SQL Trace - simple SELECT shows writes

SQL Trace - simple SELECT shows writes

Rate Topic

Display Mode

Topic Options

Author

Message

GordonLiddy

Posted Wednesday, August 29, 2012 8:59 AM

Valued Member

Group: General Forum Members
Last Login: Today @ 5:04 AM
Points: 50, Visits: 368

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

Post #1351680

patrickmcginnis59

Posted Wednesday, August 29, 2012 9:24 AM

SSC-Addicted

Group: General Forum Members
Last Login: Wednesday, January 16, 2013 4:23 PM
Points: 415, Visits: 2,333

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.

Post #1351705

Adi Cohn-120898

Posted Wednesday, August 29, 2012 9:34 AM

SSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 9:50 AM
Points: 2,019, Visits: 4,923

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/

Post #1351712

Gazareth

Posted Wednesday, August 29, 2012 10:16 AM

UDP Broadcaster

Group: General Forum Members
Last Login: Monday, May 20, 2013 1:53 AM
Points: 1,474, Visits: 2,342

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.

Post #1351753

GilaMonster

Posted Wednesday, August 29, 2012 10:21 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:11 PM
Points: 37,741, Visits: 30,020

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 2008, MVP
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

Post #1351757

GordonLiddy

Posted Thursday, August 30, 2012 1:09 AM

Valued Member

Group: General Forum Members
Last Login: Today @ 5:04 AM
Points: 50, Visits: 368

Sounds reasonable. Thank you everyone for your answers!

Sincerely,

Gord

Post #1352031

« Prev Topic | Next Topic »

Permissions