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?
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.
Does SQL Server have a query cache similar to mysql, whereas the queryresult 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 requestArticles individually checked for conformance to usenet standardsTopic URL: http://www.dbForumz.com/General-Dis...pict170423.htmlVisit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbForumz.com/eform.php?p=571428
View 1 Replies View RelatedIs 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 RelatedHi,
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
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?
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
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
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 RelatedI 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 RelatedI'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.
I have to disable update, delete and insert commands in my sql query analyser window
View 14 Replies View RelatedHello!
I'm trying to improve some queries. I write the query, run it, register how long it took to return the data, twick a little and run it again. Problem: There is some kind of caching going on which render the second iteration useless in terms of time comparison. Is there any way to disable this caching mechanism, temporarily, obviously?
Thanks for your help.
J.C.
Does anyone know how to diable the File => Connect option in Query Analyzer?
Thanks
Clarification:
The situation is that I have several users on an application that controls their access to the DB. The user ID has full rights to the DB but the application stops them from making changes to data. Now I have a user that needs to be able to create and run SELECT statements in a “Query Analyser” type of environment. I can publish a Query Analyser session with locked credentials using Citrix, but I don’t want him to be able to use the File => Connect option to reconnect with one of the application Ids thus giving him full rights in an open environment.
Any ideas?
Thanks
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 ?
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.
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 ?
how to completly disable noise words checking in full-text query?
(noise dictionaries already cleared!)
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.
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.
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 RelatedHi 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.
In Sybase, I can bind a database or table to cache, can I do the same
on SQL 7.0?
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..
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
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
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
Thank you in advance
Chris
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.
Thanks
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?
Any comments would be much appreciated - cheers