Solved

SQL Server weird behavior; DBCC FLUSHPROCINDB temporarily resolves issues

Posted on 2009-04-07
2
867 Views
Last Modified: 2012-05-06
Experts, I have an interesting problem that has no easy solution.  Furthermore I do not know the exact problem, just the symptoms and what has temporarily resolved it:

Approximately every 2-3 hours our database will run excessively slow.
-every call to the database is through stored procedures.
-web service calls (hitting the DB) that usually take 1-2 seconds now time out (180 seconds)
-windows services that poll the DB now time out

When we run the command 'DBCC FLUSHPROCINDB...' to clear the execution plans the database immediately speeds up and we experience no more time outs.  However, 2-3 hours later the database slows again and the time outs occur.

My question to you experts, is what steps do I take to find the specific problem? Running 'DBCC FLUSHPROCINDB(...)' every 2-3 hours is not acceptable as it is not resolving the problem.  How can I further diagnose and pinpoint the culprit?  Could one bad stored procedure execution plan slow down an entire database?  Anyone experience something similar with regards to 'DBCC FLUSHPROCINDB(...)' temporarily "fixing" the database?


0
Comment
Question by:thecoon
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 32

Accepted Solution

by:
bhess1 earned 500 total points
ID: 24093137
The problem is almost certainly in the way the applications work with queries, or stored procedures with RECOMPILE flags for each call.  What is happening is a side effect of the fact that SQL Server just continues to grow the procedure cache .... and grow, and grow, and grow, until you run out of available memory, at which point... it continues to grow, leading to disk swapping of memory that just slows your system to a crawl.

Your quick workaround is to run the DBCC command on a schedule, every 90 minutes say, to keep things running.  Your long term goal is to revise code to make more reuse of the same plans
0
 
LVL 3

Author Comment

by:thecoon
ID: 24101701
bhess1, all of the queries through applications are done via stored procedures without RECOMPILE flags.  The only thing I can think of that might make the procedure cache grow is we use dynamic SQL in several stored procedures that are called infrequently (procedure is called ~3,000 times in a day, but we have stored procedures that are called 180,000 times a day).

Do you have any good articles on procedure cache, for what you are talking about, that could help me?
0

Featured Post

Increase Agility with Enabled Toolchains

Connect your existing build, deployment, management, monitoring, and collaboration platforms. From Puppet to Chef, HipChat to Slack, ServiceNow to JIRA, Splunk to New Relic and beyond, hand off data between systems to engage the right people.

Connect with xMatters.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question