Boris Pazin (8/16/2013)
wolfkillj (8/16/2013)
Wait just a sec - the OP asked about using DBCC FREEPROCCACHE but accepted Gail's and Grant's answers that apply to the buffer pool, so I'm confused. Boris, were you asking about clearing the plan cache or clearing the buffer pool?Well, I am not quite sure what I am asking. 🙂
I am programmer and not so familiar with SQL Server setup, but now I have to administer one SQL Server and trying my best.
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.
wolfkillj (8/16/2013)
The plan cache holds execution plans that can be reused so that a new execution plan doesn't have to be created each time the same T-SQL is executed. Plans stay in cache until SQL Server needs space there and flushes plans based on age and/or amount of use (not sure exactly what criteria it uses at the moment - need to do some Googling on that) or until someone executes a command or stored proc that clears either the whole cache or a particular plan. Clearing the entire cache can cause a a sudden, temporary decrease in query performance as SQL Server creates new execution plans for every query, but that performance hit only lasts until the cache is populated and the new execution plans can be reused. There's generally no reason to totally clear the plan cache - sometimes, it may be necessary to clear a single plan so that a better plan can be compiled the next time the query runs, but this should be done only after a thorough investigation of performance problems.The buffer pool holds data pages or index pages that have been read from disk in memory. When SQL Server needs a data or index page to execute a query, it looks for the page in the buffer pool first; if it can't find it there, it reads the page from disk and stores it in the buffer pool. Pages stay in the buffer pool until SQL Server needs space for additional pages. The logical read of a page from memory is much faster than the physical read of the page from disk. Keeping pages in memory as long as possible increases the chance that pages will be there when requested, providing a performance gain, especially for queries that access the same pages over and over. There's generally no reason to clear the buffer pool, either - as Gail and Grant said, SQL Server manages it well on its own.
So, Boris, the overnight jobs on your server may cause some slight performance degradation if the execution plans force the plans for the web service queries out of the plan cache (which will be remedied when a new execution plan is compiled the next time the web service queries run) or if the overnight jobs force pages used by the web service queries out of the buffer pool (which will also be remedied when SQL Server reads the pages from disk into the buffer the next time the web service queries run). The temporary performance hit often is little more than a hiccup in well-designed databases execution efficient queries. It sounds like it didn't disrupt your processes very much at all - evidence the SQL Server generally knows its business very well!
Hmm, not so sure now. Jobs are not same every night. One night job was really big and after it is done server was on CPU 100%.
I don't know if this was cause and effect, or just a coincidence. Maybe CPU 100% was caused by something else, or website worked slowly because of messed cache.
Anyway, after running of DBCC FREEPROCCACHE, CPU was fine again. :ermm:
Hi Boris,
DBCC FREEPROCCACHE clears the plan cache, which means that a new execution plan must be compiled for every new query run in that database instance. The status of the plan cache usually shouldn't affect CPU usage by any noticeable amount, unless the plan cache has been cleared or SQL Server is otherwise required to compile a lot of execution plans. That hit should be pretty short, though. Once plans are cached, subsequent queries will reuse them, reducing the load on the server resources.
If your CPU is being pegged at 100% for any length of time and that's causing problems, you'll need to find the culprit. Unfortunately, that's not always easy. The queries running on the server could be poorly written - the performance difference between a well-written query and a poorly written one can be staggering. There are different ways to write queries well and different ways to write queries poorly, and what works well in one situation may work poorly in another. The databases may lack indexes on certain tables that could boost the performance of the queries, but poorly conceived indexes can hurt performance rather than help. In many (maybe most) cases, performance tuning the database will alleviate pressure on server resources, but sometimes the sheer load on the server can be more than the hardware can handle.
If you find that you're dealing with performance problems often, I suggest you get Grant Fritchey's book [urlhttp://www.amazon.com/Server-2012-Query-Performance-Tuning/dp/1430242035]SQL Server 2012 Query Performance Tuning[/url] (there are earlier editions for earlier versions of SQL Server, too).
You may be feeling overwhelmed - that's natural when you've been given responsibility for SQL Server with no preparation. The SQL Server Central forums are a great place to get information and advice, and two of the three people who have replied to this thread are leading lights in the SQL Server community (yours truly being the odd man out!) . Lots of us got here the same way you did - we found ourselves thrown into the water and had to learn to swim.
Regards,
Jason
Jason Wolfkill