We have upgraded our SQL Server instance with buckets of RAM, so it can now much more effectively cache . The issue is that it takes time (days) to populate the cache. As far as I understand this is something internally managed by the engine, based on user queries. Is there value in pre-empting this process by running a series of select * statements on big / frequently used tables to cache all this data on service start up? Any particularly clever way to do it?
There isn't normally any added value in doing this: you may cache data that isn't needed that requires eviction when your real load occurs.
How much time do you think this will save too? Have you done some measurements to see if running a SELECT * on some table improves response after server start up?