Today I want to ask questions more than provide guidance. The cost of hardware has dropped to the point that I find myself with a server having more memory than my entire database. How about a 4:1 ratio of Ram to Database Data.
Now I start asking myself how to best take advantage of that additional memory? The traditional answer on a machine dedicated to SQL Server is to let SQL Server manage the resources. To this point, that has been an adequate response. But, sometimes I wish for a little more boost in processing.
In older versions of SQL Server you could pin TempDB to operate only in RAM rather than on disk, thus increasing performance. Today this is no longer an option unless you are using a RAM disk driver. Would it make sense to use 1/6th of my available memory to put TempDB on a RAM disk? Using a RAM Disk a segment of your ram is made available to the operating system as a hard drive. You can move TempDB to any mountable disk, so a RAM Disk works just fine. Some have used SSDs in this capacity.
My cache hit ratio is extremely high on my server. My table data and index pages are most often already in RAM. Still, an option available to me is to use the PINTABLE command forcing specific table(s) to be retained in memory and not paged out when RAM is needed. I suppose this could be the poor mans IN-MEMORY table substitute prior to SQL Server 2014. This isn’t really the same thing.
Using PINTABLE comes with concerns. If you have too many tables pinned in memory and RAM is needed for some other process you could actually decrease your performance. In my case, the memory available compared to the memory consumed is so much higher this is not really a risk.
All my other cache is not being swapped out for needed RAM. What other things might I do to take advantage of this additional capacity? Share your thoughts here online, or drop me a note for future Newsletter Editorials at btaylor@sswug.org.
Cheers,
Ben