October 6, 2008 at 11:34 am
Hi All
I am in a situation that i need to find the cached data by SQL, i looked into syscacheobjects, is there any other way to look into this, is it possible to find out from whome the object is been cached,i mean if it is from which application or from which user.
What does the Cacheobjtype column means on syscacheobjects .
Please post your comments
Cheers
🙂
October 8, 2008 at 7:04 pm
Can you please explain your base problem ? Looks like this is a subpart of your problem ???
October 9, 2008 at 11:09 am
Thanks Nikel
We are using java hibernate to connect to SQL server, i need to know wether the Hibernate statement has been cached on SQL server memory,
As SQL Chahes SP and views internally.
My point is if the statement is used more than once SQL caches that, but i have a strong doubt that hibernate statements are not been cached, so i need a proof
Cheers
🙂
October 9, 2008 at 11:29 am
SQL will cache the exec plan of all queries that it runs. How long the plan stays in cache depends on how often it gets used, how much memory pressure the server is under, how complex the plan is and some other conditions.
Syscacheobjects is a compatibility view. Try this.
SELECT SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 9, 2008 at 11:44 am
Plus, whether the plan is in the cache is one thing, and whether it can actually be re-used is an entirely different matter.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply