May 3, 2011 at 9:13 am
Ya, I've never had a video that was so big that I couldn't stream it. So I'm not too worried :hehe:.
May 3, 2011 at 9:16 am
Ninja's_RGR'us (5/3/2011)
Ya, I've never had a video that was so big that I couldn't stream it.
I hate you. Just saying... 😉
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 3, 2011 at 9:19 am
GilaMonster (5/3/2011)
Ninja's_RGR'us (5/3/2011)
Ya, I've never had a video that was so big that I couldn't stream it.I hate you. Just saying... 😉
Who do you think I was probing with the 20 MBPS :hehe::w00t::-D
May 3, 2011 at 9:35 am
GilaMonster (5/3/2011)
Ninja's_RGR'us (5/3/2011)
Ya, I've never had a video that was so big that I couldn't stream it.I hate you. Just saying... 😉
Actually now that I think of it I <almost> have to wait on youtube for those new full HD 1080 videos. You know when I open a couple of them at the same time.
:smooooth: :alien:
I really need a faster connection :-D.
May 3, 2011 at 9:38 am
GilaMonster (5/3/2011)
You can get far without buying anything (other than a good internet connection)
Amazing!! A real treasure! Thanks for sharing 🙂
May 3, 2011 at 9:43 am
GilaMonster (5/3/2011)
Ninja's_RGR'us (5/3/2011)
Ya, I've never had a video that was so big that I couldn't stream it.I hate you. Just saying... 😉
Haha - my phone probably streams faster than your connection. Just sayin' 😀
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 3, 2011 at 9:45 am
SQLRNNR (5/3/2011)
GilaMonster (5/3/2011)
Ninja's_RGR'us (5/3/2011)
Ya, I've never had a video that was so big that I couldn't stream it.I hate you. Just saying... 😉
Haha - my phone probably streams faster than your connection. Just sayin' 😀
my phone probably defenitely streams. And it's almost 4 years old.
May 5, 2011 at 2:07 am
Steve Jones - SSC Editor (5/3/2011)
There are a few things that will automatically go to disk with tempdb. After some checking, you cannot get away from spillage during queries. If a query is estimated to use 10MB of memory, and it uses 15, then that 5MB will spill to disk, no matter what. Doesn't matter if you have 4GB or 4TB of RAM.
Thanks for this Steve. I was beginning to get worried that no one could list a single item/thing guaranteed to hit tempdb regardless of memory.
And I had thought this thread had done its dash, so was pleasantly surprised to see it spring back to life. Admittedly most of those were a bit off topic, but hey, they were links to good info and I'm always happy to get two-for-one.
Thanks guys,
Steve.
May 5, 2011 at 2:12 am
Fal (5/5/2011)
Thanks for this Steve. I was beginning to get worried that no one could list a single item/thing guaranteed to hit tempdb regardless of memory.
Bear in mind that being in tempDB and being in memory are not mutually exclusive. Something can be in TempDB and still be entirely memory resident.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 5, 2011 at 3:20 am
On the original question, I suspect with most work profiles, you'd get better overall system performance from putting TempDB on physical disk and allocating the RAM you were going to use as a RAMDisk to the whole SQL Instance instead. I'd only consider it if I knew tempDB contention was a real bottleneck and did some realistic load testing that proved it was worthwhile. Also, it would create a difficult situation if you had unexpected tempDB growth.
MS did write a KB on it, but it's rather out of date now:
http://support.microsoft.com/kb/115050
There's also some more info here:
http://support.microsoft.com/kb/917047
The double RAM cache section is worth noting, as it's treated like any other disk, data is cached and written lazily even though it's all memory, so you're potentially wasting available memory resource.
May 5, 2011 at 10:41 am
GilaMonster (5/5/2011)
Fal (5/5/2011)
Thanks for this Steve. I was beginning to get worried that no one could list a single item/thing guaranteed to hit tempdb regardless of memory.Bear in mind that being in tempDB and being in memory are not mutually exclusive. Something can be in TempDB and still be entirely memory resident.
Very true. I've love to see an article or blog that shows this. It's one of those things tempdb misunderstandings
May 5, 2011 at 10:53 am
HowardW (5/5/2011)
On the original question, I suspect with most work profiles, you'd get better overall system performance from putting TempDB on physical disk and allocating the RAM you were going to use as a RAMDisk to the whole SQL Instance instead. I'd only consider it if I knew tempDB contention was a real bottleneck and did some realistic load testing that proved it was worthwhile. Also, it would create a difficult situation if you had unexpected tempDB growth.MS did write a KB on it, but it's rather out of date now:
http://support.microsoft.com/kb/115050
There's also some more info here:
http://support.microsoft.com/kb/917047
The double RAM cache section is worth noting, as it's treated like any other disk, data is cached and written lazily even though it's all memory, so you're potentially wasting available memory resource.
I see this talked about often, but not real testing being done. Now that we have lots of x64 systems, and the 2/4GB RAM limits don't apply, I wonder if this is still true? If I have a 16GB system, does it make more sense in workloads to put 2GB to tempdb? I guess it depends on the buffer pool size v tempdb load.
May 6, 2011 at 6:45 am
Steve Jones - SSC Editor (5/5/2011)
I see this talked about often, but not real testing being done. Now that we have lots of x64 systems, and the 2/4GB RAM limits don't apply, I wonder if this is still true? If I have a 16GB system, does it make more sense in workloads to put 2GB to tempdb? I guess it depends on the buffer pool size v tempdb load.
I'm sure there are some situations where it would help, but still think the odds are stacked against for most workloads.
SQL Server's already pretty well optimised to avoid most TempDB operations spilling to disk if there's available memory (much more so than in SQL 2000 days) and although it looks and feels like any other database, internally it often operates quite differently in terms of how it allocates data/logs in order to take advantage of the less stringent requirements for data security on failure or re-use of data after a session finishes with it.
Putting it in RAM means that you have a static allocation of memory that can only be used for one purpose and has to be sized for the maximum possible usage of TempDB in your environment (plus headroom) rather than dynamic according to usage.
Add to it that it still utilises the buffer pool as if it was a physical disk and it doesn't seem like you'd want to do it as a matter or course.
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply