The most specific example I can give is when I am printing a batch job of about 200. The day I restart the server, this batch job is spooled lightning fast. As each day goes by, this same job begins to drag to the point where it affects other users who are attempting to access the database while the job is printing. At that point, I simply restart the server and all is well.
It seems I recall seeing a post at one point that mentioned something about why restarting can have such a profound impact.
Next time try running DBCC FREEPROCCACHE to see if the query plan is getting bad over time. This will, however clear all of the procedure cache, and is not at all suggested on a high traffic server. If this is a high traffic server, run sp_recompile on all of the tables underlying the query you are running.
I did a little reading on sp_recompile and just want to confirm that when I shutdown the server, a recompile is happening, which in turn, speeds the database significantly. Is this the only thing I need to do to achieve the same performance gains of restarting the server? Also, by using sp_recompile on all of my tables, will this improve performance over the entire database (like a restart currently does)? Thanks for the help.
When you reboot, the procedure cache is cleared. It is memory resident only. As for whether you will realize any performance gains, well, that depends on the actual problem. This one is easy to rule out, so I start here.
Okay, so sp_recompile is better way of clearing the procedure cache while there is heavy traffic. How often should I run this procedure? I currently print the batch once a day. Is there any harm in running the procedure once a day on every table? Is this a potential permanent solution to my problem? Also, is there an easy way to have the procedure run on all tables at once or do I have to manually specify each table? Again, thank you for the help. I really appreciate it.
Whoa, dragon! Does clearing out the query plan from the procedure cache help? If not, then you do not need to recompile anything. In fact, you should not, as it will cause all queries to generate new query plans (which utilizes extra CPU cycles). That could land you in a worse place than where you are now. If this does end up helping the problem, I would advise the least invasive fix, which will very likely end up being one single table. It will be a mater of some experimentation to figure out which table that will be, but likely candidates are often tables that are deleted and refilled on some cycle.