Procedure Cache Hit Rate Is Low - Need Advice
Mar 18, 2008
I have a 32 bit SQL 2005 EE clustered installation with 10GB of physical memory and AWE enabled. Our monitoring tool, Spotlight, is reporting the Procedure Cache to be 384MB and a Hit Rate of 75% on a fairly regular basis. Sometimes the Procedure Cache increases to 495MB and a Hit Rate of 82%.
(1) With 2005 can the Procedure Cache be increased?
(2) What is the max size of Procedure Cache?
(3) How do I increase the Hit Rate to a higher percentage?
I do not encounter the issue on any other SQL Server installation, however this is our only cluster.
DBCC PROCCACHE
num proc buffs = 64889
num proc buffs used = 1135
num proc buffs = 1135
active proc cache size = 2896
proc cache used = 364
proc cache active = 364
Thanks, Dave
View 1 Replies
ADVERTISEMENT
Feb 9, 2007
I have installed a SQL Server diagnose tool for evaluation. It prompts and warns me that "Procedure Cache hit rate is for example 15%. Its help indicates:
The Procedure Cache Hit Rate alarm is raised when the ratio between the number of times SQL Server looks for a plan in the procedure cache and the number of times it does not find a required plan in the procedure cache falls below a threshold.
A low procedure cache hit rate indicates that SQL Server is finding fewer of the query execution plans it needs already in memory and therefore has to perform more compiles. These extra compilations will degrade SQL Server performance by causing extra CPU load.
What can I do to increase the rate?
Canada DBA
View 2 Replies
View Related
May 31, 2007
Is there a way to drop clean buffers at the database level instead of the server/instance level like the undocumented €śDBCC FLUSHPROCINDB (@dbid)€??
Is there a workaround for €śdbo€? to be able to flush procedure and data cache without being elevated to €śsysadmin€? server role?
PS: I am aware of the sp_recompile option that can be used to invalidate cached execution plans.
Thx.
View 1 Replies
View Related
Sep 12, 2014
Is there anyway to clear the procedure cache of a single procedure?
View 9 Replies
View Related
Nov 1, 1999
I know this might be a dumb one, but what the heck. My new 7.0 server's procedure cache
stays at 100%. After researching this looks like what I want. Nay response appreciated.
View 1 Replies
View Related
Jul 28, 1999
Ours is a MSSQL Server Client server application with very minimal usage of Store procedures.
The proc cache is configured at 5%.I execute "dbcc proccache" to keep track of the proc cache.
I have seen that the "proc cache size" reduces to a very small a amount when there is peak usage.
It starts at 42,000 and comes down to 400, though it is always greater than "proc cache used".
I am worried if this causes crashes.
Please advise why this happens and solutions if any.
Thanks in advance,
Ramakrishna seelam.
View 1 Replies
View Related
Feb 11, 1999
Hello folks!
We are going to install SQL server 6.5 with MSMQ.
And set 128M memory for SQL Server.
What % Procedure cach do you recomend to set?
Thanks,
Alona
View 1 Replies
View Related
Apr 26, 2000
Hi Everybody!
In the application I have, database interaction is mainly through stored procedures.
Currently the setting for procedure cache is 30%. Does anyone have experience with having had changed this and effect on performance?
Thanks!
- Manoj
View 1 Replies
View Related
Jun 1, 2001
Is any way we can tell sql server to keep specific (long runing) query in procedure Cache.
I already tried to do this by creating job (run every 1 hr from 8 am to 6 pm)
but is not enough
Thanks
View 1 Replies
View Related
Jul 15, 2002
Is there a way to increase the size of the procedure cache. Or is it only a auto configuring option.
I have 2gb of memory, and when I check the size of the procedure cache it is just 10mb. I would like to increase this to around 50mb. Not sure if there is an setting to do this. Had a look on BOL could not find anything.
thanks
Jane
View 1 Replies
View Related
Jun 20, 2014
I'm putting together some monitor scripts, have buffer cache ratio etc etc but struggling to get an accurate script for the current procedure cache hit ratio...
View 1 Replies
View Related
Jul 23, 2005
Using SQL Server 2000. When does SQL flush or clear the procedurecache? I am dynamically creating and dropping stored procedures (SP).Does SQL clear the cache for the SP that has been dropped? If not,when the SP is recreated (with the same name), does SQL use theexecution plan from cache?Thank you in advance.Jack
View 2 Replies
View Related
Apr 28, 2008
My server (SQL 2005 SP2) typically runs with a procedure cache usage of about 92% or higher... lately it seems like at some point in time during the day it just drops to anywhere between 50% and 65%... with this comes horrible server performance and many snowball effects. If I clear the procedure cache it will go up only about 10% for a minute or two. The only way I can get it to recover completely seems to be restarting the SQL service. Then it will be fine till the next incident. The database is a read only (not set to read only but no updates other than replication). and the same SPs are run over and over and over throughout the day. also did notice that the compiles of the SPs goes up drastically at this point also. not sure if this is part of the cause or part of the effect.
CPU is normal. response from anything (even sp_who) is slow.
i do not understand the way procedure cache works completely so I thought I would ask for some direction.
Any ideas where to look or where to start???
Any thing I can do to catch this when it happens would be great.
thanks a head of time.
View 34 Replies
View Related
Dec 27, 2007
Im getting this error when trying to set up a cache dependency...are there any special permissions etc?From CS:SqlCacheDependency dep = new SqlCacheDependency("MySite-Cache", "Products");Cache.Insert("Products", de.GetAllProductsList(), dep); From connectionStrings.config:<add name="SiteDB" connectionString="Data Source=localhost,[port]SQLEXPRESS;Integrated Security=true;User Instance=true; AttachDBFileName=|DataDirectory|ASPNETDB.MDF" providerName="System.Data.SqlClient" />Also tried this using my machinename<add name="SiteDB" connectionString="Data
Source=<machinename>,[port]SQLEXPRESS;Integrated Security=true;User
Instance=true; AttachDBFileName=|DataDirectory|ASPNETDB.MDF"
providerName="System.Data.SqlClient" /> From web.config: <caching> <sqlCacheDependency enabled="true" pollTime="10000"> <databases> <add name="MySite-Cache" connectionStringName="SiteDB" pollTime="2000"/> </databases> </sqlCacheDependency> </caching> EDIT: So making progress I can't seem to get the table registered for cache dependency:The sample i have says"aspnet_regsql.exe -E -S .SqlExpress -d aspnetdb -t Customers -et"and the command line response is "Enabling the table for SQL cache dependency..An error has happened. Details of the exception:The table 'Customers' cannot be found in the database."Where does this "Customers" table come from? There is obviously not an application specific "Customers" table in aspnetdb I'm confused probably more by the example than anything....
View 3 Replies
View Related
May 30, 2008
from BOL:
"
Restoring a database clears the plan cache for the instance of SQL Server.
"
i'm seeing this behavior after log restores as well.
what's the reason the whole procedure cache is flushed?
thanks.
View 2 Replies
View Related
Dec 19, 2005
I was wondering if SQL Cache Dependency would be in fact invalidated if:
1. it was created based on a procedure type command.
2. if the select statement retrieves the data from multiple database tables
Any help would be more appreciated. I am stuck with the fact that none of the data bases on sql dependency is invalidated. I spent literally hours to understand what i am doing incorrectly.
Thanks
View 1 Replies
View Related
Dec 6, 2000
We see the following message in our error log.
WARNING: Clearing procedure cache to free contiguous memory.
It is accomonpanied by fairly intensive CPU activity.
We get this roughly once per working day.
Anyone have any idea why, and what we can do to stop this?
Regads,
Jim Plant
View 2 Replies
View Related
Jun 15, 2015
I want to analyze procedure cache, to find inefficient plans and parameter issues.
I do it trow DMV But my requests to DMV are very slow and demand resources because procedure cache is about several GB Actually I dont need on-line analysis.
Is it possible to have fast snapshot of procedure cache?
View 0 Replies
View Related
Jul 20, 2007
How do most people handle database searches for things like a product database? What I mean by that is your typical product table may look like:
ProductIDProductTitleProductDescriptionProductCategoryIDEtc...
Assuming you have a Full Text Catalog set up on the ProductDescription field, would you use Dynamic Sql or a Stored Procedure?
I'm wrestling with this because I haven't found a good way to either parse a parameter in SQL to make a stored procedure work with the same flexibility as Dynamic TSQL, and I would prefer not to have any direct access to the table from the application.
My other option (which to me isn't a great one) is to have a fairly large number of optional "keyword" parameters in the stored procedure and then parse them on the application side...this is less than favorable in that in theory someone could pass more keywords than I've allotted in my stored procedure.
How would you approach this challenge?
Thanks in advance.
Ryan
View 2 Replies
View Related
Nov 10, 2005
I have a stored procedure that creates an xml file. It executes a SELECTstatement with the FOR XML clause and then writes the xml file using bcp andxp_cmdshell. I am calling this procedure by passing it a parameter via ADO.I have configured the SQL Server Agent with a proxy account so non-SysAdmincan execute xp_cmdshell.I'm concerned about giving non-SysAdmins execute on xp_cmdshell. I'm alsoconcerned about having to maintain the password on my proxy account whenthat users' password changes.Is there a better, more secure way to generate this xml file.Thanks
View 7 Replies
View Related
Feb 15, 2008
Hi guys,
I am looking at the plan caches/cached pages from the perspective of
sys.dm_os_memory_cache_counters and sql serverlan Cache - Cache Pages
For the first one I am using
select (sum(single_pages_kb) + sum(multi_pages_kb) )
from sys.dm_os_memory_cache_counters
where type = 'CACHESTORE_SQLCP' or type = 'CACHESTORE_OBJCP'
a slight change from a query in
http://blogs.msdn.com/sqlprogrammability/
For the second just perfmon.
The first one gives me a count of about 670,000 pages only for the object and query cache and the second one gives me a total of about 100,000 pages for five type of caches including object and query.
If I am using the query from http://blogs.msdn.com/sqlprogrammability/ to determin the plan cache size
select (sum(single_pages_kb) + sum(multi_pages_kb) ) * 8 / (1024.0 * 1024.0) as plan_cache_in_GB
from sys.dm_os_memory_cache_counters
where type = 'CACHESTORE_SQLCP' or type = 'CACHESTORE_OBJCP'
it gives me about 5 GB when in fact my SQL Server it can access only max 2GB with Total and Target Server Memory at about 1.5 GB.
Does anyone have any idea what is going on?
View 2 Replies
View Related
Sep 17, 2015
The plan cache is cleared automatically (every 30-60 seconds) on our high load OLPT server.
Can't find a reason for it. I have been logging procedure cache thru dm_exec_cached_plans, every second for a couple of minutes.
It shows that it doesn't grow beyond 200Mb. SQLserver has 64Gb available.
DBCC FREEPROCCACHE is not executed.
dm_os_memory_clerks shows the same 200Mb max (approximately).
I disabled the SQL Agent, still happens, so it's not a job causing the problem.
I disabled the Resource Governor, still the same problem.
View 9 Replies
View Related
Dec 6, 2007
Hi everyone,
I posted a related thread before about this error below when I process a dimension. And seems that the solution by using "ClearCache" can not fingure out the issue when I want to process a mining structure...... .
OLE DB error: OLE DB or ODBC error: There is not enough procedure cache to run this procedure, trigger, or SQL batch. Retry later, or ask your SA to reconfigure SQL Server with more procedure cache.
; Sort failed because there is insufficient procedure cache for the configured number of sort buffers. Please retry the query after configuring lesser number of sort buffers.
Could someone please give me some suggestions?
Your help will be very appreciated:-)
Thanks^_^
Winnie
View 2 Replies
View Related
Jun 22, 2006
I have 3 tables:
1) users: users information
2) products: products available
3) purchases: has relationships to both the users and products tables.
How can I write a stored procedure that inserts to the Purchases table and
populates to the other two tables. I was thinking that the records have to be
populated first on the "users" and "products" table then
when my INSERT is done on the "purchases" table it will refer to the
other two tables. I tried query builder but it wouldn’t allow me to insert from
one single INSERT statement to one table. Any advice that on how to approach
this? Thanks you!
View 1 Replies
View Related
Jul 20, 2005
SQL SERVER 2000Hi allThis is my first attempt at writing a stored procedure. I have managed toget it working but its unlikely to be the best way of handling the problem.While writing it I found some things that I don't understand so if any onecould shed any light it would be much appreciated. I have posted these atthe end.Sorry about the length but I thought it might be worthwhile posting the codeThe purpose of the procedures is as follows : we have a view of lots of bitsof information that need automatically mailing to different people. eachelement of information has a name allocated against it. If we had 100 piecesof data, 50 could go to manager 1 25 could go to manager 2 and 25 to manager3 etc...Both SP's look at the same viewThe first SP generates a distinct list of managers and for each managercalls the second SPThe second SP filters the view for the data belonging to the selectedmanager, and builds an HTML mail. It then sends all the bits of informationbelonging to that manager off in an EMAIL to him/her. ( It uses a brilliantbit of code from sqldev.net to handle the mail)the first mail then repeats for all the managers in the listCODE ---- SP 1ALTER PROCEDURE dbo.PR_ADMIN_CLIENT_WEEKLY_NOTIFICATION_2ASbeginSET NOCOUNT ONdeclare @no_of_managers as intdeclare @current_record as intdeclare @manager_name as varchar(100)-- count how many distinct managers we need to send the mail toselect @no_of_managers = COUNT(DISTINCT manager_name) FROMdbo.vw_client_notification_email_1-- open a cursor to the same distinct listdeclare email_list cursor for select distinct manager_name fromdbo.vw_client_notification_email_1 dscopen email_list-- for each distinct manager get the managers name and pass it to the storedprocedure that generates the mail.set @current_record = 0while (@current_record) < @no_of_managersbeginfetch next from email_list into @manager_nameEXECUTE dbo.pr_admin_client_weekly_notification @manager_nameset @current_record = @current_record+1end-- close the cursorclose email_listdeallocate email_listendCODE ---- SP2ALTER PROCEDURE dbo.PR_ADMIN_CLIENT_WEEKLY_NOTIFICATION(@current_manager_name as varchar(100))-- a unique managers name is passed from the calling procedureas beginSET NOCOUNT ON-- declarations for use in the stored procedureDECLARE @to as varchar(100)DECLARE @entry varchar(500)DECLARE @region as varchar(100)DECLARE @type as varchar(100)DECLARE @site_ref as varchar(100)DECLARE @aborted as varchar(100)DECLARE @weblink as varchar(1000)DECLARE @manager_name as varchar(100)DECLARE @manager_email as varchar(100)DECLARE @body VARCHAR(8000)DECLARE @link varchar(150)DECLARE @web_base VARCHAR(150)-- set up a connection to the view that contains the details for the mailDECLARE email_contents cursor for select region,type,site_ref,aborted_visit,link,manager_name,manager_e mail fromvw_client_notification_email_1 where manager_name = @current_manager_nameopen email_contents--some initial textset @body = '<font color="#FF8040"><b>Reports W/E ' +convert(char(50),getdate()) + '</b></font><br><br> <a href = http://xxxx > Click here to logon to xxxxx </a><br><br> '--fetch the first matching record from the table and build the body of themessagefetch next from email_contents into@region,@type,@site_ref,@aborted,@link,@manager_na me,@manager_emailset @web_base = 'http://'set @weblink = @web_base + @linkif @aborted = 0 set @aborted = '' else set @aborted = 'ABORTED'set @body = @body + '<font size="2"><b> Region </b>' + @region+ ' <b>Type</b> ' + @type+ ' <b>Site Reference </b> <a href = "' + @weblink + '">' + @site_ref+'</a>'+ ' <b>Unique Report Reference </b>' + @link + '<br>'-- continue reading the records for this particular message and adding on tothe body of the textwhile(@@fetch_status = 0)beginfetch next from email_contents into@region,@type,@site_ref,@aborted,@link,@manager_na me,@manager_emailif @aborted = 0 set @aborted = '' else set @aborted = 'ABORTED'if (@@fetch_status = 0) set @body = @body + '<b> Region </b>' + @region+ ' <b>Type</b> ' + @type+ ' <b>Site Reference </b> <a href = "' + @weblink + '">' + @site_ref+'</a>'+ '<b>Unique Report Reference </b>' + @link + '<br>'end-- close the cursorset @body = @body + '</font>'close email_contentsdeallocate email_contents-- generate the mailDECLARE @rc int EXEC @rc = master.dbo.xp_smtp_sendmail@FROM = N'FROM ME',@TO = @manager_email,@server = N'server',@subject = N'Weekly Import',@message = @body,@type = N'text/html'endQuestionsis the way I've done it OK. I thought I would be able to do it in a singleSP but I really struggled nesting the cursor things.@@fetchstatus seems to be global, so if your using nested cursors, how doyou know which one you are refering to. If you have multiple calls to thesame SP how does it know which instance of the SP it refers to.When I first wrote it, I used a cursor in SP1 to call SP2, but I couldn'tget the while loop working - I have a feeling it was down to the @@fetchstatus in the 'calling' procedure being overwritten by the@@fetchstatus in the 'called' procedure.The whole @@fetchatus thing seems a bit odd. In the second procedure, I haveto fetch, then check, manipulate then fetch again, meaning that the samemanipulation code is written twice. thats why in the first procedure I usedthe select distint count to know how long the record set is so I only haveto run the manipulation code once. Is what I have done wrong?its possible that the body of the mail could be > 8K, is there anotherdatatype I can use to hold more than 8Kmany thanks for any help or adviceAndy
View 3 Replies
View Related
Oct 13, 2004
If you are familiar with Crystal reports or Visual basic, you may be familiar with the Rate and Pmt functions.
I need to duplicate them in SQL sever 7.
Anybody have code for this already? I hate re-inventing the wheel.
More (Unnecesary) details:
I have a client who has handed me the formula that I need to use for calculating Interest rates. Unfortunatly, the formula was written in Crystal reports, so now I need to pick it apart and do the work that CR does automaticly. Any help?
View 1 Replies
View Related
Oct 27, 1999
Hello,
Does 7.0 store db Growth rate information?
I am looking for information that tells me how fast a db is growing in MB and or percentages over a given period of time, ie weekly, monthly, yearly etc. Either in real numbers or estimates. Does 7.0 already store something like this or do I need to create some code for this?
Or does someone have something like this already coded that they would be willing to share?
Thank you in advance.
Troy
View 2 Replies
View Related
Jul 15, 2004
Hi,
I want to store tax rate in my tables. I set the data type to float, I wan't 4 decimal places and the data in the table has 4 decimals, but when I run a query in query analyzer it returns: 4.4999999999999998E-2 instead of 0.045.
How can I fix this?
View 4 Replies
View Related
May 8, 2008
I am working with a company that needs some SQL Reporting developed. What is the going rate for SQL Reporting work?
View 14 Replies
View Related
May 29, 2014
I have a Table as below
Month Year Rate
1 2013 100
2 2013 101
8 2014 105
The rate is the value for the Month(1)January and year 2013.
I wana have a query which need to get rate value between 3rd month 2013 and 5the month 2014.
The Output has to be
3 2013 101
4 2013 101
5 2013 101
to
4 2014 105
5 2014 105
View 2 Replies
View Related
Jan 25, 2003
anyone wrote a function to calcuate internal rate of return?
View 8 Replies
View Related
Apr 30, 2008
I've got a statistics table that I've been writing to for about 2 years now. Every saturday night, a size (in MB) snapshot of each DB file is taken and dumped into this table. I'm then emailed a copy for that week.
Now, I'm trying to figure out what the fastest growers are. Here's the table ddl
CREATE TABLE [dbo].[DBSizeStats] (
[statid] [int] IDENTITY (1, 1) NOT NULL ,
[LogDate] [datetime] NULL ,
[Server] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DBName] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MDFName] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MDFSize] [decimal](18, 0) NULL ,
[LDFName] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LDFSize] [decimal](18, 0) NULL ,
[TotalSize] [decimal](18, 0) NULL
) ON [PRIMARY]
GO
What I'm trying to figure out is how to query the average monthly and yearly growth percentages per DB on the MDFSize column.
I'm usually pretty good at this sort of thing, but I just can't seem to wrap my head around how to solve this issue. I'm not having a very good math day.
what am I missing here?
View 10 Replies
View Related
Mar 26, 2004
Have the following rate schedule - amount and discount rate
$0-10 = 1% discount
$10-100 = 2%
$100-over = 3%
I created the following rate table with min range and no max range so last entry will handle everything over 100$
$0 1%
$10 2%
$100 3%
I then create a view to translate it as min and max range
$0 10 1%
$10 100 2%
$100 max 3%
Select* from view where $10 between minrange and maxrange
give 2 rows
1%
2%
Question: Is there a way to structure the rate table so I can use SELECT BETWEEN and
what is the most common way to setup a rate table
View 3 Replies
View Related
