tempdb in RAM

When you said noticeable delay, did you mean one second or was it worse?

Eventually, for this to work in a high availability production environment, clustering needs to be able to support local storage (since using tempdb the way you are would not work using shared storage). There is a similar request on Connect that I was already planning to cover in my next digest:

https://connect.microsoft.com/SQLServer/feedback/details/532759

Simon Sabin has asked for better usability in the SQL Server Agent log viewer: specifically, to allow

I have an elderly lap top I use for presentations, so uses IDE drive, I replaced this with a SSD and split my databases out to an external 5.4k usb disk for the data drive - this took a previous 45 min query down to 6 mins, sadly my laptop doesn't support more than 1GB ram but I will look into this on my other laptop and my workstation - very interesting, thanks.

I wrote about this a while back (http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/08/24/958.aspx); 2006 - wow - it was a while back.

Be interested if you can rerun some of the tests/check the conclusions.

I found tempdb in RAM can dramatically improve performance in certain situations; there is also a get out clause when using tempdb - set no autogrow on the file in ram; but have other files on disk that can grow (and shrink) - its "a way" but not ideal

I wouldn't worry about the page file and burning out a modern SSD. If you are running windows 7 and get a drive that supports trim I doubt you have any issues at all. SSD's don't "fail" exactly when they reach their write cycle limit. You can still read from it just not write any new data to a cell that has been flagged. If you have a drive that fails all together it is more than likely some kind of manufacturing defect, firmware issue or a failure outside the actual NVRam chips. I've been running SSD's in my laptop for over a year now and have several in my machines at home. Not one has failed due to to much write activity. I have had failures though, they aren't bullet proof!

Re: Clustering.

Not sure who wrote it or when but this article is interesting:

http://www.superspeed.com/servers/ms-sql-performance.php#5

Anyone have any comments on that?

Rich

Tempdb in RAM? How about an entire database?

Tempdb in RAM? How about your entire server?

Sounds like TimesTen http://en.wikipedia.org/wiki/TimesTen

We've recently implemented this in a production environment.  Check out http://spivey24.blogspot.com/2011/05/sql-tempdb-in-ram-drive.html

We did some testing with an SSD and we found that for some queries improvment is really important.  For example query execution may fall from 20 minutes to 3 minutes. With tempdb in RAM you may expect for such query greater improvments, since this is faster than a SSD.  With multi-core processor, CPU may not be an issue. But don't expect to have this improvement everywhere.  Some queries don't use tempdb, and this is not all T-SQL code that use temp tables.