Post reply

Profiler writes Question

  • February 15, 2011 at 1:51 am

    #233549

    Hi all,

    I came across a strange scenario yesterday while capturing performance data using profiler.

    While looking at my profiler results, I noticed that some select statements show high write values?

    My first thougt was that it might be using a temp table and the writes into the temptable might be causing this, but it's a straightforward select with a few joins.

    I also looked at the query plan and couldn't identify any reason why?

    Any of you out there have any input/suggestions?

    Thanks!


    What's this "backup strategy" everyone is on about?

  • February 15, 2011 at 1:57 am

    #1286902

    Is it spooling or sorting ?


    Clear Sky SQL
    My Blog[/url]

  • February 15, 2011 at 2:11 am

    #1286915

    Hi Dave,

    I don't see any spooling operations, there is an order by though...

    just FYI - Below are the physical and logical results from statistics profile to show the operations from the query plan...

    PhysicalOpLogicalOp

    NULLNULL

    Compute ScalarCompute Scalar

    Stream AggregateAggregate

    SortDistinct Sort

    FilterFilter

    Hash MatchRight Outer Join

    Index ScanIndex Scan

    Nested LoopsLeft Outer Join

    Hash MatchRight Outer Join

    Index ScanIndex Scan

    Hash MatchInner Join

    Nested LoopsInner Join

    Nested LoopsInner Join

    Clustered Index SeekClustered Index Seek

    Nested LoopsInner Join

    Nested LoopsInner Join

    Index SeekIndex Seek

    Clustered Index SeekClustered Index Seek

    Clustered Index SeekClustered Index Seek

    Clustered Index ScanClustered Index Scan

    Nested LoopsInner Join

    Index SeekIndex Seek

    Clustered Index SeekClustered Index Seek

    Index SeekIndex Seek


    What's this "backup strategy" everyone is on about?

  • February 15, 2011 at 2:45 am

    #1286929

    Sounds like the order by condition is causing a sort which in turn is causing the writes as the rows are being spooled to tempdb so that the entire set can be ordered.

    Do you see the same if you remove the ORDER BY ?


    Clear Sky SQL
    My Blog[/url]

  • February 15, 2011 at 4:30 am

    #1286960

    Thanks Dave,

    I'll definitely do the test without the order by, I also noticed that not all the instances of this query captured in profiler show writes... it might be that sometimes the optimiser create different exec plan depending on the parameters passed... which could be the source of the writes?

    I'm trying to "catch" this scenario - Thanks for the feedback, it definitely gave me a new angle to get to the bottom of this...


    What's this "backup strategy" everyone is on about?

  • February 15, 2011 at 6:52 am

    #1287024

    Hash match operators build a hash table, that can result in writes to tempdb. I also see a sort operation, again, writes to the tempdb. That's probably where that stuff is coming from.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • February 15, 2011 at 7:22 am

    #1287051

    Grant Fritchey (2/15/2011)

    Hash match operators build a hash table, that can result in writes to tempdb. I also see a sort operation, again, writes to the tempdb. That's probably where that stuff is coming from.

    Thanks for clarifying Grant


    What's this "backup strategy" everyone is on about?

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

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