You need to optimize your SQL database performance in SQL Server, and have discovered a large amount of the time to produce results is during disk access. You have plenty of memory on the machine, and want to take advantage of it. What options are available to you?
Without doing anything SQL Server will take advantage of available memory to cache access to disk stored tables. In fact, the work you do in tables is performed in cache, and submitted to the disk as a background task. So, any server benefits from having adequate cache for work in process, etc.
Another technique is to use inline views or Common Table Expressions (CTE) to help simplify complicated queries. Using these virtual tools you can break up complicated queries into smaller more efficient steps.
Memory table variables are often used for the same purpose. The difference is the scope of the data. With a CTE or inline view, the results are only available on the next statement that uses it. With a Table variable, the data lives for the length of the currently executing batch. If you need a temporary set that lasts longer, # temp tables live for the length of your database connection.
If you need the ultimate speed provided by a temporary object, and you want it to remain indefinitely, then a Memory Table may be your solution, a feature recently released in SQL Server. These tables work like a permanent table stored in your database. The difference is that the data is persisted directly in RAM. This is similar to having a RAM disk.
You can implement a similar behavior for a table by Pinning the table in cache.
Do you have any recommendation regarding the use of these in memory query optimization tools? Do you have any favorites? Do you have any advice for how and when to use one over the other? Share your advice with your comment here or by email to btaylor@sswug.org
Cheers,
Ben