February 15, 2011 at 1:51 am
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!
February 15, 2011 at 1:57 am
Is it spooling or sorting ?
February 15, 2011 at 2:11 am
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
February 15, 2011 at 2:45 am
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 ?
February 15, 2011 at 4:30 am
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...
February 15, 2011 at 6:52 am
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
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply