• Boris Pazin (8/16/2013)


    In short, there are nightly jobs which contains a lot of SQL code. Those queries are not used on website, visitors use other queries. I am worrying that after job is done, cache is not optimized for running website queries, because jobs queries on some way took some part in cache and thus there is less space for website queries.

    As explained by Gail, Steve and Wolfkillj, it does not really work that way. The plans for the web queries will remain in cache, as long as there is space for them.

    However... If the jobs runs the same queries as the web app, but with different parameters, it may be different. In this case you may end up with plans that are not optimal for the web application. Or it could be the other way round, the job runs with the plans for the web queries.

    This is because of something known as "parameter sniffing": SQL Server looks at the parameter values to determine the best query plan. If the parameter changes, this can backfire. Most likely, if you saw a benefit on your system with DBCC FREERPOCCACHE, you had a problem related to parameter sniffing - but if that is due to the job or not far from certain. And if may not be due to the queries of the job, but it may be the fact that the jobs adds a lot of rows to some tables and so on.

    Using FREEPROCCACHE to resolve such a problem is a bit of a sledgehammer. A better approach, in my opinion, would be to track down the problematic queries.

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]