People familiar with SQL Server know that the database engine caches data pages in memory for faster access to queries. There are a series of algorithms governing their life expectancy. This allows the query processor to fulfill a wide range of requests by using frequently accessed pages. This usually works great for simple queries but when we get to more complex requests, the performance gain diminishes quickly. This is caused before the pages have to be reprocessed to answer the needs of a particular query that might join tables, filter data, perform calculations, etc. While I was in TechEd a few years ago, I had a discussion with one of the program manager on SQL Server where I proposed the possible caching of query results.
The database engine could cache the outcome of queries. SQL Server could take care of tracking which result sets should be invalidated by tracking the lineage of the result set. This means that if a page of data is modified, the result set which are dependent on it should get invalidated and the memory freed. Since developers try to minimize the amount of data return to the application or reports, we could expect huge gains in performance for the users. Scenarios such as BI would greatly benefit from this, as the dashboards and reports query content could be delivered instantly without the need to resolve complex joins and aggregation; a little bit in the way Analysis Services performs aggregations and stores them in the cube. The persistence of the results could also be based on criteria similar to the one used by regular data pages. In certain scenarios where the data is static, those results could even be persisted transparently to disk as the last stage before it gets deleted completely from SQL Server’s cache.
Another advantage this might have, is that the DBMS is the closest to the data and is aware of the changes happening on it. If you combine this with traditional caching techniques present in applications with a notification mechanism from the database engine, you could keep the application’s cache closely synchronized, without potentially any polling necessary. If we take this one step further, we could say that once a certain result set gets invalidated by an updated to its underlying data, it could get reconstructed based on database policies and a notifications could be sent to the clients asking them to refresh a certain result sets. Since the result sets are based on the outcome of a certain query, the query handle in the plan cache as its hook to a certain result sets, this way, the DBMS only has to figure what query we’re trying to execute and deliver the result back to the client.
Using this kind of functionality, it would be fairly easy to decouple this functionality from the core DBMS to have it hosted on a separate server dedicated to the delivery of those cached result sets. The job of the core DBMS could revolve more around keeping the cache synched those “cache servers” and maintaining data integrity on persistent storage. If data pages could also be propagated from the core DBMS to the cache servers, each server could rebuild their result sets itself, therefore distributing the load accross potentially multiple servers. Since only the pages changed would be propagated from the core to the cache servers, the traffic and load should be minimal. We could take this one step further and only propagate the data pages to only servers with dependencies on that particular page.
I was hoping to see this in SQL Server 2008, but I guess it might be coming to SQL Server vNext! 🙂