Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Trace - simple SELECT shows writes Expand / Collapse
Author
Message
Posted Wednesday, August 29, 2012 8:59 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, June 26, 2014 7:19 AM
Points: 71, Visits: 489
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
Posted Wednesday, August 29, 2012 9:24 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-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
Posted Wednesday, August 29, 2012 9:34 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 5:28 AM
Points: 2,114, Visits: 5,502
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
Posted Wednesday, August 29, 2012 10:16 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 4:41 PM
Points: 2,013, Visits: 3,448
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
Posted Wednesday, August 29, 2012 10:21 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:32 AM
Points: 40,205, Visits: 36,609
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
Posted Thursday, August 30, 2012 1:09 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, June 26, 2014 7:19 AM
Points: 71, Visits: 489
Sounds reasonable. Thank you everyone for your answers!

Sincerely,

Gord
Post #1352031
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse