Query Cache?
Jul 20, 2005
Does SQL Server have a query cache similar to mysql, whereas the query
result is cached, if the table has not been changed?
If so, please refer me to more info.
Thanks.
--
http://www.dbForumz.com/ This article was posted by author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbForumz.com/General-Dis...pict170423.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbForumz.com/eform.php?p=571428
View 1 Replies
ADVERTISEMENT
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 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
Nov 2, 2005
Is there a clean way to flush the query cache so I can simulate the first execution of an SQL statement any time I want to?
View 3 Replies
View Related
Mar 22, 2006
Hi,
During a trace in profiler, I see some lines that indicate :
exec sp_execute 12
Or
exec sp_execute 24
I suppose these are query in cache and are recall by query processor ? If yes, how can I see this query in clear text ?
Thanks for help
View 2 Replies
View Related
May 8, 2008
Hello,
I need to disable query cache in MS SQL Server 2005 Express. How can i do it?
My application can opertate ~2K records per sec (INSERT).
A million records costa me extra 142 Mb of RAM.
It means that sql server will take exrta 1Gb of RAM every hour.
I can't afford that.
Can anyone please help me?
View 8 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
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
Apr 3, 2002
HI
May I know how to clear the cache area in the analyser as I need to
know the time taken for execution of an sql statement(query) before
and after creation of indexes.
tia
MK
View 1 Replies
View Related
Jan 13, 2015
All the recommendations I see from Microsoft docs is to limit the use of Query Notifications (QNs) to notifying connected clients when changes to mostly-static reference or configuration data occur, and to keep the number of overall query forms in play and connected clients to a minimum. Any way regarding a more integral use of QNs and Service Broker from a web app to notify n-web servers (farm) of an update to data that could be updated concurrently and quite frequently, or with a system where the technique is used extensively with lots of different query forms?
View 0 Replies
View Related
Apr 3, 2006
I am trying to improve the performance of a query. No matter how badit runs the first time, it runs really fast the second time.So how can I tell if I've done anything to improve the query if italways comes back quickly after the first run? I assume thequery/data/plan is in cache - how can I clean it out for my session?Thanks in advance for any help you can provide -
View 4 Replies
View Related
Aug 3, 2015
I've a spatial (GIS) Data which is used frequently insertion, updation.
5 lakh records insertion in daily basis. when I trying to generate reports last 3 days or one weak, it takes 20-30 minute.
very disappointing while playing with clients. how to boostup and perform fast.
I think as so once we set query plan in buffer permanently then i would be faster than ever.
View 4 Replies
View Related
Jun 4, 2015
I am using SQL Server 2012 Express.
I am doing performance tuning of SP/Query in Dev-Test environment.
I found that SQL Server caches plan between successive executions.
So if I test/execute SP 10 times, after 1st or 2nd execution, SQL server will pull-up plan-info from CACHE...Not from SQL SERVER Or Database...
Means i am not getting correct answer...
I found this 2 commands:
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
But they say that executing above command might interfere/bother other people executing other query/sp on this server.
They also say that: Freeing the plan cache causes, for example, a stored procedure to be recompiled instead of reused from the cache. This can cause a sudden, temporary decrease in query performance.
Part of query was using Dynamic-SQL executed with EXEC command.
I replaced that with SP_EXECUTESQL.
How can I start testing of each SP-run with Fresh/Blank CACHE ?
View 1 Replies
View Related
Aug 17, 2015
I'm getting an alert which states that both my Buffer Cache Hit Ratio and PLE are low on one of my SQL Servers though I'm not sure how to correctly check this.
I ran:
SELECT object_name, counter_name, cntr_value
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Buffer Manager%'
AND [counter_name] = 'Buffer cache hit ratio'
Which gives me the Buffer Cache Hit Ratio, cntr_Value of 9 though its constantly dipping between 3-3000 and is never steady and I'm unsure if this is normal.
I also ran:
SELECT object_name, counter_name, cntr_value
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Buffer Manager%'
AND [counter_name] = 'Page life expectancy'
Which gives me the Page life expectancy of 209061.
If these values would cause concern and if this is a normal Buffer Cache Hit Ratio? It's constantly dropping from high or low from what I can see. These scripts were pulled from another forum and I'm assuming they're showing the correct values.
View 1 Replies
View Related
Feb 24, 2006
I am using a lookup component in a SSIS data flow. The lookup is a select to a foxpro table. THe lookup works fine with full cache selected. I cannot get the lookup to work with a partial or no cache. I have the latest Foxpro OLE DB driver installed which I understand to support paramaterized queries. Has anyone had success with using cached lookup to Foxpro? Does anyone know how to set the lookup properties of sqlcommand and sqlcommandparam? I am unable to find any examples in BOL or on the web.
Here are some details. IF I go with "use a table or a view" option with the default cache query I get initialization errors
[lkp_lab_worst_value [6170]] Error: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft OLE DB Provider for Visual FoxPro" Hresult: 0x80040E14 Description: "Command contains unrecognized phrase/keyword.".
In the advanced editor I see
SQLCommand set to
"select * from `kcf`"
and SQLCommandParam set to
"select * from
(select * from `kcf`) as refTable
where [refTable].[patkey] = ? and [refTable].[dayof_stay] = ? and [refTable].[modifier] = ? and [refTable].[kcf_code] = ? and [refTable].[source] = ? and [refTable].[kcf_time] = ?"
I believe the above error is because Foxpro V7 does not support the inner subselect . In addition the query contains CRLF without a continuation character (";").
If I remove the CRLF in the sqlcommandparam query, using the advanced editor, I get this design time error "OLE D error occurred while loading column metadata. Check the sqlcommand and sqlcommandparam properties". The designer requires both properties to be set, its unclear to me how the interact.
I cannot find any examples in BOL or on the web on how to set these 2 properties. Can someone give me a few guidelines?
I can get past the design errors by changing sqlcommandparam to a plain select that is VFP 7 compatible ( I removed the subselect and the square brackets):
select * from kcf as refTable where refTable.patkey = ? and refTable.dayof_stay = ? and refTable.modifier = ? and refTable.kcf_code = ? and refTable.source = ? and refTable.kcf_time = ?
But then I get a runtime error
[lkp_lab_worst_value [6170]] Error: An OLE DB error has occurred. Error code: 0x80040E46. An OLE DB record is available. Source: "Microsoft OLE DB Provider for Visual FoxPro" Hresult: 0x80040E46 Description: "One or more accessor flags were invalid.".
[lkp_lab_worst_value [6170]] Error: OLE DB error occurred while binding parameters. Check SQLCommand and SqlCommandParam properties.
Any idea on what I should try next ?
View 3 Replies
View Related
Mar 14, 2015
Is only one plan is kept for one query in plan cache?
i heard generally hash is created for a query and plan is search with this hash.
View 2 Replies
View Related
Mar 13, 2003
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?
Any help or advice would be much appreciated.
View 5 Replies
View Related
Jul 12, 2006
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?
View 1 Replies
View Related
Jul 9, 2002
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
Can anyone help on this.
Thanks in advance.
Madhu.
View 3 Replies
View Related
Dec 3, 2001
In Sybase, I can bind a database or table to cache, can I do the same
on SQL 7.0?
View 1 Replies
View Related
Sep 9, 2005
Hi..
I don't know this is possible or not.
for example.. I have a blog site..
my entry links are like that
blog.asp?ID=33
blog.asp?ID=36
36 points "select * tbl where id=36"
And approximately BLOG #36 has 400 views everyday..
content is same.It never change..
Everyday my application performs "select * tbl where id=36"
with direct ASP or Stored Procedure..
Do you have any solution about this ?
How to improve performance..
Just imagine, If entry #36 had 2.000 views everyday ?
just example..
Of course the less perform query, the more performance..
View 1 Replies
View Related
Mar 14, 2008
Hello!
When I create new job, I get a message "Job cache 97% full"
What should I do? And what IS a job cache?
Thanks!
Makkaramestari
View 3 Replies
View Related
Apr 25, 2007
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.
Thanks,
Mehul Mistry
View 2 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
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 18, 2000
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.
Thanks
View 1 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
Sep 30, 1999
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?
Any comments would be much appreciated - cheers
View 1 Replies
View Related
Aug 16, 2000
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.
Thanks.
View 1 Replies
View Related
Mar 18, 2005
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
View 5 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
