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 ««123»»

Worktables & Hash Tables Expand / Collapse
Author
Message
Posted Thursday, January 31, 2013 1:19 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 12:03 AM
Points: 1,380, Visits: 2,708
Thanks Grant

Definately need to do some more Research and Testing

Thanks
Post #1414308
Posted Wednesday, February 13, 2013 8:01 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 12:03 AM
Points: 1,380, Visits: 2,708
Hi Grant

I know this post is getting old, I thought I'd just fill you in on my findings

I watched a video (from SQL.Bits) called "Advanced SQL Server 2008 troubleshooting by Klaus Aschenbrenner"

I noted something interesting, it seems that spill to disk happens when statistics are out of date.
>> SQL requests a memory grant of 10kb. This is based on 20 rows
>> SQL gets the memory grant but realizes that there's actually 200 rows instead of 20
>> SQL can't request the grant to be increased
>> Spill to tempb

Also, I've done some testing on a table with 30mil rows. When doing a sort 5000 rows, I see the memory allocation happening but I don't see any tempdb allocations (I'm using sys.dm_db_session_space_usage and sys.dm_db_task_space_usage
)

When I do a sort of 10mil rows, I see the memory allocations as well as tempdb allocations as well as a sort warning in profiler.

When using sys.dm_exec_memory_grants - If the ideal_memory is more that the granted memory. I think this is where the spill is happening. ??


Thanks

Post #1419539
Posted Wednesday, February 13, 2013 8:09 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:22 AM
Points: 14,205, Visits: 28,534
Stats being out of date like that is only one possible cause of a spill, but yeah, that could be it. And yes, what you're seeing is SQL Server wants X for memory, X isn't available. So it does a spill to disk for the amount of memory that it has to in order to satisfy the processing needs of the query. That's pretty much it in a nutshell.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of:
SQL Server Query Performance Tuning
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1419541
Posted Wednesday, February 13, 2013 11:26 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 12:03 AM
Points: 1,380, Visits: 2,708
What I'm struggling to see is the tempdb allocation when spill doesn't happen.

Thanks
Post #1419667
Posted Wednesday, February 13, 2013 12:25 PM


SSC-Forever

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

Group: General Forum Members
Last Login: Yesterday @ 8:36 AM
Points: 40,615, Visits: 37,080
Why would you see a tempDB allocation when something's not spilling to TempDB?


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 #1419693
Posted Wednesday, February 13, 2013 11:28 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 12:03 AM
Points: 1,380, Visits: 2,708
GilaMonster (2/13/2013)
Why would you see a tempDB allocation when something's not spilling to TempDB?


From Grant:
All temporary tables (hash/work tables, temp tables, table variables) are written to disk as well as memory, every time


None of the temporary objects are memory only
Post #1419821
Posted Wednesday, February 13, 2013 11:47 PM


SSC-Forever

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

Group: General Forum Members
Last Login: Yesterday @ 8:36 AM
Points: 40,615, Visits: 37,080
Grab Kalen's book, Adam Machanic's memory grants video and go over them.

Memory grants for sorts are, oddly enough, memory. If the process can't get enough memory, then the sort spills to TempDB. It would be decidedly pointless to request memory, get enough memory for the sort and still write to TempDB.

Oh, and spills are not the same as work tables, temp tables or table variables.



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 #1419826
Posted Thursday, February 14, 2013 12:17 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 12:03 AM
Points: 1,380, Visits: 2,708
GilaMonster (2/13/2013)
Grab Kalen's book, Adam Machanic's memory grants video and go over them.

Memory grants for sorts are, oddly enough, memory. If the process can't get enough memory, then the sort spills to TempDB. It would be decidedly pointless to request memory, get enough memory for the sort and still write to TempDB.

Oh, and spills are not the same as work tables, temp tables or table variables.


Thanks

Memory grants for sorts are, oddly enough, memory. If the process can't get enough memory, then the sort spills to TempDB


How does this process differ for Hash Joins?

Thanks

Post #1419836
Posted Thursday, February 14, 2013 1:40 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: Yesterday @ 8:36 AM
Points: 40,615, Visits: 37,080
SQLSACT (2/14/2013)
How does this process differ for Hash Joins?


Why don't you go and read the book that's been recommended several times, do the research and see for yourself?



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 #1419865
Posted Thursday, February 14, 2013 3:55 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:22 AM
Points: 14,205, Visits: 28,534
SQLSACT (2/13/2013)
GilaMonster (2/13/2013)
Why would you see a tempDB allocation when something's not spilling to TempDB?


From Grant:
All temporary tables (hash/work tables, temp tables, table variables) are written to disk as well as memory, every time


None of the temporary objects are memory only


You're conflating multiple different things into one. Sorts are different. You'll note in the quote you threw out there, I don't say sorts.


----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of:
SQL Server Query Performance Tuning
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1419939
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse