InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 14:23:58 GMT20http://www.sqlservercentral.com/Forums/Topic1413573-1292-1.aspx[quote][b]Bhuvnesh (3/8/2013)[/b][hr][quote][b]Grant Fritchey (1/31/2013)[/b][hr]Get a copy of SQL Server 2008 Internals by Kalen Delaney. Then read it 100 times. You'll still be confused (as I am) but less so.[/quote] Grant, i have read this text around 10 times and it always put smile on my face So i decided this time to post something which often came in my mind when i was here .That if a master like you can get confuse then i shouldn't get hopelesss to open this book again and again. it actually motivates me :-).[/quote]Thanks. Happy to help.Fri, 08 Mar 2013 04:23:40 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic1413573-1292-1.aspx[quote][b]Grant Fritchey (1/31/2013)[/b][hr]Get a copy of SQL Server 2008 Internals by Kalen Delaney. Then read it 100 times. You'll still be confused (as I am) but less so.[/quote] Grant, i have read this text around 10 times and it always put smile on my face So i decided this time to post something which often came in my mind when i was here .That if a master like you can get confuse then i shouldn't get hopelesss to open this book again and again. it actually motivates me :-).Fri, 08 Mar 2013 03:33:13 GMTBhuvneshhttp://www.sqlservercentral.com/Forums/Topic1413573-1292-1.aspxHi GuysI know this post is getting old, I figured this response would help someone else as much as it did mehttp://blogs.msdn.com/b/craigfr/archive/2006/08/10/687630.aspxThanksFri, 08 Mar 2013 00:12:07 GMTSQLSACThttp://www.sqlservercentral.com/Forums/Topic1413573-1292-1.aspxThanks Gail and GrantThu, 14 Feb 2013 04:00:13 GMTSQLSACThttp://www.sqlservercentral.com/Forums/Topic1413573-1292-1.aspx[quote][b]SQLSACT (2/13/2013)[/b][hr][quote][b]GilaMonster (2/13/2013)[/b][hr]Why would you see a tempDB allocation when something's not spilling to TempDB?[/quote]From Grant:[quote]All temporary tables (hash/work tables, temp tables, table variables) are written to disk as well as memory, every time[/quote][quote]None of the temporary objects are memory only[/quote][/quote]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.Thu, 14 Feb 2013 03:55:25 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic1413573-1292-1.aspx[quote][b]SQLSACT (2/14/2013)[/b][hr]How does this process differ for Hash Joins?[/quote]Why don't you go and read the book that's been recommended several times, do the research and see for yourself?Thu, 14 Feb 2013 01:40:54 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1413573-1292-1.aspx[quote][b]GilaMonster (2/13/2013)[/b][hr]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.[/quote]Thanks[quote]Memory grants for sorts are, oddly enough, memory. If the process can't get enough memory, then the sort spills to TempDB[/quote]How does this process differ for Hash Joins?ThanksThu, 14 Feb 2013 00:17:32 GMTSQLSACThttp://www.sqlservercentral.com/Forums/Topic1413573-1292-1.aspxGrab 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.Wed, 13 Feb 2013 23:47:02 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1413573-1292-1.aspx[quote][b]GilaMonster (2/13/2013)[/b][hr]Why would you see a tempDB allocation when something's not spilling to TempDB?[/quote]From Grant:[quote]All temporary tables (hash/work tables, temp tables, table variables) are written to disk as well as memory, every time[/quote][quote]None of the temporary objects are memory only[/quote]Wed, 13 Feb 2013 23:28:57 GMTSQLSACThttp://www.sqlservercentral.com/Forums/Topic1413573-1292-1.aspxWhy would you see a tempDB allocation when something's not spilling to TempDB?Wed, 13 Feb 2013 12:25:18 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1413573-1292-1.aspxWhat I'm struggling to see is the tempdb allocation when spill doesn't happen.ThanksWed, 13 Feb 2013 11:26:30 GMTSQLSACThttp://www.sqlservercentral.com/Forums/Topic1413573-1292-1.aspxStats 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.Wed, 13 Feb 2013 08:09:00 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic1413573-1292-1.aspxHi GrantI know this post is getting old, I thought I'd just fill you in on my findingsI 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 tempbAlso, 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. ??ThanksWed, 13 Feb 2013 08:01:59 GMTSQLSACThttp://www.sqlservercentral.com/Forums/Topic1413573-1292-1.aspxThanks GrantDefinately need to do some more Research and TestingThanksThu, 31 Jan 2013 13:19:03 GMTSQLSACThttp://www.sqlservercentral.com/Forums/Topic1413573-1292-1.aspx[quote][b]SQLSACT (1/31/2013)[/b][hr]ThanksI understand the workspace memory area (Well, I hope I do). A query will request required memory and once the query has been granted that, if it needs to, it will request additional memory for processing a hash join for example, which might have to spill to disk it memory can't accomodate that additional memory. Correct?I'm not experiencing any particular issue, I'm just trying to understand the behaviour.Where I got thrown off a bit was here[quote]All temporary tables (hash/work tables, temp tables, table variables) are written to disk as well as memory, every time.[/quote]Thanks[/quote]Sorry, but they are. You can watch it occur in the allocations. None of the temporary objects are memory only. They all require some disk allocation. It's just that the majority of the storage is, usually, depending on the system, etc., in memory.Thu, 31 Jan 2013 08:06:14 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic1413573-1292-1.aspxThanksI understand the workspace memory area (Well, I hope I do). A query will request required memory and once the query has been granted that, if it needs to, it will request additional memory for processing a hash join for example, which might have to spill to disk it memory can't accomodate that additional memory. Correct?I'm not experiencing any particular issue, I'm just trying to understand the behaviour.Where I got thrown off a bit was here[quote]All temporary tables (hash/work tables, temp tables, table variables) are written to disk as well as memory, every time.[/quote]ThanksThu, 31 Jan 2013 08:02:37 GMTSQLSACThttp://www.sqlservercentral.com/Forums/Topic1413573-1292-1.aspxWell what's the major issue. You're asking about very low level behavior. It's not immediately applicable to most tuning situations. I mean, there is a specific area called workspace memory used for temporary allocation of objects within a query that is pulled from the buffer pool. How is that useful to you if you have a query creating a work table? Fix the query, add the index, modify the structure... Do what's necessary to get by. Does it really matter exactly where the allocation goes?If you need to look at allocations, start with sys.dm_os_memory_clerks. You can see what is being allocated to the different memory management allocations. You can look at the buffer pool through sys.dm_os_buffer_descriptors. These can help you see where the allocation is going. Memory allocation for a query is shown in the actual execution plan. You can also look at sys.dm_exec_query_memory_grants for execution queries. To see specifically where information is going, take a look at sys.dm_db_session_space_usage. That's going to show you where the memory allocations are going. As to precisely when the memory/disk spill occurs, I'm not sure. It's largely a question of how much memory do you have. Once there's not enough, everything goes to disk. You can kind of figure out if you're using disk or not by looking at the allocations available through the DMOs I listed above.But I'm still unclear where this gets you, except a more complete understanding of why a hash, sort, or work table actually does cause things to slow down.Thu, 31 Jan 2013 07:35:54 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic1413573-1292-1.aspxI've read your Execution Plans book and I've taken a lot from there.This particular area of worktables/hashtables is eluding me thoughThanks for your helpThu, 31 Jan 2013 06:50:46 GMTSQLSACThttp://www.sqlservercentral.com/Forums/Topic1413573-1292-1.aspxGet a copy of SQL Server 2008 Internals by Kalen Delaney. Then read it 100 times. You'll still be confused (as I am) but less so.Thu, 31 Jan 2013 06:44:57 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic1413573-1292-1.aspx[quote][b]Grant Fritchey (1/31/2013)[/b][hr][quote][b]SQLSACT (1/31/2013)[/b][hr]Thanks [quote]Basically correct. Although, it really only matters to the internals engine. For your purposes, both are dealt with the same way. According the to the Internals book, a hash table goes to what is called a work file, but that's treated in all ways the same as a work table. Further, when you have a hash join and you look at the I/O stats of the query, that hash is evidenced as something called a work table. So, I just treat the hash as a type of work table since the external evidence is exactly the same and MS labels it that way.[/quote]wrt to the memory/tempdb situationAre work/hash tables first created in memory and spilled to tempdb if necessary?Thanks[/quote]Yes & no. All temporary tables (hash/work tables, temp tables, table variables) are written to disk as well as memory, every time. But the primary work is done in memory, if it can. Otherwise it puts the excess on the place it reserved on the disk.[/quote]Thanks[quote] All temporary tables (hash/work tables, temp tables, table variables) are written to disk as well as memory, every time[/quote]Which area of work will go to memory and what will go to disk?[quote]But the primary work is done in memory[/quote]If memory cannot accomodate the primary work, is that [i]spilled[/i] to disk?I think I need to do some more research on this topic, still very confusedThu, 31 Jan 2013 06:36:28 GMTSQLSACThttp://www.sqlservercentral.com/Forums/Topic1413573-1292-1.aspx[quote][b]SQLSACT (1/31/2013)[/b][hr]Thanks [quote]Basically correct. Although, it really only matters to the internals engine. For your purposes, both are dealt with the same way. According the to the Internals book, a hash table goes to what is called a work file, but that's treated in all ways the same as a work table. Further, when you have a hash join and you look at the I/O stats of the query, that hash is evidenced as something called a work table. So, I just treat the hash as a type of work table since the external evidence is exactly the same and MS labels it that way.[/quote]wrt to the memory/tempdb situationAre work/hash tables first created in memory and spilled to tempdb if necessary?Thanks[/quote]Yes & no. All temporary tables (hash/work tables, temp tables, table variables) are written to disk as well as memory, every time. But the primary work is done in memory, if it can. Otherwise it puts the excess on the place it reserved on the disk.Thu, 31 Jan 2013 06:30:52 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic1413573-1292-1.aspxThanks [quote]Basically correct. Although, it really only matters to the internals engine. For your purposes, both are dealt with the same way. According the to the Internals book, a hash table goes to what is called a work file, but that's treated in all ways the same as a work table. Further, when you have a hash join and you look at the I/O stats of the query, that hash is evidenced as something called a work table. So, I just treat the hash as a type of work table since the external evidence is exactly the same and MS labels it that way.[/quote]wrt to the memory/tempdb situationAre work/hash tables first created in memory and spilled to tempdb if necessary?ThanksThu, 31 Jan 2013 06:13:48 GMTSQLSACThttp://www.sqlservercentral.com/Forums/Topic1413573-1292-1.aspxBasically correct. Although, it really only matters to the internals engine. For your purposes, both are dealt with the same way. According the to the Internals book, a hash table goes to what is called a work file, but that's treated in all ways the same as a work table. Further, when you have a hash join and you look at the I/O stats of the query, that hash is evidenced as something called a work table. So, I just treat the hash as a type of work table since the external evidence is exactly the same and MS labels it that way.Thu, 31 Jan 2013 06:04:51 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic1413573-1292-1.aspxHi AllAfter testing and researching - I want to make sure that my understanding is correct regarding Worktables & Hash Tables>Worktables & Hash Tables are 2 different things>Hash tables are only created when there is a Hash Join.>Worktables are created when immediate results need to be stored somewhere>Worktables & Hash Tables are both created in Memory first and spill to tempdb if necessary>A Merge join will not employ a worktable if both inputs are uniquePlease advise if I am on the right track here?ThanksWed, 30 Jan 2013 05:14:17 GMTSQLSACT