Let me say up front that rs:ClearSession=true didn't work :)
I have a report on RS that displays two blocks of data. The intent is to move items from one list to the other. To do so, there's a link on the detail lines which does a drill-through to another report. That report takes parameters (IDs for the record to be moved) and uses a stored procedure as it's datasource. The stored procedure takes in the parameter, does an INSERT INTO into the table that's driving which list items appear on, then returns the ID that was affected. On the report, there's just a text box stating that the item was added to the table and a "Back" text box. This Back item is a drill through back to the original report.
When we go back to the report (not really *back* since we're actually drilling through to the report anew), I want it to show the two lists with the one item moved from the one list to the other. Problem is, since the report was already run, it's showing the data from the cache. If I hit the refresh button, it updates and the item moves as it should.
I've read a number of posting on this and the only thing anyone said that should work was to use the rs:ClearSession=true option in the URL. So I generated a URL to the report, added this option. Tested that my URL worked and then changed the "Back" text box's navigation so that rather than a simple drill-through, it goes to a URL...the one with the ClearSession.
But it still doesn't work. I click the link on one of my lines, it goes to the drill through saying that it added the line to the table (I can even query the table in QA and see that it did), then I click the Back and I can see in the address box that the ClearSession arguement is there but the results still don't refresh.
I've also tried CTRL-F5 while in this state and the data doesn't refresh. The only thing that makes it re-run the stored procedure and pull in new data is the green refresh button.
I have reports which are cached to decrease the time it takes to run. Is there anyway to display within the report, when the cache (snapshot) was taken?
So if it's 10:00:00 AM and the report was manually ran and cached for 60 mins. When the report is called again at 10:10:00 AM, I would like to display to the user that the data of the report is from 10:00:00 AM.
I created a report model using reporting services, deployed the model locally and then built a report based on that model. The report works fine, but any data updates don't show up, seems like data is cached. I bounced reporting services, cleared cached tables in ReportServerTempDB but of no use. Has anybody faced this problem? How can I get updated data for reports built through a report model. Any help is appreciated.
I have a report whose parameters are limited by the user who is logged in. Ex. if a user had full access they would see all 100 hospitals in our hospital parameter, if another user had limited access they might only see 5 - this will vary largely on who is logged in. The data source that drives these parameters are set as follows:
Credentials stored securely in the report server, username and password are entered and Impersonate the authenticated user after a connection has been made to the data source is checked.I now want to cache this report for X minutes. So I goto Properties/Execution and select 'Cache a temporary copy of the report. Expire copy of report after a number of minutes'.
Applying this gives me the error: Credentials used to run this report are not stored.
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....
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.
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.
On Microsoft performance monitor, what is the difference between SQL Server Cache Manager: Cache Hit Ratio and SQL Server Buffer Manager: Buffer Cache Hit Ratio? We have a production server where the buffer cache hit ratio is consistently at 99%, which is normal. However, the cache hit ratio is 73%. What is the difference between the two hit ratios, and why would we have such a significant difference between the two?
I read about the SQL 2005 Buffer Cache and the .NET cache.I tried it on about 8000 users query. Both are fast....SQL 2005 being a little faster (probably due to I was sorting the .NET Cache).Any comments?
Hi there, We have cluster server and we are using sql server 7, everything was working fine on node a and when we move to node b and run the job from the jobs it gives the error message:
Error 22022:SQLServerAgent Error: job ox7bb7d7bf23b8974d8ea8f15e43e854cf does not exist in the job cache
Hi all, I have a question regarding SQL caching. I want to use SQL caching for my pages.I've gone through http://msdn2.microsoft.com/en-us/library/e3w8402y(VS.80).aspx article on msdn, where in step by step procedure is explained for this. But I have a slightly different situation. In the article, we need to configure our application from web.config by following... <!-- caching section group --><caching> <sqlCacheDependency enabled = "true" pollTime = "1000" > <databases> <add name="Northwind" connectionStringName="NorthwindConnectionString1" pollTime = "1000" /> </databases> </sqlCacheDependency></caching>
Where in "NorthwindConnectionString1" is the SQLdatasource connection string. But I'm not using SQLDatasource, and have my own classes to build a connection datasource and get a dataset, and then bind it to my control.So in this case what should I write for the above "connectionstring" attribute. My connection string is in web.config only with a section defined. and in my application i'm reading from web.fing only.The connection section i'm using is as follows... <ConnSettings> <add key="ConnString" value="server=serverName;database=dbName;uid=dbuser;Pwd=password"/> </ConnSettings> I want to know how to configure my sql cache in this situation.
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
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
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?
I have been seeing this strange statistics in one of our servers. The cache hit ratio has gone beyond 100%, it is currently showing 124%. Has anyone seen this before.
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.
Help, have recently upgraded from 6.5 to 7.0 and have come across a problem with performance. The problem appears to relate to the buffer cache being flushed, the buffer cache hit ratio drops from 98% to 0% in a matter of a second. It then very slowly grows, then is flushed again, then increase slowly upto 30%.
Does any one have any ideas as to what would flush the buffer cache?
Hi, I was wondering if anyone knew where I could find good information on performing a database cache. I want to cache tables and run queries from them within a Cold Fusion Application.
I have a large dell server with 4 processors, and 8 gig of memory on win 2000 advanced with sql 2000 enterprise edition running a 3rd party app. My cache hit ratio averages about 76%. I thought the gneral rule was if you get below 80% to add more memory. However my memory manager shows I am only using 71% of my memory and have a full gig available. I have the sql server set to use about 7.1 gig of the 8 gig on the server. My ? is if I am only using 71% of my memory, will will adding more memory actually help my cache hit ratio
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.
I have a server where the cache hit ratio averages 99.5%, however the cache flushes figure is high at 13068 over half a day, the avg free page scan is 122, both above what microsoft reccomend.
The server has 320Mb memory of which 272 is allocated to SQL Server.
Of the 272, 229.9 is allocated to the data cache.
I would be grateful of any suggestions to improve the situtaion
Microsoft recommends a 1MB L2 processor cache for SQL2000. I only have a 256K cache. What is the impact and what can I do, other than replace my procs, to compensate.
We recently moved a database to the SAN and it is not performing so well. Watching performance logs I have noticed that the Cache hit ratio is only around 60%. I checked memory right away and that seems to be fine. Plenty of physical memory available and only about 40% is committed to use.
The SAN is currently configured to have 200 mg of Read Cache, I would imagine that is a little low, but not sure.
This is the first time we have ever put a database on the SAN so our knowledge of optimization is lacking. Any ideas on how to improve performance would be helpful.
if i run a query once it takes 10 seconds and if i run the same query again it takes 1 second because it is cached. So i want to know how i can clear it because i want do some benchmarking on a query that is in cache
I'm very confused about which dbms to use. I've two choices: MS SQL Server 2000 and Intersystems Cache. Although Cache is post relational I don't know anything about it. However it has object oriented features, I know how to do things in SQL Server. Can anyone give me the advantages and disadvantages of both systems in this situation? Thanks in advance.
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.
