November 26, 2007 at 6:24 am
I want to know which stored procedures are executed in the last 24 hour in my database and i wish if i could also know about the program who called the stored procedure?
OR
I can know about a specific stored procedure that when it was executed last time and by which program?
Please help...its urgent?
Regards,
Zeeshan Malik
November 26, 2007 at 6:29 am
zeeshan malik (11/26/2007)
I want to know which stored procedures are executed in the last 24 hour in my database and i wish if i could also know about the program who called the stored procedure?OR
I can know about a specific stored procedure that when it was executed last time and by which program?
Please help...its urgent?
Regards,
Zeeshan Malik
Unless you were running a trace, you will not be able to find this out :(. Transaction logs contain a lot of information, but they do not store information about stored procedure executions.
Regards,
Andras
November 26, 2007 at 6:35 am
ok Andras please give me some idea how to run a trace i don't know any thing about it? If you can give me some site URL for help or any thing...thanks
November 26, 2007 at 6:42 am
zeeshan malik (11/26/2007)
ok Andras please give me some idea how to run a trace i don't know any thing about it? If you can give me some site URL for help or any thing...thanks
The easiest way to set it up is to use "SQL Server Profiler". This is a GUI tool that comes with SQL Server, you can access it via the SQL Server 2005->Performance Tools-> SQL Server Profiler.
Note that it does come at a price, depends on what events you filter, the logging will cost in terms of performance and space usage. However, if you set it up, it will log the selected events and you will be able it find out who is using what.
You can read more about it in Books Online; http://msdn2.microsoft.com/En-US/library/ms191443.aspx and http://msdn2.microsoft.com/EN-US/library/ms191006.aspx are good starting points.
Regards,
Andras
November 26, 2007 at 7:49 am
You can automate the use of SQL Profiler (also called Trace) either by using the correct stored procedures (sp_trace_create is the first of many, look 'em up in the BOL) or by setting up the trace that you like within SQL Profiler and then generating the scripts from there. Either way, assuming you collect just the default information, which will give you want you want, you won't place much of a load on your system. If you add tons and tons of counters, you can bring the system to it's knees. Start with the defaults and make adjustments as needed.
Since you're operating in 2005, you can also take a look at the Object Execution Statistics report. It doesn't tell you who called, but you can see quite a bit of other information. This gathers information from the dynamic management views such as sys.dm_exec_query_stats and others. You can look at those directly if you like.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 26, 2007 at 8:21 am
n 2005, you can see which queries are in the plan cache, along with the time the plan was put into the cache and the last time it was executed. It's not by any means guaranteed to show you all the queries that have executed, as plans can be removed from the cache.
You can try this query, but as I said, it may not show you all the stored procs run in the last 24 hours, just some of them. Once a plan is removed from the cache, all info on it is lost. If you need this info, look at running a server side trace to capture it.
select last_execution_time, dbid, objectid, text
from sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.sql_handle)
where statement_start_offset = 0 and objectid is not null and last_execution_time between getdate() and DATEADD(dd,-1,getdate())
You can use the object_name(objectid) function to get the name from the db id. If you're running SP2, use this query to get the name directly. (object_name was changed in SP2 so that it could take a second parameter)
select last_execution_time, dbid, object_name(objectid,dbid) as ObjectName, text
from sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.sql_handle)
where statement_start_offset = 0 and objectid is not null and last_execution_time between getdate() and DATEADD(dd,-1,getdate())
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
November 26, 2007 at 8:25 am
Good one. I hadn't thought about looking at the cache.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 17, 2012 at 12:27 am
@Gila-Monster
Hi,
I would like to give you a small correction in your code which is the right one, it is a small thing but needed.
in your query you have given the end-date-range as the start-date-range (in between clause in the where clause ), i am just correcting it.
--1st query correction
select last_execution_time, dbid, objectid, text
from sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.sql_handle)
where statement_start_offset = 0
and objectid is not null
---- Correction is in this line.
and last_execution_time between DATEADD(dd,-1,getdate()) and getdate()
--same correction in the second query too
May 11, 2013 at 12:23 pm
select *--b.name, a.last_execution_time
from sys.dm_exec_procedure_stats a
--inner join sys.objects b on a.object_id = b.object_id
where DB_NAME(database_ID) = 'SQL_Admin_DB'
:cool:Appu
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply