June 27, 2014 at 11:52 pm
is there any table to know old queries
August 1, 2014 at 3:26 am
Can you please explain the question properly?
Cheers
August 1, 2014 at 7:04 am
No. You can get the queries that currently have cached plans by querying the sys.dm_exec_query_stats and sys.dm_exec_sql_text DMVs, but that's all.
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
September 8, 2014 at 6:43 am
The only way to get a historical record of queries run is to run a trace at the time that you want to observe. Otherwise you have dipped-out.....
October 3, 2014 at 2:37 am
begin tran
insert into test (id,name,a) values (1,'aa','hjh')
waitfor delay '00:00:10'
commit tran
i tested like above
after i ran below ...
SELECT t.[text]
FROM sys.dm_exec_cached_plans AS p
CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) AS t
WHERE t.[text] LIKE '%insert into test%'
but i m not getting above query
October 3, 2014 at 7:14 am
dastagiri16 (10/3/2014)
begin traninsert into test (id,name,a) values (1,'aa','hjh')
waitfor delay '00:00:10'
commit tran
i tested like above
after i ran below ...
SELECT t.[text]
FROM sys.dm_exec_cached_plans AS p
CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) AS t
WHERE t.[text] LIKE '%insert into test%'
but i m not getting above query
To be honest you shouldn't be surprised. SQL Server doesn't Cache Statements like Oracle does; only the query plans (execution plans) are cached.
The table sys.dm_exec_sql_text only stores currently executing SQL and once the execution is completed will be removed.
As I mentioned previously, you should run the Profiler to record a history of Statements executed.
October 3, 2014 at 7:16 am
kevaburg (10/3/2014)
The table sys.dm_exec_sql_text only stores currently executing SQL and once the execution is completed will be removed.
Currently cached, not currently executing, but otherwise, yeah. An insert ... values is probably to trivial to have its plan cached, hence is not going to be found in the DMV which returns cached plans.
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 3, 2014 at 11:08 pm
What statement causes execution plan gets created and stored in cached in memory. ...Is it all queries or limited?
October 8, 2014 at 4:49 am
dastagiri16 (10/3/2014)
What statement causes execution plan gets created and stored in cached in memory. ...Is it all queries or limited?
SQL Server creates execution plan for each and every query statement. this execution plans consider the query statistics to estimate the cost. Depends on query type that is ad-hoc, recompiled procs etc. sql server saves or cached the plans untill there is a flush or service restart.
You can read more from Books online about this.
Hope this helps!
October 8, 2014 at 4:51 am
GilaMonster (10/3/2014)
kevaburg (10/3/2014)
The table sys.dm_exec_sql_text only stores currently executing SQL and once the execution is completed will be removed.Currently cached, not currently executing, but otherwise, yeah. An insert ... values is probably to trivial to have its plan cached, hence is not going to be found in the DMV which returns cached plans.
How would this triviality be calculated?
October 8, 2014 at 4:52 am
kevaburg (9/8/2014)
The only way to get a historical record of queries run is to run a trace at the time that you want to observe. Otherwise you have dipped-out.....
Default traces could be of some help depends what query is being referred here. For example any query used to change configuration of the SQL instance, or database properties can be retrieve from Default trace having said that they are not overwritten (7 days?)
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply