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
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 RelatedHello,
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?
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 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 ?
I have a parameter value as shown below and this is dynamic and can growÂ
Example : 101-NY, 102-CA, 165-GA
116-NY, 258-NJ, 254-PA, 245-DC, 298-AL
How do I get the values in the below format
NY,CA,GA Â --- each state to be followed with comma and the next state
NY,NJ,PA,DC,AL Â --- each state to be followed with comma and the next state
correct query that will fetch  only state names and not the numbers.
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 have a problem with sql 7 full text query. Everything appears to be in place the database is enabled and the catalogue populated but a query like this
select * from risks_intercat WHERE CONTAINS(RISK_NOTES, ' "bean curd" ')
always returns this;
Search on full-text catalog 'ftc_Risks_ic' for database ID 8, table ID 329768232 with search condition ' "bean curd" ' failed with unknown result (8bcf8cc).
has anybody got any ideas why?
I'm wanting to concatenate something that doesnt exist to an already existing field.
What I need to do is add a generic email address to every record in a data view So, what it would do is take the column that does exist and add @domain.com to another column that I would call username So rather than it just appearing as 911234 it would be a derived column saying 911234@domain.com
i've tried using +''+ but I get a space after the number.
How to run a query that is in the column as text?
View 2 Replies View RelatedIn a field there is a very long text. I am trying to retrieving using query analyser but again it gives me only part of the document...
any help please?
I have an SQL database with rows that have parens in the data.If I run a select statement such as:SELECT SongNameFROM SongsWHERE SongName = 'John Jacob (Jingleheimer Schmidt)'It returns zero rows. This also:SELECT SongNameFROM SongsWHERE SongName LIKE '%John Jacob (Jingleheimer Schmidt)%'returns zero rows.If I change it to this:SELECT SongNameFROM SongsWHERE SongName LIKE '%John Jacob%'Then I get the row returned.Is there a way to use the first query example above and return the row?I'm guessing it has something to do with the parenthesis...*** Sent via Developersdex http://www.developersdex.com ***
View 2 Replies View Related
I have this as my query text
SELECT MachNumber, ShiftNumber, HeatNumber, PartNumber,
RevNumber, CureTime, Bump1, Bump1Interval, Bump2,
Bump2Interval, DwellTime, LateToDrop, EarlyToDrop,
OverTime, CycleTime, ChangeTime, Date
FROM HeatInfo
WHERE (ShiftNumber = @ShiftNumber)
AND (Date >= @StartDate) AND (Date <= @EndDate)
This correctly returns 4 rows, which is great except that I would like to have these records Sorted by the Date. So I added the OrderBy command to the bottom of the query, like this
SELECT MachNumber, ShiftNumber, HeatNumber, PartNumber,
RevNumber, CureTime, Bump1, Bump1Interval, Bump2,
Bump2Interval, DwellTime, LateToDrop, EarlyToDrop,
OverTime, CycleTime, ChangeTime, Date
FROM HeatInfo
WHERE (ShiftNumber = @ShiftNumber)
AND (Date >= @StartDate) AND (Date <= @EndDate)
ORDER BY Date
This query returns 181 rows! whis is not great. What am I doing wrong here, I suppose that it is something extreamly obvious or I would probably have figured it out.
My host does allow me to create full text catalogs. I use SQL Server Management Studio Express, so I'd have to do it "by hand" as opposed to point-clicking menus and going through wizards. Where do I go to find those commands? I've seen them before but I can't remember where to get them. They're the ones that begin with sp_.
I need the commands for:
Creating a full text catalog
Hi Everyone,My SQL Query is :"select Field1 from table1"The Field1 in Database is of Type "nvarchar" . I need to convert this Field into "integer" in the Query itself.Please Help!!I have already tried "Select convert(int, Field1 ) from table1"The field gives zero output ThanksRegardsNavdeep
View 4 Replies View RelatedHi,
I'm using SQL Server 7.0.
I have a query (select * from table1) and I'd like to have the results of this query sent to a text file instead of the results windows when I run it from Query Analyzer.
Any suggestions?
Thanks in advance,
Darrin Wilkinson
I have a couple columns in a table that have a data type of Text. In a query I need to concatenate these two columns into one result.
For example
Code:
SELECT (Table1.TextColumn1 + ' ' + Table1.TextColumn2) AS 'Text'
FROM Table1
However when I try this I get the error
Invalid operator for data type. Operator equals add, type equals text.
how to output txt file in query by simple way ? This query have error.
Select * from invoice
into 'c:abc.txt'
Hi...
I've this type of data: 148.667,31 or 344.207.6 ... type text.
I need to convert it to money in a query...
How I can do this?
Thanks...
Paolo Manfrin
i jave the following query
select *,substring(stafflog,15,11) as test into #t1 from dbo.Customers
where stafflog like '%armagh%'
go
select left(stafflog,4) as Staff,count(left(stafflog,4)) as Total from #t1
where cast(left(test,charindex(' ', test))as smalldatetime) = cast(convert(varchar(8),getdate()-1,1) as datetime)
group by left(stafflog,4)
go
select Title,Address1,Address2,Town,County,Postcode,TelephoneDay,TelephoneWork,TelephoneEvening,
MobileTelephoneNo,Contact,Mail,Telephone,Terms,StaffLog
from #t1
where cast(left(test,charindex(' ', test))as smalldatetime) = cast(convert(varchar(8),getdate()-1,1) as datetime)
go
drop table #t1
go
i need this query to be scheduled to run at a certain time every day and the results to be put in a text file.
is there an easy way to do this or what should i be looking at doing to get it to work
Hi,
I have the following table called "tests" :
id WeekNbrnametest hours
--------------------------------------------------------------------------
1 2007/26John "testA"5
2 2007/26John "testB"6
3 2007/26David "testA"3
4 2007/28David "testC"2
5 2007/30Victor "testD"1
I want to write a query so that I have as a result one row per person and per week, as followed
WeekNbrname testhours
--------------------------------------------------------------------------------
2007/26John"testA, testB" 11
2007/26David"testA" 3
2007/28David"testC" 2
2007/30Victor"testD" 1
This means that I need to concatenate the values of the test column if in the same week and same person.
For now, I have only managed to do the job without the test concatenation the following way:
SELECT DISTINCT WeekNbr, name, SUM(hours) as [Total of hours]
FROM tests
GROUP BY WeekNbr, name
and I get the following:
WeekNbrnamehours
-------------------------------------------------------
2007/26John 11
2007/26David 3
2007/28David 2
2007/30Victor 1
Anyone could help me please?
Thanks so much in advance
Pierrot
I want to store the SelectQuery result in a text file.
I have given this query
SELECT * FROM xxx OUTPUT TO 'd:xxx.txt'
but this error is comming
Incorrect syntax near the keyword 'TO'.
can any one help me please!
Regards
Js.Reddy