SQL Cache Dependency Invalidation - Can It Work On The Procedure Based Command?
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
ADVERTISEMENT
Jan 6, 2006
I'm trying to set enable SQL cache invalidation on a webpage, specifically on a a datasource that fills a GridView.
The website http://beta.asp.net/QUICKSTART/aspnet/doc/caching/SQLInvalidation.aspx makes it sound so very simple, and I have attempted to do all that it says, yet something just isn't right. The site says that the following needs to be done.
Queries must explicitly include column names in the SELECT statement. Using "SELECT *" results in a query that will not be registered with Sql Server 2005 query notifications.
Table names in queries must include the ownername. For example, if you issue a query against the authors table in the pubs database, the query must reference the table as "dbo.authors".
The security identity running the query must have rights to register queries for notification in Sql Server 2005. This right can be granted with the following T-SQL command: GRANT SUBSCRIBE QUERY NOTIFICATIONS TO username.
The security identity running the query must also have rights to send query notifications from Sql Server 2005. This right can be granted with the following T-SQL command: GRANT SEND ON SERVICE::SqlQueryNotificationService TO username.
I've done items 1,2 & 3, but when I attempt to do item 4 I get the following result in TSql:
Msg 15151, Level 16, State 1, Line 1
Cannot find the service 'SqlQueryNotificationService', because it does not exist or you do not have permission.
My login account has sysadmin priviledges, so I'm guessing the latter part of the error doesn't apply to me. But what do I have to do to get SqlQueryNoticationService to exist?
View 3 Replies
View Related
Mar 31, 2006
I have used the notification-based cache invalidation with my web application, but how can I check it really works? I mena if the backend data didn't change , the data will come from cache not from the database?
<asp:SqlDataSource ID="SqlDataSource_WebInfo" runat="server" ConnectionString="<%$ ConnectionStrings:LocalSqlServer %>" SelectCommand="SELECT simplified, traditional FROM temp" ProviderName="System.Data.SqlClient" EnableCaching="True" SqlCacheDependency="CommandNotification">
</asp:SqlDataSource>
Thanks a lot!
View 1 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 8, 2007
Hello,
Am wondering if someone can give me a pointer on where to start with a query i have.
What i am trying to do is provide some sort of information to a user that data from a database has been changed. I know that you can cache a page and have output cache directive invalidate it next time it loads if changed but wondering if its poss to be told its changed without reload ( dont see how but never know) - even if not on that page.
What im kinda thinking is something like the popup that outlook has when a new message arrives in in box. Is that just sending a new query? is there a table last modified function in sql? etc.
This is for a web app in c#.
Any help would be appreaciated.
cheers
View 3 Replies
View Related
Jun 5, 2007
Hi I am using SQL Server cache invalidation with caching in my application.
I have a master page and several other pages that are referring the master page.
I have specified :
<%@ OutputCache Duration="60" VaryByParam="*" SqlDependency="CommandNotification" %> on one of my content page.and I have included the
System.Data.SqlClient.SqlDependency.Start(connectionstring) in my Application_Start.My web.config contains this section as well -
<caching><sqlCacheDependency enabled="true">
<databases><add name="BizPartnerV4" connectionStringName="BizPartnerConnectionString"/>
<add name="DirectBuyBeaverton" connectionStringName="DirectBuyBeavertonConnectionString"/></databases>
</sqlCacheDependency>
I</caching> have also run the aspnet_regsql utility to enable sqlcache dependency for the database.
But my page is not taking the values from cache when the page is refreshed.
Please help.
View 6 Replies
View Related
May 22, 2008
If I simply want a db table to have caching enabled and just care about the application getting that data from the cache instead of physically getting it from the table (no need for me to have a delegate for notification), there is no need for me to create a sqldependency object within my middle tier method associated with my command, correct? I have the sql cache enabled for a particular db table and have the following in my Application_Start event of the global.asox file (SqlDependency.Start “connectionstring�) Once I change something in the table, the cache is invalidated. That’s all I need, correct?
View 3 Replies
View Related
May 24, 2007
Hi,
I am not sure is this is .NET issue or a problem with the SQL Service broker so i hope you can either point me in the right direction or make suggestions to solve this problem.
I am using the SQL Server 2005 service broker to manage my caching in an ASP.NET application. The service broker is started with all require queues and permissions set up for the service account that connects to SQL Server. The application + broker service works fine in that if a table row change is made to a registered query the cache will invalidate in the web application.
The problem i am getting is errors appear in the application event log as well as the SQL logs and i am not sure what it means or why it is throwing an error even though the application works as it was designed.
Below are a couple of errors that are being logged
The query notification dialog on conversation handle '{ADC34B70-BD09-DC11-AFEA-0016E6539AEB}.' closed due to the following error: '<?xml version="1.0"?><Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8470</Code><Description>Remote service has been dropped.</Description></Error>'.
The query notification dialog on conversation handle '{E8642B09-BB09-DC11-AFEA-0016E6539AEB}.' closed due to the following error: '<?xml version="1.0"?><Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8490</Code><Description>Cannot find the remote service 'SqlQueryNotificationService-f19bedef-a9f9-46e1-8f37-1b573614bd26' because it does not exist.</Description></Error>'.
Service Broker needs to access the master key in the database 'test'. Error code:25. The master key has to exist and the service master key encryption is required.
Thanks for your help.
Jim
View 1 Replies
View Related
May 31, 2007
Hello all.. i'm having a major issue with my Sql Express 2005 database :(
I'm using wicked code sitemap to allows for a sitemap to be stored in a database.. it uses sql cache dependency to invalidated the cache bla bla
Problem:
After i update a record / add new record the database generates a SqlQueryNotificationStoredProcedure
But it never gets executed. I've done tests on the code it's calling everything accordingly so i'm assuming the problem lies in a configuration setting of the database
done alot of searching around the net and i've found this
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO username
i'm using Windows Authentication how do i run this and will this solve the problem
Thank you in advance
Chris
View 3 Replies
View Related
Feb 8, 2008
I'm working off of the example shown here:
http://www.c-sharpcorner.com/UploadFile/mosessaur/sqlcachedependency01292006135138PM/sqlcachedependency.aspx?ArticleID=3caa7d32-dce0-44dc-8769-77f8448e76bc
The tutorial shows that an entry must be made in web.config for a sqlCacheDependency node in web.config. When a dependency is added, they set an attributed called "connectionStringName" that references a connectionString established earlier in the web.config. My question is twofold:
1). First, I tried setting up my connection string using the connectionStrings node (my other apps use the AppSettings node), but when I try to extract the value in my code using the following syntax:
connStr = Convert.ToString(System.Configuration.ConfigurationManager.ConnectionStrings["devConnStr"].ConnectionString);
I get the following compilation error: "ConfigurationManager does not exist in the class or namespace System.Configuration";
Am I extracting it with the wrong code? Does that setup exists in .NET v 1.4? No, I cannot use .NET v 2.0 for various reasons.
2). If I cannot use ConnectionStrings in that way and must use AppSettings, how do I set up SqlCacheDependency node to recognize that connection string?
View 1 Replies
View Related
Sep 11, 2007
Hello.I am having problems with SQL Cache dependency. I am using SQL 2005, ASP .net 2.0.Every time i try to load data from cache, this is null. It acts like someone is constantly changing everything in the db.Because of this my website makes hundreds of connections to the db instead of 5. This is a major issue that i cannot figure it out. PLEASE ADVISE.On my local machine everything seems to work just fine. On the testing server the cache is always null.Is cache dependency related - to the platform used? - to the number of IIS servers connected to the DB? - to the sql user used? Also a strange thing happens. When i change something in web.config the cache is working for about 1 min and after that it stops. Here is the code i wrote: public ManufacturerList GetAllManufacturers() { if (HttpContext.Current.Cache[ConstantsManager.Instance.GetDefaultAsString("CACHE_MANUFACTURERS")] != null) return HttpContext.Current.Cache[ConstantsManager.Instance.GetDefaultAsString("CACHE_MANUFACTURERS")] as ManufacturerList; SqlCacheDependency dep = new SqlCacheDependency(ConstantsManager.Instance.GetDefaultAsString("DB_NAME"), ConstantsManager.Instance.GetDefaultAsString("TABLE_MANUFACTURERS")); _manufacturerList = new ManufacturerList(); DataReader reader = SqlHelper.ExecuteDataReader(WebContext.ConnectionString, CommandType.StoredProcedure, "dvx_web_MANUFACTURER_LoadAll", null); while (reader.Read()) { int manufacturerID = reader.GetInt("ID"); Manufacturer findManufacturer = _manufacturerList.FindByID(manufacturerID); if (findManufacturer == null) { findManufacturer = new Manufacturer(); findManufacturer.LoadFromDataReader(reader); _manufacturerList.Add(findManufacturer); } } reader.Close(); HttpContext.Current.Cache.Insert(ConstantsManager.Instance.GetDefaultAsString("CACHE_MANUFACTURERS"), _manufacturerList, dep); return _manufacturerList; } Thanks a lot.
View 1 Replies
View Related
Jun 11, 2006
Hi,
can I use sql cache dependency with ms sql 2005 express edition?
View 1 Replies
View Related
Jul 10, 2006
I have a sqlCacheDependency element registered in web.config that I figured would set the database up to deliver all notifications for all its tables:
<sqlCacheDependency enabled="true">
<databases>
<add name="MyDb"
connectionStringName="MyDbConnnectionString"/>
</databases>
</sqlCacheDependency>
I've seen docs saying that's all I need to do with SQL Server 2005, but that doesn't appear to be the case. I get the error:
The database 'MyDB' is not enabled for SQL cache notification.To enable a database for SQL cache notification, please use the System.Web.Caching.SqlCacheDependencyAdmin.EnableNotifications method, or the command line tool aspnet_regsql. To use the tool, please run 'aspnet_regsql.exe -?' for more information.
Are the docs I read wrong or am I missing something?
View 12 Replies
View Related
Nov 17, 2006
Hi,
Our development team is building an application using .net framework 2.0 and sql server 2005 developer edition. They are using service broker and sql cache dependency classes in their code and we are running into a problem where the database used for their application is growing alarmingly in size, ends up using a log of memory and finally drags the machine. The database machine is a developer class machine used for development purpose only.
After we did our research we found the following:
1. the tables created by the team do not increase in size.
2. the system tables created by sql server for service broker and sql dependency grows very big during their usage.
sys.sysconvgroup
sys.sysdesend
sys.sysdercv
The above three tables have the same number of records and they are equal in size for eg, at one point they were 1.5 gb each amounting to total 5 gb out which the actual user created tables, indexes, sp amount to only 500 Mb.
There were some post in other forums that suggested to use triggers to close the open conversations and also use END CONVERSATIONS in the code. We have used that solution also but doesn't seem to solve the problem.
Is there a definite solution from microsoft for this issue.
Anybody has any insight to this. Thanks for your help in advance.
View 1 Replies
View Related
May 30, 2008
I have implemented SQL cache dependency to invalidate cache whenever the result of the stored procedure gets changed. The stored procedure is written obeying all the notification rules.
Our problem:
As long as we are working on Local machine, Development server or Pre production server its working fine means the cache gets invalidated whenever there is any change in the result set. But the same implementation doesn’t work in Production. Cache does not get invalidated.
Difference in Production environment and other environment:
Production uses cluster SQL server, others use standard SQL server. Production has load balancing means application is deployed on 6 servers which hits same clustered SQL server. In application’s global.asax file i have Start dependency so I see 6 different notifications started in SQL server which is right. But whenever there is a change in result set the cache doesn’t get invalidated in any of the application.
My question is
Do we have to follow any different kind of implementation of SQL cache dependency for web farm and cluster SQL server scenario?
On the same note I would like to add, on the same SQL server I have one more database and a different application is accessing this database. This application is also using SQL cache dependency and its working fine. The only thing this .Net application is NOT deployed on web farm. Its deployed on single application server.
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
Apr 15, 2008
is it possible that sql statements cached from a program running this morning (that has now ended) and that they are still running in the sql?
I see records being added when they shouldn't be now and i'm wondering if sql could have cached the statements as the server was very slow at the time.
please advise?
View 8 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
Jan 28, 2007
Hello,
I have a sequense of sql commands in order to recursively update a table that has parents/childs
After I create a temporary table, I need to run an sql command that for some reason is not working. No errors, the command is actually excecuted, but I beieve the rowcount is 0 from the beggining
Here is the command:
Dim InsertConnection As Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection("Server=myServer;User ID=myUser;pwd=myPSW;Database=myDatabase")
Dim SqlInsertCommand As Data.SqlClient.SqlCommand = New Data.SqlClient.SqlCommand("while @@rowcount > 0 " _
& "begin INSERT INTO submenu" _
& uid & " (pageid,parentid) SELECT y.pageid , y.parentid FROM submenu" & uid _
& " i INNER JOIN page y ON y.ParentId = i.pageID LEFT OUTER JOIN subMenu" _
& uid & " i1 ON i1.pageId = y.pageId WHERE(i1.pageID Is NULL) " _
& "end", InsertConnection)
InsertConnection.Open()
SqlInsertCommand.ExecuteNonQuery()
InsertConnection.Close()
SqlInsertCommand = Nothing
If I insert any other SQLcommand there it is excecuted normally.
The command I have is excecuted fine using sql server manager.
Is there any way that a command is excecuted in the SQL manager but not in a page...??
Any ideas would be great...
Thank you
View 6 Replies
View Related
Jun 7, 2004
Hi
I want set the recovery model to SIMPLE via an ASP page
my DB is on the web
i used this code in my ASP page
strSQL = "ALTER DATABASE dbname SET RECOVERY SIMPLE"
and when i run it
nothing happen and the Recovery ramain FULL model
==========================
Please tell me is this a correct code
if not please tell me the correct code for ASP
until i change the Recovery Model to SIMPLE
Thanks
View 2 Replies
View Related
Feb 23, 2001
Hello,
I am planning to backup my log files to DLT and to disk on a regular basis. I was planning to issue a "backup log xxxxDB to device with NO_truncate" and then issue a "backup log xxxxxDB to DLT_device".
I have tried this but noticed that the size of file produced from the first command was greater than produced by the second command. It seems that the NO_truncate command is actually truncating the Log.
I am using SQL 7 and SP2 and truncate log on checkpoint option is switched off.
Can anyone shed light on this.
Thanks in advance.
Samir
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
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
Nov 8, 2007
Hi all,
I have a script to bulk copy data from my table into textfile. This bcp script will be executed by xp_cmdshell. When I try to execute the scipt inside query windows it returned an error : Error = [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified. But when I test the bcp script in command prompt it will execute successfully
Is there any misconfiguration ? (I'm using sqlexpress) thanks in advance.
Best regards,
Hery
View 9 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
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
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
