<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2008
»
SQL Server Newbies
»
Worktables & Hash Tables
24 posts, Page 2 of 3
««
1
2
3
»»
Worktables & Hash Tables
Rate Topic
Display Mode
Topic Options
Author
Message
SQLSACT
SQLSACT
Posted Thursday, January 31, 2013 1:19 PM
Ten Centuries
Group: General Forum Members
Last Login: 2 days ago @ 7:48 AM
Points: 1,201,
Visits: 2,126
Thanks Grant
Definately need to do some more Research and Testing
Thanks
Post #1414308
SQLSACT
SQLSACT
Posted Wednesday, February 13, 2013 8:01 AM
Ten Centuries
Group: General Forum Members
Last Login: 2 days ago @ 7:48 AM
Points: 1,201,
Visits: 2,126
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
Grant Fritchey
Grant Fritchey
Posted Wednesday, February 13, 2013 8:09 AM
SSChampion
Group: General Forum Members
Last Login: Today @ 3:41 AM
Points: 13,383,
Visits: 25,189
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans
Product Evangelist for
Red Gate Software
Post #1419541
SQLSACT
SQLSACT
Posted Wednesday, February 13, 2013 11:26 AM
Ten Centuries
Group: General Forum Members
Last Login: 2 days ago @ 7:48 AM
Points: 1,201,
Visits: 2,126
What I'm struggling to see is the tempdb allocation when spill doesn't happen.
Thanks
Post #1419667
GilaMonster
GilaMonster
Posted Wednesday, February 13, 2013 12:25 PM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 3:18 PM
Points: 37,744,
Visits: 30,025
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
SQLSACT
SQLSACT
Posted Wednesday, February 13, 2013 11:28 PM
Ten Centuries
Group: General Forum Members
Last Login: 2 days ago @ 7:48 AM
Points: 1,201,
Visits: 2,126
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
GilaMonster
GilaMonster
Posted Wednesday, February 13, 2013 11:47 PM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 3:18 PM
Points: 37,744,
Visits: 30,025
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
SQLSACT
SQLSACT
Posted Thursday, February 14, 2013 12:17 AM
Ten Centuries
Group: General Forum Members
Last Login: 2 days ago @ 7:48 AM
Points: 1,201,
Visits: 2,126
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
GilaMonster
GilaMonster
Posted Thursday, February 14, 2013 1:40 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 3:18 PM
Points: 37,744,
Visits: 30,025
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
Grant Fritchey
Grant Fritchey
Posted Thursday, February 14, 2013 3:55 AM
SSChampion
Group: General Forum Members
Last Login: Today @ 3:41 AM
Points: 13,383,
Visits: 25,189
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans
Product Evangelist for
Red Gate Software
Post #1419939
« Prev Topic
|
Next Topic »
24 posts, Page 2 of 3
««
1
2
3
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
