question

If memory serves, the execution plan & procedure cache are cleared on a restart. If you are referring to the statistics (the physical object), that is NOT cleared. However, statistics about the table/index contained in the DMV's are also cleared. There are a number of things that occur when SQL Server starts up that could slow it down, such as the time it takes to rollback/rollforward from the transaction log if the transaction log is unusually large or having a cold cache. Does the database speed up after a bit from when the initial connections? One solution that I've heard about but never implemented myself is having a process to call all the related stored procedures before the server is open for client connections. This would force the server to warm up the cache so that the overhead of creating the query plan & cache are done before the client gets to the web site. I don't know how effective this method is, but it's one that I've seen. Are you sure the slowness is related to a cold cache? What have you investigated? Is it just this one database or do you have 500 other databases on the same system? Without knowing more about the situation or what you're seeing, it'll be difficult to determine if the slowness is related to a cold cache or something else. Hope this helps!!

Thanks John. I've also heard about the warm up idea of caching once the server restarts and before getting the client connections. This is just for to know and figure out what to expect when the server restarts. I do have multiple databases but not yet even in double digits. Only one of them is a big database, all of the rest are in MB's. I'm all about performance, so I tend to look in to ways I can optimize SQL and the server which would help serve users fast. Do you have any reference to MS technet article or someone else who has managed to research on this topic?